Re: Using java to move large objects/data over db link?

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 13 Sep 2006 13:49:18 -0700
Message-ID: <1158180558.236512.31290@p79g2000cwp.googlegroups.com>

sporb wrote:
> Thanks for your thoughts on this, Steve.
>
> We are constrained to either build views or packages on the remote
> system. In the case of views, your approach will work, but there is a
> strong preference towards using packaged functions and procedures, and
> that is where my efforts are concentrated.
>
> -Bill
>

Bill,

I hope I am understanding what you want to do. Do you not have access to the remote database from your webapp server because of a firewall, or is it procedural? If it is procedural, then what is below may not help.

You can get around what I think is still your problem by building your java code *inside* the database to which you have access. This java code, using Oracle's JVM in the database, would invoke the function in the remote database. Below is a simple example with a 40K clob read (in the same database, but is technically the same as if it weren't) across the wire and printed out...

SQL> connect rep1/rep1
Connected.
SQL> create table t0913(c clob);

Table created.

SQL> declare
  2 l_clob clob;
  3 begin
  4 dbms_lob.createtemporary(l_clob,false);   5 for i in 1..40000 loop
  6 dbms_lob.writeappend(l_clob,1,'*');   7 end loop;
  8 insert into t0913 values(l_clob);   9 end;
 10 /

PL/SQL procedure successfully completed.

SQL> create or replace function rclob return clob as   2 l_clob clob := empty_clob();
  3 begin
  4 select c into l_clob from t0913;
  5 return l_clob;
  6 end;
  7 /

Function created.

SQL> select dbms_lob.getlength(c) from t0913;

DBMS_LOB.GETLENGTH(C)

---

                40000

SQL> connect rep/rep
Connected.
SQL> CREATE OR REPLACE JAVA SOURCE NAMED showRemoteClob AS   2 import java.sql.*;
  3 import java.io.*;
  4 public class showRemoteClob {

  5  	 public static void getClob() {
  6  	   try {
  7  	     Class.forName("oracle.jdbc.driver.OracleDriver");
  8  	     Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/test10g","rep1","rep1");
  9  	     CallableStatement cs = conn.prepareCall("{? = call rclob}");
 10  	     cs.registerOutParameter(1, Types.CLOB);
 11  	     cs.execute();
 12  	     Clob retValue = cs.getClob(1);
 13
 14  	     Reader reader = retValue.getCharacterStream();
 15  	     int c = 0;
 16  	     while ((c = reader.read()) != -1) {
 17  	       System.out.print((char)c);
 18  	     }
 19  	   }
 20  	   catch(Exception e) {
 21  	     System.out.println(e);
 22  	   }
 23  	 }

 24 }
 25 /

Java created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE showRemoteClob AS   2 LANGUAGE java NAME 'showRemoteClob.getClob()';   3 /

Procedure created.

SQL> set serveroutput on
SQL> exec dbms_java.set_output(1000000);

PL/SQL procedure successfully completed.

SQL> exec showRemoteClob

---

---

...snipped

PL/SQL procedure successfully completed.

You could invoke the PL/SQL wrapper in "your" local database from your webapp.

Regards,

Steve Received on Wed Sep 13 2006 - 15:49:18 CDT