Hello!
How could I retrieve an OUT parameter of type Oracle Object (create type
....) from a PL/SQL procedure
via JDBC?
Is it possible? In oracle demos I couldn't find a such examples....
Thank you.
There are LOADS of examples on the oracle website.
however, as you cannot find any!!!.
/*
* "[Example]Returning a nested table from PLSQL to JAVA"
This example uses a JDBC thin client to call a PLSQL stored
function which returns an address object list as a nested table.
***NOTE*** This sample only works with Oracle8i.
It requires the use of at least the JDBC 815 driver
To try it out cut and paste the following into three different files:
1. create.sql - this creates the required sql types
2. getaddr.sql- this creates the plsql function
3. test.java - this calls the plsql and displays the resultset
Run 1&2 under the SCOTT/TIGER schema
********** CREATE.SQL ********************************
create or replace type address as object (
street varchar2(60),
city varchar2(30),
state char(2),
zip_code char(5) );
/
create or replace type address_list as table of address;
/
********** GETADDR.SQL *******************************
-- This stored function requires the creation of two SQL types in the
database
-- 1. An address type
-- 2. An address_list type
create or replace function get_address_list
return address_list is
v_table address_list:=address_list();
i binary_integer;
begin
-- neccessary to initialize table with a certain size
v_table.extend(5);
-- fill nested table with values
for i in 1..5 loop
-- call address object constructor to fill all the fields
for each row
v_table(i):=address('x','y','z',to_char(i));
end loop;
--return the nested table
return v_table;
end get_address_list;
/
*********************** test.java ***************************************
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class test {
Connection conn;
OracleCallableStatement cstmt;
ARRAY array;
ResultSet rs;
// JDBC Implementation specific connect string
// replace hostname, port, and SID with appropriate values
String connectString="jdbc
racle:thin:scott/tiger@hostname
ort:SID";
public static void main(String<> args) {
test t=new test();
}
public test() {
int index;
try {
// Register JDBC driver and get connection to Oracle8i RDBMS
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn=DriverManager.getConnection(connectString);
// Call the function which returns a nested table
cstmt=(OracleCallableStatement)conn.prepareCall("{ ? = call
get_address_list() }");
// The return parameter is an ARRAY type (nested table)
// The database sql type is ADDRESS_LIST
cstmt.registerOutParameter(1,OracleTypes.ARRAY,"ADDRESS_LIST");
cstmt.execute();
// Get the return value and covert it into a JDBC ResultSet
array = (ARRAY) ((OracleCallableStatement)cstmt).getOracleObject(1);
rs=array.getResultSet();
// Loop through ResultSet rows
while(rs.next()) {
// Get the object that is in the address_list ( SQL TYPE "ADDRESS" )
// 1st column is the row index
// 2nd column is the actual object
STRUCT obj= (STRUCT)rs.getObject(2);
// Get the column attributes for the object
Object<> attrs=obj.getAttributes();
// get each of the object columns
// It is the programmers responsibility to know specifically
// what these types are. In this case they are all strings.
String s1=(String)attrs<0>;
String s2=(String)attrs<1>;
String s3=(String)attrs<2>;
String s4=(String)attrs<3>;
System.out.println(s1+","+s2+","+s3+","+s4);
}
} catch(Exception e) { e.printStackTrace(); }
}
}*/
/*
Calling
ScratchBook.transformResult(this, res);
//converting a result set to an object
public static Object transformResult(Object ref, java.sql.ResultSet res)
{
try
{
int col = res.getMetaData().getColumnCount();
String colName = null;
for (int i = 1; i <= col; i++)
{
colName = res.getMetaData().getColumnName(i);
try
{
java.lang.reflect.Field f =
ref.getClass().getDeclaredField(colName.toLowerCase());
if (f != null)
{
if (f.getType() ==
java.sql.Date.class)
{
f.set(ref,
res.getDate(colName));
}
else
if (f.getType() ==
int.class)
{
f.setInt(ref,
res.getInt(colName));
}
else
{
f.set(ref,
res.getString(colName));
}
}
}
catch (NoSuchFieldException nfe)
{
akaan.util.Debug.printDebugMessage("ScratchBoo
k.transformResult", "skipping " + colName);
}
catch (IllegalArgumentException il)
{
akaan.util.Debug.printDebugMessage("ScratchBoo
k.transformResult", "illegal value for " + colName);
}
}
}
catch (Throwable t)
{
akaan.util.Debug.printThrowable("ScratchBook.transformResult",
t);
}
return null;
}
*/
/*
*If you add the ROWID pseudocolumn to a query you can retrieve
it in JDBC with the ResultSet getString entrypoint. You can also
bind a ROWID to a preparedStatement parameter with the setString
entrypoint.
This allows in-place updates, as in the following example:
Statement stmt = conn.createStatement ();
// Query the employee names with "FOR UPDATE" to lock the rows.
// Select the ROWID to identify the rows to be updated.
ResultSet rset =
stmt.executeQuery ("select ENAME, ROWID from EMP for update");
// Prepare a statement to update the ENAME column at a given
ROWID
PreparedStatement pstmt =
conn.prepareStatement ("update EMP set ENAME = ? where ROWID =
?");
// Loop through the results of the query
while (rset.next ())
{
String ename = rset.getString (1);
String rowid = rset.getString (2); // Get the ROWID as a
String
pstmt.setString (1, ename.toLowerCase ());
pstmt.setString (2, rowid); // Pass ROWID to the update
statement
pstmt.executeUpdate (); // Do the update
}
//this could be used as follows
("begin open?=getsupplierlist();closesupplierlistcur(); end")
//so that you could call an sql routine using a ref cursor &
//close it at the sametime, whilst still returning the result set
The Oracle JDBC driver supports bind variables of type REFCURSOR. A
REFCURSOR is represented by a JDBC ResultSet. Use the getCursor
method of the CallableStatement to convert a REFCURSOR value
returned by a PL/SQL block into a ResultSet. JDBC lets you call a
stored procedure that executes a query and returns a results set.
Cast the corresponding CallableStatement to
oracle.jdbc.driver.OracleCallableStatement to use the getCursor method.
CallableStatement cstmt;
ResultSet cursor;
// Use a PL/SQL block to open the cursor
cstmt = conn.prepareCall
("begin open ? for select ename from emp;
end;");
cstmt.registerOutParameter (1, OracleTypes.CURSOR);
cstmt.execute ();
cursor = ((OracleCallableStatement)cstmt).getCursor (1);
// Use the cursor like a normal ResultSet
while (cursor.next ())
{System.out.println (cursor.getString (1));}
*/
