Skip to content

Advanced PL/Sql : Calling Java stored procedures from within oracle PL/SQL-complete reference for the new oracle dba




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
 
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.