SQL*Loader from PL/SQL using Java SP not working

From: LR <lrako_at_hotmail.com>
Date: 18 Apr 2002 01:37:03 -0700
Message-ID: <dcc2db96.0204180037.59c8b77b@posting.google.com>

Hello,

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

Coded, compiled, loaded the Ut.class successfully into the DB.

For testing purposes, I created a 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 R. Received on Thu Apr 18 2002 - 03:37:03 CDT