you are a new oracle dba or developer and you would like to know how a java stored procedure can be called from within an oracle PL/sql procedure or anonymous block.
read this article for an initial understanding through an example.
| Subject: | How to Return a BLOB to PL/SQL From a Java Stored Procedure | |||
| Doc ID: | 251045.1 | Type: | HOWTO | |
| Modified Date: | 16-OCT-2003 | Status: | PUBLISHED | |
PURPOSE
-------
The purpose of this document is to show how to return a BLOB or a large byte[] stream
using a temporary BLOB from a Java Stored Procedure to PL/SQL in Oracle 9i.
SCOPE & APPLICATION
-------------------
This Note is for PL/SQL developers who needs
to build a PL/SQL wrapper for a Java Stored Procedure
that is returning a BLOB or a large byte[] stream.
How to Return a BLOB or byte[] stream to PL/SQL From a Java Stored Procedure
-----------------------------------------------------------------------------
In this sample, the scott/tiger schema is used to load
the java source necessary, compile and map a PL/SQL wrapper
to the Java Stored Procedure and execute the code.
Step 1:
-------
Load the Java Stored Procedure into the scott/tiger schema of the database
using the following source.
- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -
CREATE OR REPLACE JAVA SOURCE NAMED "RetBlob" AS
import java.sql.*;
import oracle.sql.BLOB;
import java.io.*;
public class RetBlob {
// This byte stream could be binary data like a BLOB or a large
// byte[] stream > 32K of data
static byte[] data = {0,1,2,3,4,5,6,7,8,9};
boolean x;
public static BLOB quote() {
try {
Connection conn = DriverManager.getConnection("jdbc:default:connection");
// Create a temporary BLOB
BLOB my_ReturnBlob = BLOB.createTemporary(conn, true, BLOB.DURATION_SESSION);
try {
// Write data to the temporary BLOB
OutputStream outstream = my_ReturnBlob.getBinaryOutputStream();
outstream.write(data);
outstream.close();
}
catch (IOException f)
{
System.err.println(f.getMessage());
}
// The return must be within the scop of the try and catch block.
return my_ReturnBlob;
}
catch(SQLException e)
{
System.err.println(e.getMessage());
return null;
}
} // End quote
};
/
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -
Step 2:
-------
Verify the load compile the Java Stored Procedure
ALTER java source "RetBlob" compile;
show errors java source "RetBlob"
Step 3:
-------
Create a PL/SQL procedure that maps against the Java Stored Procedure
in the scott/tiger schema.
- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -
CREATE OR REPLACE FUNCTION PL_WRAPPER RETURN BLOB
AS LANGUAGE JAVA
NAME 'RetBlob.quote() return oracle.sql.BLOB[]';
/
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -
Step 4: Example of Using.
-------
Use the following anonymous PL/SQL block to execute the Java Stored Procedure
- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -
set serveroutput on
declare
x blob;
begin
select pl_wrapper into x from dual;
dbms_output.put_line('Length: '||dbms_lob.getlength(x));
dbms_lob.freetemporary(x);
end;
/
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -
Sample Output:
SQL> declare
2 x blob;
3 begin
4 select pl_wrapper into x from dual;
5 dbms_output.put_line('Length: '||dbms_lob.getlength(x));
6 dbms_lob.freetemporary(x);
7 end;
8 /
Length: 10
PL/SQL procedure successfully completed.
SQL>
RELATED DOCUMENTS
-----------------
Note 114009.1 Storing and Loading Java Classes into the Database
Note 68349.1 8i: JDBC Support for BLOB/CLOB
Note 103492.1 Example: Using the JDK 1.2 (JDBC 2.0) Clob and Blob Classes
Note 95816.1 Example: Use Streaming to Insert Media (Binary) Data to BLOB via JDBC
Oracle9i Java Stored Procedures Developer's Guide Release 2
