Re: FTP from within PL/SQL using Java stored procedure

From: Krassimir Boyanov <kboyanov_at_printrak.com>
Date: 27 Mar 2002 14:29:23 -0800
Message-ID: <15071bed.0203271429.4034a782@posting.google.com>

Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<a7t0il01nv5_at_drn.newsguy.com>...
> In article <15071bed.0203270911.141aa974_at_posting.google.com>,
> kboyanov_at_printrak.com says...
> >
> >Hi,
> >
> >I am using Oracle 8i.
> >I want to use my FTPClient Java class from within Pl/SQL.
> >I have tested Java class from Java itself, it works fine.
> >NOTE: I want to instanciate the methods, not make them static.
> >
> >I created PL/SQL object wrapping the method for connect(server, user, pass),
> >and then call this method from within anonymous PL/SQL block.
> >
> >The result is getting error:
> >"ORA-00932:Incosistent Datatypes"
> >
> >Any suggestions.
> >Many TIA.
> >
> >Krassimir
>
>
> 1) you have to have at least ONE static method, that is a requirement. This
> static method is what you can bind to from SQL. You need a java instantiated
> object in order to "start" - just like "main" in a standalong java app.

I don't agree. For example I have working Java class, that I took from Oracle Docs and added some additional methods. It works fine. And does not have any static methods at all. Let me know if you need to take a look.
>
> 2) so, lets see the java method specification you are binding to and the sql
> binding wrapper you have.... without the code, it is very hard to say.

• This is the Oracle object: ------------

CREATE OR REPLACE TYPE itf_ftp_type AS OBJECT (

   server VARCHAR2(255),
   usr VARCHAR2(255),
   pass VARCHAR2(255),
   MEMBER PROCEDURE ftp_open (server VARCHAR2, usr VARCHAR2, pass VARCHAR2)

     AS LANGUAGE JAVA
     NAME 'FtpClient.open(java.lang.String, java.lang.String,

java.lang.String)',
)

• This is the call: ------------ DECLARE v_ftp itf_ftp_type; server VARCHAR2(255):='OracleServer'; usr VARCHAR2(255):='anonymous'; pass VARCHAR2(255):='epic'; BEGIN
  • assign a value to emp_id SELECT VALUE(ftp) INTO v_ftp FROM itf_ftps ftp WHERE ftp.server='OracleServer';

  v_ftp.ftp_open (server, usr, pass);   

  UPDATE itf_ftps ftps SET ftps = v_ftp WHERE ftps.server='OracleServer';
  commit;
 exception
  when others then

     DBMS_OUTPUT.PUT_LINE (SQLCODE);
     DBMS_OUTPUT.PUT_LINE (SQLERRM);

END; Thanks,
Krassimir Received on Wed Mar 27 2002 - 16:29:23 CST