How to Pass the File name as Parameter and populate the request id of the concurrent Program dyanamically using SQL Loader

For one of my client i was having a requirement to pass the file name as parameter to the SQL loader program and capture the file name and the request id in the table.After surfing a lot on google and don't find anything so i come up with the following solution which works for me so thought of sharing the solution. Please find below the details.

1. Create Control file as below 
2. In the control file we can use $FILE as the parameter to pass file name as parameter to the SQL Loader Program

LOAD DATA
INFILE '$FILE'
<APPEND/REPLACE/TRUNCATE>
INTO TABLE XX_STAGING_TBL
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
 , MAIL_REQUEST_ID      DECIMAL EXTERNAL NULLIF (MAIL_REQUEST_ID=BLANKS)
 , ORDER_NUMBER         DECIMAL EXTERNAL NULLIF (ORDER_NUMBER=BLANKS)
 , ORDER_HEADER_ID      DECIMAL EXTERNAL NULLIF (ORDER_HEADER_ID=BLANKS)
 , LINE_NUMBER          DECIMAL EXTERNAL NULLIF (LINE_NUMBER=BLANKS)
 , ORDER_LINE_ID        DECIMAL EXTERNAL NULLIF (ORDER_LINE_ID=BLANKS)
 , MAIL_SEND_STATUS     CHAR NULLIF (MAIL_SEND_STATUS=BLANKS)
 , TO_RECIPIENT         CHAR NULLIF (TO_RECIPIENT=BLANKS)
 , CC_RECIPIENT         CHAR NULLIF (CC_RECIPIENT=BLANKS)
 , CREATION_DATE        DATE "MM/DD/YY" NULLIF (CREATION_DATE=BLANKS)
 , CREATED_BY           DECIMAL EXTERNAL NULLIF (CREATED_BY=BLANKS)
 , LAST_UPDATE_DATE     DATE "MM/DD/YY" NULLIF (LAST_UPDATE_DATE=BLANKS)
 , LAST_UPDATED_BY      DECIMAL EXTERNAL NULLIF (LAST_UPDATED_BY=BLANKS)

 , REQUEST_ID           "STRING_TO_REPLACE"
)


3. Save the file and move it to the XXCUST_TOP/bin directoy.
4. After this write a shell script to execute the Control file as below

### Below command is used to replace the "STRING_TO_REPLACE" with the current request id so that i will insert the constant value in the table for column REQUEST_ID.

sed s/STRING_TO_REPLACE/$p_conc_request_id/ $XX_TOP/bin/XX_DEMO_CTL.ctl > $XX_TOP/bin/${XX_DEMO_CTL}_${p_conc_request_id}.ctl

##go to the directory

cd $XX_TOP/bin

## run the SQLLDR for the New Control file

sqlldr ${p_userid_pwd} control=$XX_TOP/bin/${XX_DEMO_CTL}_${p_conc_request_id}.ctl log=/usr/tmp/XX_DEMO_CTL.log bad=/usr/tmp/XX_DEMO_CTL.bad data=${p_file_name}

## After completion remove the newly created Control file and soft link.

rm ${XX_DEMO_CTL}_${p_conc_request_id}.ctl

exit

5. Define the HOST concurrent program for the above shell script and pass the file name as parameter.

6. submit the Concurrent program and look for the data in the REQUEST_ID column the request id of the Shell Script Concurrent Program should be populated.

7. Please test it before using it on PROD instance.

Note :- We can create new Control file at run time dynamically and remove it after processing the file. Or we can Update the same Control file to keep the value of request id column as constant and once the processing the csv file is complete again run the above mentioned sed command to replace the request id value to a string "STRING_TO_REPLACE"

Script to Get the Nth Position String within a String

CREATE OR REPLACE PACKAGE xx_delimited_string
AS

