SQL*Loader from PL/SQL thru JSP does not work

From: LR <lrako_at_hotmail.com>
Date: 15 Apr 2002 09:08:20 -0700
Message-ID: <dcc2db96.0204150808.177b548@posting.google.com>

Hi everybody,

I need to run SQL*Loader from PL/SQL.
e.g :
sqlldr userid=lrako/rako123 control=/eucon2/apps/germany.ctl sqlldr userid=lrako/rako123 control=/eucon2/apps/france.ctl (...)

Decided to go through Java Stored Procedures. Read the article on http://asktom.oracle.com/pls/ask/f?p=4950:8:691333::NO::F4950_P8_DISPLAYID,F4950_P8_B:952229840241,Y

and coded, compiled, loaded the class successfully into the DB.

    import java.io.*;
    import java.lang.*;
    public class Ut extends Object
    {     

     public static int RunThis(String[] args)


     {

     Runtime rt = Runtime.getRuntime();
     int        rc = -1;
   
     try


     {

        Process p = rt.exec(args[0]);
   
        int bufSize = 4096;
        BufferedInputStream bis =
         new BufferedInputStream(p.getInputStream(), bufSize);
        int len;
        byte buffer[] = new byte[bufSize];
   
        while ((len = bis.read(buffer, 0, bufSize)) != -1)
           System.out.write(buffer, 0, len);
   
        rc = p.waitFor();
     }
     catch (Exception e)


     {

        e.printStackTrace();
        rc = -1;
     }
     finally


     {

        return rc;
     }
     }

   }

For testing purposes, I created a basic RunUtil class.

import Ut ;
  public class RunUtil
{    

      public static void main (String args[])     {

       String res ;
       Ut os = new Ut() ;
       res = os.RunThis(args) ;
       System.out.println(res) ;  

    }

}

From HP-UX command line, I issue:  

/opt/java1.2/bin/java RunUtil "sqlldr userid=lrako/rako123 control=/eucon2/apps/germany.ctl"

works fine. My table is loaded succesfully.

Then I connect to my db using lrako/rako123_at_eucon2. I have CONNECT, RESOURCE privileges.

Then I publish the class to PL/SQL

create or replace

   function RUN_CMD( p_cmd in varchar2) return number    as
    language java
    name 'Ut.RunThis(java.lang.String[]) return integer';

Then I'm granting the permissions, using a SYS account. (I think this is where I'm going wrong somewhere).

begin

        dbms_java.grant_permission
    ( 'LRAKO',

     'java.io.FilePermission',
     '/eucon2/8.0.6/bin/sqlldr',
     'execute');

      dbms_java.grant_permission
        ('LRAKO',
        'java.lang.RuntimePermission',
        '*',
        'writeFileDescriptor' );

      dbms_java.grant_permission
        ('LRAKO',
        'java.lang.RuntimePermission',
        '*',
        'readFileDescriptor' );

      dbms_java.grant_permission
        ('LRAKO',
        'java.io.FilePermission',
        '/eucon2/apps/germany.ctl',
        'read,write,delete' );

      dbms_java.grant_permission
        ('LRAKO',
        'java.io.FilePermission',
        '/eucon2/apps/germany.dat',
        'read,write,delete' );

dbms_java.set_output(1000000);   

end;
/

OK. And finally.

SQL> declare
  2 x varchar2(10424) ;
  3 begin
  4 dbms_java.set_output(1000000);
  5
  6 x:=Run_CMD('/eucon2/8.0.6/bin/sqlldr userid=lrako/rako123 control=/eucon2/apps/germany.ctl');
  7 end ;
  8
  9
 10 /
SQL*Loader: Release 8.0.6.3.0 - Production on Mon Apr 15 17:01:12 2002 (c) Copyright 1999 Oracle Corporation. All rights reserved.

PL/SQL procedure successfully completed.

And there it is not working. Nothing was loaded in my table. Notice that I could see the SQL*Loader prompt.

Can anybody help ?

Regards,

Lalaina Received on Mon Apr 15 2002 - 11:08:20 CDT