SQLLDR - SQL* Loader ORA-01008: not all variables bound

IF you are receiving this error, it might be due to issues in multiple places. You have to understand that the error is not in SQL Loader alone.

It might be due to the Call to SQL Loader when a bind variable used to call it is undefined.

But the majority of this issue is due to the bind variables referenced in the control file of the SQL Loader itself.

Check for any typos in all the bind variables or any bind variable name that is not used in the control file.

Record 2: Rejected - Error on table TEST.
ORA-01008: not all variables bound

Oracle Applications - Key tables - MTL_SYSTEM_ITEMS_B

MTL_SYSTEM_ITEMS_B

columns: 

INVENTORY_ITEM_ID : Inventory item identifier 
ORGANIZATION_ID : Organization identifier 
ACCOUNTING_RULE_ID : Accounting rule identifier 
INVOICING_RULE_ID : Invoicing rule identifier 
PURCHASING_ITEM_FLAG: Flag indicating purchasing item 
SHIPPABLE_ITEM_FLAG : Flag indicating shippable item 
CUSTOMER_ORDER_FLAG : Flag indicating customer orderable item 
INTERNAL_ORDER_FLAG : Flag indicating internally orderable item 
SERVICE_ITEM_FLAG : Flag indicating service item 
INVENTORY_ITEM_FLAG : Flag indicating inventory item 
ENG_ITEM_FLAG : Flag indicating engineering item 
INVENTORY_ASSET_FLAG : Flag indicating item is an inventory asset 
PURCHASING_ENABLED_FLAG : Flag indicating item is purchasable 
CUSTOMER_ORDER_ENABLED_FLAG : Flag indicating item is customer orderable 
INTERNAL_ORDER_ENABLED_FLAG : Flag indicating item is internally orderable 
SO_TRANSACTIONS_FLAG VARCHAR2 : Sales order transactions flag 
MTL_TRANSACTIONS_ENABLED_FLAG : Flag indicating item is transact-able 
STOCK_ENABLED_FLAG : Flag indicating item is stock-able 
BOM_ENABLED_FLAG : Flag indicating item may appear on a BOM 
BUILD_IN_WIP_FLAG : Flag indicating item may be built in WIP

Description: 

MTL_SYSTEM_ITEMS_B is the master table for items. This table contains the definitions for inventory items, engineering items, and purchasing items. Main item characteristics include  Bill of Material, Costing, Purchasing, Receiving, Inventory, Physical attributes, General Planning, MPS/MRP Planning, Lead times, Work in Process, Order Management, and Invoicing.

The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID.

Each item is initially defined in an item master organization. The user. A row is inserted for each new organization the item is assigned to. Many columns such as MTL_TRANSACTIONS_ENABLED_FLAG and BOM_ENABLED_FLAG correspond to item attributes defined in the
MTL_ITEM_ATTRIBUTES table. The attributes that are available to the user depend on which Oracle applications are installed. The table MTL_ATTR_APPL_DEPENDENCIES maintains the relationships between items attributes and Oracle applications.

Two unit of measure columns are stored in MTL_SYSTEM_ITEMS_B table. PRIMARY_UOM_CODE is the 3-character unit that is used throughout Oracle Manufacturing. PRIMARY_UNIT_OF_MEASURE is the 25-character Unit of Measure that is used throughout Oracle Purchasing. Unlike the PRIMARY_UOM_CODE, the Unit of Measure is language-dependent attribute, however, PRIMARY_UNIT_OF_MEASURE column stores value in the installation base language only.

MLS tables are implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. Translations table (MTL_SYSTEM_ITEMS_TL) holds item Description and Long Description in multiple languages. DESCRIPTION column in the base table (MTL_SYSTEM_ITEMS_B) is for backward compatibility and is maintained in the installation base language only.

Oracle Applications - Query to list all the responsibilities assigned to a user

Oracle Applications - Query to list all the responsibilities assigned to a user

SELECT DISTINCT
fu.user_name, frtl.RESPONSIBILITY_NAME
FROM fnd_responsibility fr
, fnd_responsibility_TL frtl
, fnd_user_resp_groups furg
, fnd_user fu
WHERE furg.responsibility_id = fr.responsibility_id
AND frtl.responsibility_id = fr.responsibility_id
and fr.APPLICATION_ID=furg.RESPONSIBILITY_APPLICATION_ID
AND fu.user_id = furg.user_id
AND fu.user_name = '12345'  -- username here
order by fu.user_name