-- p_str - the string to get the i'th word from
-- p_str can be xyz,yxz,zyx,zxy and you want to get the zxy as return ---- Value
-- p_n - the word to get from the string -- an index into the string
-- p_enclosed_by - what the words might be wrapped in.  In the above -- -- example, chr(34) is a double quote
-- p_terminated_by - what separates the words.  In the above example, ---- chr(44) is a comma.
  

  
   FUNCTION nth_word(
      p_str                      IN             VARCHAR2
    , p_n                        IN             VARCHAR2
    , p_enclosed_by              IN             VARCHAR2 DEFAULT ''''
    , p_separated_by             IN             VARCHAR2 DEFAULT ','
   )
      RETURN VARCHAR2;

   PRAGMA RESTRICT_REFERENCES(nth_word, WNDS, RNDS);
END xx_delimited_string;
/

CREATE OR REPLACE PACKAGE BODY xx_delimited_string
AS
   TYPE vcarray IS TABLE OF VARCHAR2(2000)
      INDEX BY BINARY_INTEGER;

   g_words                                           vcarray;
   g_empty                                           vcarray;
   g_last_string                                     VARCHAR2(4096);

   FUNCTION de_quote(
      p_str                      IN             VARCHAR2
    , p_enc_by                   IN             VARCHAR2
   )
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN REPLACE(LTRIM(RTRIM(p_str
                               , p_enc_by
                                )
                         , p_enc_by
                          )
                   , p_enc_by || p_enc_by
                   , p_enc_by
                    );
   END de_quote;

   PROCEDURE parse_string(
      p_str                      IN             VARCHAR2
    , p_delim                    IN             VARCHAR2
    , p_sep                      IN             VARCHAR2
   )
   IS
      l_n                                               NUMBER DEFAULT 1;
      l_in_quote                                        BOOLEAN DEFAULT FALSE;
      l_ch                                              CHAR(1);
      l_len                                             NUMBER DEFAULT NVL(LENGTH(p_str)
                                                                         , 0
                                                                          );
   BEGIN
      IF (l_len = 0)
      THEN
         RETURN;
      END IF;

      g_words                                        := g_empty;
      g_words(1)                                     := NULL;

      FOR i IN 1 .. l_len
      LOOP
         l_ch                                           := SUBSTR(p_str
                                                                , i
                                                                , 1
                                                                 );

         IF (l_ch = p_delim)
         THEN
            l_in_quote                                     := NOT l_in_quote;
         END IF;

         IF (    l_ch = p_sep
             AND NOT l_in_quote)
         THEN
            l_n                                            := l_n + 1;
            g_words(l_n)                                   := NULL;
         ELSE
            g_words(l_n)                                   := g_words(l_n) || l_ch;
         END IF;
      END LOOP;

      FOR i IN 1 .. l_n
      LOOP
         g_words(i)                                     := de_quote(g_words(i)
                                                                  , p_delim
                                                                   );
      END LOOP;
   END parse_string;

   FUNCTION nth_word(
      p_str                      IN             VARCHAR2
    , p_n                        IN             VARCHAR2
    , p_enclosed_by              IN             VARCHAR2 DEFAULT ''''
    , p_separated_by             IN             VARCHAR2 DEFAULT ','
   )
      RETURN VARCHAR2
   IS
   BEGIN
      IF (   g_last_string IS NULL
          OR p_str <> g_last_string)
      THEN
         g_last_string                                  := p_str;
         parse_string(p_str
                    , p_enclosed_by
                    , p_separated_by
                     );
      END IF;

      RETURN g_words(p_n);
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN NULL;
   END nth_word;
END xx_delimited_string;
/


How to Test 

SELECT xx_delimited_string.nth_word(:p_str
                         , 4
                         , CHR(34)
                         , CHR(44)
                          )

  FROM DUAL

Store timestamp with time zone in VARCHAR2 column and convert it into Date or timestamp Format

Format Timestamp with time zone ('YYYY-MM-DDTHH:MI:SS.FF+TZ')  if stored into VARCHAR2 column and needs to convert into 'YYYY-MM-DD HH:MI:SS' then use the Below select statement for conversion.


SELECT CAST (
          TO_TIMESTAMP_TZ ('2014-02-12T23:02:14.000+0000',
                           'YYYY-MM-DD"T"HH24:MI:SS.FF TZH:TZM')
             AT TIME ZONE DBTIMEZONE AS DATE)
  FROM DUAL

Useful Scripts for Discoverer

