8 Replies Latest reply: Dec 6, 2012 1:17 AM by kaminanikamini RSS

    sql*loader with forms

    kaminanikamini
      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
          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
            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
              >
              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
                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
                  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
                    I found this links bfore posting here.
                    But i dont understand it properly
                    • 7. Re: sql*loader with forms
                      Prabodh
                      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
                        THANKS PRABODH...