FTP from within PL/SQL using Java stored procedure
Krassimir Boyanov
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
damorgan
wheel?
Daniel Morgan
Thomas Kyte
kboy...@printrak.com says...
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.
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.
--
Thomas Kyte (tk...@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
Krassimir Boyanov
> Why aren't you using the Oracle built-in package to do this? Why re-invent the
> wheel?
Can you give an example which package does FTP?
Thanks,
Krassimir
Krassimir Boyanov
> In article <15071bed.02032...@posting.google.com>,
> kboy...@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
Thomas Kyte
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.
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$tk...@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$tk...@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$tk...@ORA817DEV.US.ORACLE.COM> set serveroutput on
ops$tk...@ORA817DEV.US.ORACLE.COM> exec dbms_java.set_output( 20000 );
PL/SQL procedure successfully completed.
ops$tk...@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.
Ian Terence Botham
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 <tk...@oracle.com> wrote in message news:<a7tl5...@drn.newsguy.com>...
damorgan
done it.