--List of Business Areas
SELECT ba_name "Business Area"
     , ba_created_by "Creator"
     , ba_created_date "Creation Date"
     , ba_updated_by "Updated By "
     , ba_updated_date "Last Update Date"
     , ba_id
  FROM eul5_us.eul5_bas
/
--List of Folders
SELECT   b.ba_name
       , f.obj_name folder_name
       , f.obj_id
       , f.obj_ext_owner owner
    FROM eul5_us.eul5_objs f
       , eul5_us.eul5_ba_obj_links l
       , eul5_us.eul5_bas b
   WHERE 1 = 1
     AND f.obj_id = l.bol_obj_id
     AND b.ba_id = l.bol_ba_id
     AND UPPER (b.ba_name) LIKE UPPER ('%'||'&Business_area'||'%')
     AND UPPER (f.obj_name) LIKE UPPER ('%'||'&FOLDER_NAME'||'%')
ORDER BY b.ba_name
       , f.obj_name
/
--List of Folder Items
SELECT   i.exp_name item_name
       , i.exp_id
       , i.it_ext_column
       , f.obj_name folder_name
       , b.ba_name
    FROM eul5_us.eul5_expressions i
       , eul5_us.eul5_objs f
       , eul5_us.eul5_ba_obj_links l
       , eul5_us.eul5_bas b
   WHERE f.obj_id = i.it_obj_id
     AND f.obj_id = l.bol_obj_id
     AND b.ba_id = l.bol_ba_id
     AND UPPER (i.exp_name) LIKE UPPER ('%'||'&ITEM_NAME'||'%')
     AND UPPER (b.ba_name) LIKE UPPER ('%'||'&Business_area'||'%')
     AND UPPER (f.obj_name) LIKE UPPER ('%'||'&FOLDER_NAME'||'%')
ORDER BY b.ba_name
       , f.obj_name
       , i.exp_name
/
--List of Workbooks
SELECT doc_name "Document"
     , doc_developer_key
     , doc_description "Description"
  FROM eul5_us.eul5_documents
/

Way to search a String for LONG data type In oracle using Object types and Pipeline Functions

CREATE OR REPLACE TYPE XX_LONG_DATA_REC_TYPE AS OBJECT(OBJECT_NAME VARCHAR2(240), LONG_DATA_TEXT CLOB);
/

CREATE OR REPLACE TYPE XX_LONG_DATA_TBL AS TABLE OF XX_LONG_DATA_REC_TYPE
/

CREATE OR REPLACE FUNCTION XX_SEARCH_STRING( P_SEARCH_STRING VARCHAR2 )
   RETURN XX_LONG_DATA_TBL
   PIPELINED
IS
   XX_LONG_DATA_REC            XX_LONG_DATA_REC_TYPE;
BEGIN
   FOR Z IN ( SELECT VIEW_NAME, TEXT
                FROM DBA_VIEWS
               WHERE ROWNUM <=10 )
   LOOP
      IF ( UPPER(Z.TEXT) LIKE UPPER(P_SEARCH_STRING) )
      THEN
         XX_LONG_DATA_REC         := XX_LONG_DATA_REC_TYPE( Z.VIEW_NAME, Z.TEXT );
         PIPE ROW ( XX_LONG_DATA_REC );
      END IF;
   END LOOP;
END;
/

Script to test the same 

select * from table(xx_search_string('%SELECT%'));



Another way without creating any DB object

DECLARE

   L_CLOB_STRING                 CLOB;

   L_LONG_STRING                 LONG;

   L_OBJECT_NAME                 VARCHAR2( 240 );

BEGIN

   SELECT VIEW_NAME, TEXT

     INTO L_OBJECT_NAME, L_LONG_STRING

     FROM DBA_VIEWS

    WHERE ROWNUM = 1;

   L_CLOB_STRING              := L_LONG_STRING;

   IF UPPER( L_CLOB_STRING ) LIKE '%SELECT%'

   THEN

      DBMS_OUTPUT.PUT_LINE( 'OBJECT NAME : ' || L_OBJECT_NAME );

   ELSE

      DBMS_OUTPUT.PUT_LINE( 'SELECT SEARCH STRING NOT FOUND IN THE OBJECT NAME :  ' || L_OBJECT_NAME );

   END IF;

END;