Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: FTP from within PL/SQL using Java stored procedure

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

From: Ian Terence Botham <botham_ian_at_hotmail.com>
Date: 27 Mar 2002 22:36:49 -0800
Message-ID: <709f185b.0203272236.22421f87@posting.google.com>


Use Perl... Unless you are too particular using Java.... If you want a perl script , let me know I can send you the code. I tried using the Java/Pl/sql thing but it was very complex...Perl is the best bet .. call it from a shell and run it as a cron job....

Thanks
Ian....

Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<a7tl5m0e3j_at_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.
Received on Thu Mar 28 2002 - 00:36:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US