Combine multiple text files to one - DOS

Dos command to combine multiple text files into one consolidated file.

copy /a *.* all.txt

Submit concurrent request as child request of a main Concurrent program

Step 1: read the concurrent request data using fnd_conc_global.request_data
Step 2: If request data from step 1 is not null, then the program is restarting after the child requests are processed and the master has restarted. Go to step 7.
      Step 3: If the request data is null submit child programs using fnd_request.submit_request with child request                TRUE.
      Step 4: When all the child programs are done submitting, set the request data of the concurrent request to  some text, so that when the master is restarted the               request data is not null
      Step 5: Pause the master and allow the child requests to execute using fnd_conc_global.set_req_globals(conc_status => 'PAUSED',request_data => 'MASTER DONE')
      Step 6: When the child requests are executed the master restarts and goes to Step 1
Step 7: Do post processing actions and exit.

Pseudo code below :

req_data := fnd_conc_global.request_data;

if (req_data is not null) then

i := to_number(req_data);

i := i + 1;

if (i < 11 ) then

errbuf := 'Done!';

retcode := 0 ;

return;

end if;

else

i := 1;

end if;

req_id:= fnd_request.submit_request('FND', 'CHILD',

'Child ' || to_char(i), NULL,

TRUE, fnd_conc_global.printer);

if req_id = 0 then

--

-- If request submission failed, exit with error.

--

errbuf := fnd_message.get;

retcode := 2;

else

--

-- Here we set the globals to put the program into the

-- PAUSED status on exit, and to save the state in

-- request_data.

--

fnd_conc_global.set_req_globals(conc_status => 'PAUSED',

request_data => to_char(i));

errbuf := 'Sub-Request submitted!';

retcode := 0 ;

end if;

return;

end;

Oracle Applications - Query to quickly get the Financials family pack level

The below query will be useful to quickly get the financials family pack level you are in:

SELECT bug_number,decode(bug_number,

'3653484', 'FIN_PF.G',

'3153675', 'FIN_PF.F',

'2842697', 'FIN_PF.E',

'3016445', 'FIN_PF.D1',

'2380068', 'FIN_PF.C',

'2218339', 'FIN_PF.B',

'1807809', 'FIN_PF.A',

'no family packs applied') "Family Pack Level"

FROM ad_bugs

WHERE

bug_number in ('3653484', '3153675', '2842697', '3016445', '2380068', '2218339', '1807809')

ORDER BY 1;

Oracle Applications - Query to get all the functions and menus listed under a responsibility

A simple query to get all the menu listings attached to a responsibility. Particularly useful when you want to know in which responsibility a particular menu is attached.You can alter the below query to take the menu name as the where condition instead of the Responsibility name:

SELECT a.responsibility_name,c.prompt,c.description,d.menu_name

FROM apps.fnd_responsibility_tl a,

apps.fnd_responsibility b,

apps.fnd_menu_entries_tl c,

apps.fnd_menus_tl g,

apps.fnd_menus d,

apps.fnd_application_tl e,

apps.fnd_application f

WHERE a.responsibility_id(+) = b.responsibility_id

AND a.responsibility_name like '%ITD%AR%MANAGER%'

AND g.menu_id = d.menu_id

AND b.menu_id = c.menu_id

AND b.menu_id = d.menu_id

AND e.application_id = f.application_id

AND f.application_id = b.application_id

AND a.LANGUAGE = 'US'

AND c.LANGUAGE = 'US'

AND g.LANGUAGE = 'US'

and e.LANGUAGE = 'US';

Employee supervisor Hierarchy - Oracle applications

DECLARE
   CURSOR cur_main
   IS
      SELECT DISTINCT person_id, full_name
        FROM per_all_people_f
       WHERE PERSON_ID = 1234;                                    -- person id

   CURSOR cur_sup (p_person_id NUMBER)
   IS
          SELECT DISTINCT paf.person_id,
                          ppf.full_name,
                          LEVEL,
                          ppf.email_address
            FROM per_all_people_f ppf, per_all_assignments_f PAF
           WHERE 1 = 1 AND ppf.person_id = PAF.PERSON_ID
      CONNECT BY PRIOR paf.supervisor_id = ppf.person_id
      START WITH paf.person_id = 1234                             -- person id
        ORDER BY LEVEL;
