1 Reply Latest reply: Apr 21, 2014 4:39 AM by SKP RSS

    Unable to populate request id through sql loader.

    Uva

      Hi All,

      I want to update the 'Request Id' of the program which I am submitting for sql-loader.

       

      I tried with couple of way, but all went in vein.

       

      1st Way

       

      request_id char "fnd_profile.value('conc_request_id')"

       

      2nd

       

      request_id expression "select max(request_id) from fnd_concurrent_request where

       

      concurrent_program_id

       

      =(SELECT concurrent_program_id FROM FND_CONCURRENT_PROGRAMS_TL WHERE USER_CONCURRENT_PROGRAM_NAME = 'my CP Name'and language = 'US'

       

      )

       

       

      "

      But still request id is not getting populated in my table.

       

      Pls suggest me with this.

       

      Thanks in advance.

      Regards,

      Uva.

        • 1. Re: Unable to populate request id through sql loader.
          SKP

          I have used the following example for me and working fine for me

           

          CREATE TABLE test_load (

            deptno  NUMBER,

            empid number,

            ename varchar2(20));

          );

          --function

          create or replace

          FUNCTION get_deptno

            (p_dname IN dept.dname%TYPE)

            RETURN      dept.deptno%TYPE

          AS

            v_deptno dept.deptno%TYPE;

          BEGIN

            SELECT deptno

            INTO   v_deptno

            FROM   dept

            WHERE  dname = p_dname;

            RETURN v_deptno;

          END get_deptno;

          --control file=test_load.ctl

          LOAD DATA

          infile *

          REPLACE

          INTO TABLE test_load

          FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

          TRAILING NULLCOLS

          (

          deptno  "get_deptno(:deptno)",

          empid,

          ename

          )

          begindata

          SALES,1234,ABC

          RESEARCH,2345,DEF

          OPERATIONS,4567,GHI

           

          --sqlloader command

          sqlldr hr/hr control=test_load.ctl

          --OUTPUT

           

          select *  from   test_load;

          30    1234    ABC

          20    2345    DEF

          40    4567    GHI