Generating Records from DUAL

How meny times You need to generate a test with a million of rows os some exaxt number of rows,


A trick that a friend told me is, 

Just use Dual,


select *
  from dual
connect by level <=:p_number_of_rows;

With this you have all the rows that you could need whithout a single insert.

Hope it helps.

Mutli Table Inserts

How many times you have faced the Issue when loading information that your query resolved more than one issue but then you want to insert the results on more than one table,
Normally You will create something like this

INSERT into TABLE A
Values ..
Query

INSERT into TABLEB
Values ..
Query

INSERT into TABLEC
Values ..
Query


For those cases oracle created the Multi Table Insert which loks like this:

INSERT
WHEN ... THEN
INTO ... VALUES ...
WHEN ... THEN
INTO ... VALUES ...
subquery;


Aa simple sample will be if you want to insert the firsr 5 row's on the table A, then on the next 2 records on table b and then everithing else on other table, the sample will look like this:

I created this 3 tables:

  create table table_demo_1
  (dat_1 varchar2(10),
   num_2 number  );
 
  create table table_demo_2
  (dat_1 varchar2(10),
   num_2 number  );
  
   create table table_demo_3
  (dat_1 varchar2(10),
   num_2 number  );

  
And onse done that I execute the sentence has follows

INSERT
WHEN ROW_NUM<=5 THEN
INTO table_demo_1 (dat_1,NUM_2) VALUES ('A',ROW_NUM)
WHEN ROW_NUM BETWEEN 6 AND 7 THEN
INTO table_demo_2 (dat_1,NUM_2) VALUES ('B',ROW_NUM)
ELSE
INTO table_demo_3 (dat_1,NUM_2) VALUES ('C',ROW_NUM)
  SELECT DUMMY, ROWNUM ROW_NUM
      FROM dual
     CONNECT BY LEVEL<=10 ;


Easy has you can see.

Date tricks on SQL

Dates are always a confusion when working on most of the languages . . . wait what?
Well the truth is that must of the people never reads a manual because . . . is bigger than the bible.
So here is a simple explanation of the Date Datatype in Oracle Sql. First of all a date data type only stores date + time but only with a precision of seconds if you need to record fractional second then you need to use a different data type.
In almost all the languages the dates can be converted from and to strings directly. well here is not the exception, but you need to be careful because it always depends on the setup of the database.
this 3 parameters:
nls_date_format <---- br="" date="" format="">nls_language <---- br="" database="" language="" of="" the="">nls_territory <--- br="" database="" of="" territory="" the="">You can get the values from them with the following query:

select *
  from v$parameter
 where name  in  ('nls_date_format'
                            ,'nls_language'
                           ,'nls_territory');

By default the language and territory are american and america , and the date format is DD-MON-RR, wich means  '12-JAN-14' is a valid date that represents 12 January of 2014, that was easy right but . . .


What happens when your source of data has a diferent format, for example

select '07-12-1985' from dual

to make this a date

select to_date('07-12-1985','DD-MM-RRRR') from dua.


If you are bored here begin the tricks

Imagine that somen one ask you to generate a range of dates,

1,2. . . . .  30 jan 2014

how do i this?

Select start_date+level-1 calc_date
 from (select to_date('01-01-2014', 'dd-mm-rrrr') start_date,
           to_date('31-01-2014', 'dd-mm-rrrr')  end_date
   from dual) connect by level<=end_date-start_date+1

How to get the first day:

select trunc(to_date('31-01-2014', 'dd-mm-rrrr'),'mm')  fisrt_day
  from dual;

How to get the last day:

select last_day(to_date('31-01-2014', 'dd-mm-rrrr'))  last_day
  from dual;

How to add days 12 days:

select to_date('31-01-2014', 'dd-mm-rrrr') + 12 add_days
  from dual;

How to add 12 months:

select add_months(to_date('31-01-2014', 'dd-mm-rrrr'),12) add_months
  from dual;


How to substract 6 months:

select add_months(to_date('31-01-2014', 'dd-mm-rrrr'),-6 ) add_months
  from dual;
   
      **** Yes it's tha same function just using a negative number.

And thats all for now.

Hope it helps some one.

How know to which responsibilities it's associated a function?

The following query extracts the information from the setup on the E-Business Suite and receives has parameter the USER_FUNCTION_NAME  it returns the resposibilities related to that user function name.

/*
get Responsibilities associated to Funtions
Only the funtions not Excluded
*/
WITH DAT
              AS (SELECT 'FND_FORM_FUNCTIONS-' || fff.function_id ID,
                         FUNCTION_NAME,
                         USER_FUNCTION_NAME,
                         DESCRIPTION,
                         'FUNCTION' DATA_TYPE,
                         APPLICATION_ID,
                         CAST (NULL AS DATE) START_DATE,
                         CAST (NULL AS DATE) END_DATE,
                         CREATION_DATE,
                         CREATED_BY,
                         LAST_UPDATED_BY,
                         LAST_UPDATE_DATE,
                         CAST (NULL AS VARCHAR2 (480)) ASOCIATED_function_name,
                         CAST (NULL AS NUMBER) function_application_id,
                         'FND_FORM_FUNCTIONS-' || fff.function_id EXC
                    FROM FND_FORM_FUNCTIONS_VL fff
                  UNION ALL
                  SELECT 'FND_MENUS-' || MENU_ID,
                         MENU_NAME,
                         USER_MENU_NAME,
                         DESCRIPTION,
                         'MENU' DATA_TYPE,
                         NULL APPLICATION_ID,
                         CAST (NULL AS DATE) START_DATE,
                         CAST (NULL AS DATE) END_DATE,
                         CREATION_DATE,
                         CREATED_BY,
                         LAST_UPDATED_BY,
                         LAST_UPDATE_DATE,
                         CAST (NULL AS VARCHAR2 (480)) function_name,
                         CAST (NULL AS NUMBER) function_application_id,
                         'FND_MENUS-' || MENU_ID
                    FROM fnd_menus_VL fm
                  UNION ALL
                  SELECT 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID,
                         RESPONSIBILITY_KEY,
                         RESPONSIBILITY_NAME,
                         DESCRIPTION,
                         'RESPONSIBILITY',
                         APPLICATION_ID,
                         START_DATE,
                         END_DATE,
                         CREATION_DATE,
                         CREATED_BY,
                         LAST_UPDATED_BY,
                         LAST_UPDATE_DATE,
                         CAST (NULL AS VARCHAR2 (480)) function_name,
                         CAST (NULL AS NUMBER) function_application_id,
                         'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID
                    FROM fnd_responsibility_vl
                   UNION ALL
                  SELECT 'FND_MENU_ENTRIES-' || MENU_ID || '-' || ENTRY_SEQUENCE,
                         CAST (ENTRY_SEQUENCE AS VARCHAR2 (4000)),
                         PROMPT,
                         ent.DESCRIPTION,
                         'MENU ITEM  ' || NVL2 (fun.TYPE, '-' || fun.TYPE, '-SUBMENU'),
                         NULL APPLICATION_ID,
                         CAST (NULL AS DATE) START_DATE,
                         CAST (NULL AS DATE) END_DATE,
                         ent.CREATION_DATE,
                         ent.CREATED_BY,
                         ent.LAST_UPDATED_BY,
                         ent.LAST_UPDATE_DATE,
                         FUN.function_name,
                         FUN.APPLICATION_ID function_application_id,
                         'FND_FORM_FUNCTIONS-' || fun.function_id EXC
                    FROM FND_MENU_ENTRIES_VL ent, FND_FORM_FUNCTIONS_VL fun
                   WHERE     1 = 1
                         AND PROMPT IS NOT NULL
                         AND NVL (ent.GRANT_FLAG, 'Y') = 'Y'
                         AND (ent.function_id IS NOT NULL OR SUB_MENU_ID IS NOT NULL)
                         AND ent.function_id = fun.function_id(+)
                  ),
           REL
              AS (SELECT 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID PAR, 'FND_MENUS-' || MENU_ID CH FROM FND_RESPONSIBILITY
                  UNION ALL
                  SELECT 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID PAR, 'FND_REQUEST_GROUPS-' || REQUEST_GROUP_ID CH
                    FROM FND_RESPONSIBILITY
                  UNION ALL
                  SELECT NULL PAR, 'FND_RESPONSIBILITY-' || RESPONSIBILITY_ID || '-' || APPLICATION_ID CH
                    FROM FND_RESPONSIBILITY
                  UNION ALL
                  SELECT 'FND_MENUS-' || MENU_ID PAR, 'FND_MENU_ENTRIES-' || MENU_ID || '-' || ENTRY_SEQUENCE CH FROM FND_MENU_ENTRIES
                  UNION ALL
                  SELECT 'FND_MENU_ENTRIES-' || MENU_ID || '-' || ENTRY_SEQUENCE par,
                         NVL2 (SUB_MENU_ID, 'FND_MENUS-' || SUB_MENU_ID, 'FND_FORM_FUNCTIONS-' || function_id)
                    FROM FND_MENU_ENTRIES
                  )
                  ,
           FN AS (
SELECT 'FND_RESPONSIBILITY-'||RESPONSIBILITY_ID|| '-' || APPLICATION_ID ID, DECODE (RULE_TYPE,  'F', 'FND_FORM_FUNCTIONS-' || ACTION_ID,  'M', 'FND_MENUS-' || ACTION_ID) RESP_ID
                    FROM FND_RESP_FUNCTIONS
                   )
      SELECT tree.FUNCTION_NAME RESP_KEY,
                  tree.USER_FUNCTION_NAME  RESP_DESCRIPTION,
                  tree.RESP_NAME function_name,
                  tree.RESP_DESCRIPTION USER_FUNCTION_NAME,
                  tree.NAVIGATION
       FROM (SELECT CONNECT_BY_ROOT function_name RESP_NAME,
             CONNECT_BY_ROOT USER_FUNCTION_NAME RESP_DESCRIPTION,
             CONNECT_BY_ROOT DAT.APPLICATION_ID RESP_APPLICATION_ID,
             CONNECT_BY_ROOT DAT.ID RESP_ID,
             CONNECT_BY_ROOT dat.DATA_TYPE RESP_DATA_TYPE,
             SYS_CONNECT_BY_PATH (function_name || ':' || USER_FUNCTION_NAME, '>') tree,
             SYS_CONNECT_BY_PATH (DECODE (data_type,'RESPONSIBILITY',CAST (NULL AS VARCHAR2 (80)),function_name || ':' || USER_FUNCTION_NAME), '>') tree_NO_RESP,
             SYS_CONNECT_BY_PATH (LPAD (FUNCTION_NAME, 20, '0'), '>') ORD_FUN,
             SYS_CONNECT_BY_PATH (CAST (DECODE (data_type, 'MENU', CAST (NULL AS VARCHAR2 (80)), USER_FUNCTION_NAME || '-') AS VARCHAR2 (80)), '>')
                NAVIGATION,
             DAT.*,
             cr.user_name CREATED_BY_NAME,
             upd.user_name LAST_UPDATED_BY_NAME,
             REL.par,
             rel.ch
        FROM DAT,
             REL,
             fnd_user cr,
             fnd_user upd
             WHERE dat.ID = rel.ch
                     AND dat.created_by          = cr.user_id(+)
                    AND dat.last_updated_by    = upd.user_id(+)
      CONNECT BY NOCYCLE PRIOR par = ch
      START WITH (
                           USER_FUNCTION_NAME = :P_NAME-->>NAME OF THE FUNCTION HERE
                           )
         )
          tree ,FN
         WHERE DATA_TYPE='RESPONSIBILITY'
          AND RESP_DATA_TYPE='FUNCTION'
           AND tree.RESP_ID=fn.RESP_ID(+)
           AND tree.ID=fn.id(+)
           and fn.id is null;