This discussion is archived
8 Replies Latest reply: Dec 5, 2012 11:17 PM by kaminanikamini RSS

sql*loader with forms

kaminanikamini Newbie
Currently Being Moderated
I am using oracle forms 10g on windows 7 32 bit.

i want to load data from oracle form to oracle database.
i have written the following code on Push Buttons When-Button-Pressed trigger.

declare
usid varchar2(10):= get_application_property(username);
pwd  varchar2(10):= get_application_property(password);
db      varchar2(10):= get_application_property(connect_string);
v_ctl_file  text_io.file_type;
v_live_path varchar2(1000):='D:\';
v_bat_file  text_io.file_type;
               
begin
     
v_ctl_file   := text_io.FOPEN(v_live_path||'test.ctl', 'w');
text_io.PUT_LINE (v_ctl_file, 'LOAD DATA');
text_io.PUT_LINE (v_ctl_file, 'INFILE '''||v_live_path||'data.csv''');
text_io.PUT_LINE (v_ctl_file, 'INTO TABLE test');
text_io.PUT_LINE (v_ctl_file, 'fields terminated by "," optionally enclosed by ''"'' ');
text_io.PUT_LINE (v_ctl_file, '(id,name,salary)') ;
text_io.FCLOSE   (v_ctl_file);

v_bat_file   := text_io.FOPEN(v_live_path||'load_data.bat', 'w');
text_io.PUT_LINE (v_bat_file, 'sqlldr userid  = '|| usid || '/' ||pwd || '@' ||db ||
*' ERRORS=1000000 control = '|| v_live_path||'test.ctl log = '|| v_live_path||'test.log');*
text_io.FCLOSE(v_bat_file);
          
host(v_live_path||'load_data.bat');

Exception when others then
message(sqlerrm||dbms_error_text);
message(' ');
end;


SO my control file content is

LOAD DATA
INFILE 'D:\data.csv'
INTO TABLE test
fields terminated by "," optionally enclosed by '"'
*(id,name,salary)*

Batch File content:
sqlldr userid  = SCOTT/TIGER@orcl ERRORS=1000000  control = D:\test.ctl log = D:\test.log


But on my command prompt i am getting the following messsage

SQLLDR is not recognised as an internal or external command,operable prgram or batch file._

Thanks
  • 1. Re: sql*loader with forms
    Prabodh Guru
    Currently Being Moderated
    But on my command prompt i am getting the following messsage
    SQLLDR is not recognised as an internal or external command,operable prgram or batch file.>

    The solutions to this are:
    1. Include the path of the SQLLDR executable (typically %ORACLE_HOME%\bin ) in your PATH env variable.E.g.
    SET PATH=%ORACLE_HOME%\bin;%PATH%
    sqlldr userid = SCOTT/TIGER@orcl ....
    Or
    2. Specify the full path in the SQLLDR command.
    [path]sqlldr userid = SCOTT/TIGER@orcl ERRORS=100000....
    Cheers,

    PS: Since we are talking 10g , remember that the HOST command will run AS. So the path is as on AS, not client.

    Edited by: Prabodh on Dec 5, 2012 4:54 PM
  • 2. Re: sql*loader with forms
    kaminanikamini Newbie
    Currently Being Moderated
    i have done this.but on command prompt now i m getting the following message

    My Batch file----->

    C:\oracle\ptoduct\10.2.0\db_1\BIN\sqlldr.exe SCOTT/TIGER@orcl Errors=100000 control=D\test.ctl log=D:\test.log



    Message 2100 not found;No message file for product=RDBMS,facility=ULMessage 2100 not found;No Message file for product=RDBMS,fcility=UL

    Thank u ..
  • 3. Re: sql*loader with forms
    Prabodh Guru
    Currently Being Moderated
    >
    i have done this.but on command prompt now i m getting the following message

    My Batch file----->

    C:\oracle\ptoduct\10.2.0\db_1\BIN\sqlldr.exe SCOTT/TIGER@orcl Errors=100000 control=D\test.ctl log=D:\test.log


    Message 2100 not found;No message file for product=RDBMS,facility=ULMessage 2100 not found;No Message file for product=RDBMS,fcility=UL
    >
    As pointed out in earlier post, the HOST command is running on AS, so everything has to be in the context of the AS.

    E.g.
    a. you need to have a tnsnames.ora entry for SID=orcl
    b. control=D\test.ctl log=D:\test.log will be referring to the drive:folder of the AS.
    c. You control file and data file also need to be on AS.

    Are all these taken care off?

    Cheers,
  • 4. Re: sql*loader with forms
    kaminanikamini Newbie
    Currently Being Moderated
    yes i am running my oracle form on the server not on client machine.
    and in tnsnames.ora file SID is set to orcl.
    When i run sqlldr directly from command prompt it perfectly runs and load data in to table.

    But when i run through Orcle forms it shows the above message.
  • 5. Re: sql*loader with forms
    Prabodh Guru
    Currently Being Moderated
    yes i am running my oracle form on the server not on client machine.
    and in tnsnames.ora file SID is set to orcl.
    When i run sqlldr directly from command prompt it perfectly runs and load data in to table.

    But when i run through Orcle forms it shows the above message.>
    Most probably related to ORACLE_HOME being not correct.
    See {thread:id=2172420} and {thread:id=427392}

    Cheers,
  • 6. Re: sql*loader with forms
    kaminanikamini Newbie
    Currently Being Moderated
    I found this links bfore posting here.
    But i dont understand it properly
  • 7. Re: sql*loader with forms
    Prabodh Guru
    Currently Being Moderated
    Modify your batch file as follows and see if it works
    SET ORACLE_HOME=C:\oracle\ptoduct\10.2.0\db_1
    SET PATH=%ORACLE_HOME%\bin;%PATH%
    sqlldr userid = SCOTT/TIGER@orcl ERRORS=1000000 control = D:\test.ctl log = D:\test.log
    Cheers,
  • 8. Re: sql*loader with forms
    kaminanikamini Newbie
    Currently Being Moderated
    THANKS PRABODH...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points