BEGIN
   DBMS_OUTPUT.enable (1000000);

   BEGIN
      --dbms_output.put_line('Approver','Level','Supervisor');
      FOR cur_main_rec IN cur_main
      LOOP
         FOR cur_sup_rec IN cur_sup (cur_main_rec.person_id)
         LOOP
            DBMS_OUTPUT.put_line (
               cur_sup_rec.full_name || ' ' || CUR_SUP_REC.PERSON_ID);
         END LOOP;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Error in updation ' || SQLERRM);
   END;
END;

Oracle - Fetching all columns for a given table or finding all table which has a column name

The below query will help you in situations when you want to dynamically fetch the columns for a specific table or when  you simply need the list of all tables which have a column like you are looking for.

SELECT OWNER,
       TABLE_NAME,
       COLUMN_NAME,
       DATA_TYPE,
       DATA_LENGTH,
       DATA_PRECISION,
       NULLABLE,
       LAST_ANALYZED,
       CHAR_USED,
       HISTOGRAM
  FROM all_tab_columns

Oracle - List all available DB links in the database

List all available DB links in the database:

SELECT * FROM dba_db_links

Oracle - Performance Tuning SQL code using DBMS_SQLTUNE

The below write up gives a step by step approach to performance tune a query using the Oracle built in Package : DBMS_SQLTUNE 

Though this wont replace a manual performance tuning effort, it can get you started in the right direction or help you spot the obvious that you had always missed.

Please note this package required special licensing requirements to be used

STEP 1: Create a Tuning Task

DECLARE
DECLARE
  my_sqltext CLOB;
  task_name VARCHAR2(30);
BEGIN
  my_sqltext := 'SELECT DISTINCT PARENT_CUST_DOC_ID FROM APPS.XX_AR_EBL_CONS_HDR_HIST WHERE EXTRACT_BATCH_ID = 234';
  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext,
                                   user_name => 'APPS',
                                   scope => 'COMPREHENSIVE',
                                   time_limit => 60,
                                   task_name => 'sql_tuning_cons_ebill4');

END;

STEP 2: Execute the Task

begin
dbms_sqltune.execute_tuning_task ( 'sql_tuning_cons_ebill4');
end;

STEP 3: Check if the task has completed

SELECT task_name, status FROM dba_advisor_log WHERE task_name= 'sql_tuning_cons_ebill4';

STEP 4: Generate Report 

select dbms_sqltune.report_tuning_task('sql_tuning_cons_ebill4') from dual;

The suggestion below is to analyze the table and then create a index on extact_batch_id which gives a 98% benefit.

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : sql_tuning_cons_ebill4
Tuning Task Owner                 : APPS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 07/16/2010 18:37:22
Completed at                      : 07/16/2010 18:37:22
Number of Statistic Findings      : 1
Number of Index Findings          : 1

-------------------------------------------------------------------------------
Schema Name: APPS
SQL ID     : dt7baa656pgxr
SQL Text   : SELECT DISTINCT PARENT_CUST_DOC_ID FROM
             APPS.XX_AR_EBL_CONS_HDR_HIST WHERE EXTRACT_BATCH_ID = 234

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "XXFIN"."XX_AR_EBL_CONS_HDR_HIST" and its indices were not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table and its indices.
    execute dbms_stats.gather_table_stats(ownname => 'XXFIN', tabname =>
            'XX_AR_EBL_CONS_HDR_HIST', estimate_percent =>
            DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
            AUTO', cascade => TRUE);

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table and its indices
    in order to select a good execution plan.

2- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 98.06%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index XXFIN.IDX$$_5D6710001 on
    XXFIN.XX_AR_EBL_CONS_HDR_HIST("EXTRACT_BATCH_ID");

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3839049120

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                         |     2 |    52 |   103   (1)| 00:00:02 |
|   1 |  HASH UNIQUE       |                         |     2 |    52 |   103   (1)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| XX_AR_EBL_CONS_HDR_HIST |     2 |    52 |   102   (0)| 00:00:02 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("EXTRACT_BATCH_ID"=234)

2- Using New Indices
--------------------
Plan hash value: 3594741815

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |     1 |    26 |     2  (50)| 00:00:01 |
|   1 |  HASH UNIQUE                 |                         |     1 |    26 |     2  (50)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| XX_AR_EBL_CONS_HDR_HIST |     1 |    26 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX$$_5D6710001         |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("EXTRACT_BATCH_ID"=234)

-------------------------------------------------------------------------------