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

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 27 Mar 2002 15:38:30 -0800
Message-ID: <a7tl5m0e3j@drn.newsguy.com>

In article <15071bed.0203271429.4034a782_at_posting.google.com>, kboyanov_at_printrak.com says...
>
>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 getti

ng 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.

Ahh, I see - you are using an object type (not many people doing that for some reason). I assumed a package or even plsql standalone procedures for that matter...

You are correct, the one time you can map to non-static methods is from a member function in a type.

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/java.817/a81358/03_pub2.htm#36009

>>
>> 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

Well, you didn't show us what FtpClient is and that is where the problem lies -- it must implement the interface SQLData. Using SQLData, we stream the member attributes from SQL to Java and back. You must have:

o getSQLTypeName method
o readSQL method
o writeSQL method

Your example could be:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace and compile   2 java source named "FtpClient"
  3 as
  4 import java.sql.*;
  5
  6 public class FtpClient implements SQLData   7 {
  8 private String server;
  9 private String usr;
 10 private String pass;
 11
 12 public void open()
 13 {
 14 System.out.println( "I was called " + server + "," + usr + "," + pass );  15 }
 16
 17 String sql_type;
 18
 19 public String getSQLTypeName() throws SQLException  20 {
 21 return sql_type;
 22 }
23 public void readSQL( SQLInput stream, String typeName ) throws SQLException
 24 {

 25          sql_type = typeName;
 26          server = stream.readString();
 27          usr    = stream.readString();
 28          pass   = stream.readString();

 29 }
 30
 31 public void writeSQL( SQLOutput stream ) throws SQLException  32 {

 33          stream.writeString(server);
 34          stream.writeString(usr);
 35          stream.writeString(pass);

 36 }
 37
 38 }
 39 /

Java created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> CREATE OR REPLACE TYPE itf_ftp_type AS OBJECT   2 (

  3     server VARCHAR2(255),
  4     usr VARCHAR2(255),
  5     pass VARCHAR2(255),
  6     MEMBER PROCEDURE ftp_open
  7     AS LANGUAGE JAVA
  8     NAME 'FtpClient.open()'

  9 )
 10 /
Type created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set serveroutput on ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec dbms_java.set_output( 20000 ); PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> DECLARE   2 v_ftp itf_ftp_type;
  3 BEGIN
  4
  5 v_ftp := itf_ftp_type( 'aria', 'tkyte', 'xxx' );   6
  7 v_ftp.ftp_open();
  8 END;
  9 /
I was called aria,tkyte,xxx

PL/SQL procedure successfully completed.

You don't need to pass the server/user/password around -- they are instance variables and are streamed in and out....

Hope this helps.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Wed Mar 27 2002 - 17:38:30 CST