Home » Developer & Programmer » JDeveloper, Java & XML » Re: Procedure that has OUT parameter as vArray giving error :ORA-06531: Reference to uninitialized
| Re: Procedure that has OUT parameter as vArray giving error :ORA-06531: Reference to uninitialized [message #91060] |
Tue, 12 March 2002 12:40  |
subbarao
Messages: 9
Registered: March 2002
|
Junior Member |
|
|
Here is an example for you. Hope it is of some use. I just made this working.
Overview
--------
A common question is:
"Can I call stored procedures with table of PL/SQL record
parameters from a JDBC Driver?".
The answer to this question is that it is not possible, however, you can
pass a parameter which is an array of objects to emulate this behavior.
This article contains information on how to do this.
Program Notes
-------------
o This program can be run with any of the three forms of Oracle JDBC
driver.
o Run the script noted in the comments of the program to create the type
and the procedure that is called.
o This program uses the most basic method of calling objects,
via Oracle.sql.STRUCT.
References
----------
"Oracle8i JDBC Developers Guide and Reference" (A64685-01)
Program
-------
import java.sql.*;
import java.io.*;
import java.util.*;
import java.math.BigDecimal;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class callInOutStructArray extends Object {
/*
This program demonstrates how to emulate calling a stored procedure
with PL/SQL table of record parameters via JDBC. You cannot call a
PL/SQL table of records parameter directly, however, you can use an
Object with the same structure as your table of records.
The Script used to create the procedure in this example is as follows:
drop type rectab;
create or replace type rectype as object(col1 varchar2(10),col2
varchar2(10));
/
create or replace type rectab as table of rectype;
/
create or replace package ioStructArray as
procedure testproc(iorec in out rectab,orec out rectab);
end ioStructArray;
/
create or replace package body ioStructArray as
procedure testproc(iorec in out rectab,orec out rectab) is
begin
orec := iorec;
for i in 1..iorec.count loop
iorec(i).col1 := orec(i).col2;
iorec(i).col2 := orec(i).col1;
end loop;
end testproc;
end ioStructArray;
/
*/
public static void main(String[[]] args) throws SQLException {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// Connect to the database
Connection conn =
DriverManager.getConnection ("jdbc:oracle:oci8:@S692815.WORLD",
"scott", "tiger");
// First, declare the Object arrays that will store the data.
Object [[]] p1recobj = {"FIRST","LAST"};
Object [[]] p2recobj = {"SECOND","LAST"};
Object [[]] p3recobj;
Object [[]] p4recobj;
// Declare the Object Arrays to hold the STRUCTS.
Object [[]] p1arrobj;
Object [[]] p2arrobj;
// Declare two descriptors, one for the ARRAY TYPE
// and one for the OBJECT TYPE.
StructDescriptor
desc1=StructDescriptor.createDescriptor("RECTYPE",conn);
ArrayDescriptor desc2=ArrayDescriptor.createDescriptor("RECTAB",conn);
// Create the STRUCT objects to associate the host objects
// with the database records.
STRUCT p1struct = new STRUCT(desc1,conn,p1recobj);
STRUCT p2struct = new STRUCT(desc1,conn,p2recobj);
// Initialize the Input array object - to an array of STRUCT Objects.
p1arrobj = new Object [[]]{p1struct,p2struct};
// Set up the ARRAY object.
ARRAY p1arr = new ARRAY(desc2,conn,p1arrobj);
ARRAY p2arr;
// Declare the callable statement.
// This must be of type OracleCallableStatement.
OracleCallableStatement ocs =
(OracleCallableStatement)conn.prepareCall("{call
iostructarray.testproc(?,?)}");
// The first parameter is in out so we have to use setARRAY to
// pass it to the statement
ocs.setARRAY(1,p1arr);
// The first parameter is in out, so we have to Register the
// parameter as well.
// Note the re use of the TYPE.
ocs.registerOutParameter(1,OracleTypes.ARRAY,"RECTAB");
// The second paramter is out, so that has to be registered too.
// Note the re use of the TYPE.
ocs.registerOutParameter(2,OracleTypes.ARRAY,"RECTAB");
// Execute the procedure
ocs.execute();
// Associate the returned arrays with the ARRAY objects.
p1arr = ocs.getARRAY(1);
p2arr = ocs.getARRAY(2);
// Get the data back into the data arrays.
p1arrobj = (Object [[]])p1arr.getArray();
p2arrobj = (Object [[]])p2arr.getArray();
// Get the data records from each array element (which is of type
STRUCT).
p1recobj = ((STRUCT)p1arrobj[[0]]).getAttributes();
p2recobj = ((STRUCT)p1arrobj[[1]]).getAttributes();
p3recobj = ((STRUCT)p2arrobj[[0]]).getAttributes();
p4recobj = ((STRUCT)p2arrobj[[1]]).getAttributes();
// Show the results:
System.out.println("First Object is now "+p1recobj[[0]]+" and
"+p1recobj[[1]]);
System.out.println(" "+p2recobj[[0]]+" and
"+p2recobj[[1]]);
System.out.println("Second Object is now "+p3recobj[[0]]+" and
"+p3recobj[[1]]);
System.out.println(" "+p4recobj[[0]]+" and
"+p4recobj[[1]]);
}
}
Sample Output
-------------
First Object is now LAST and FIRST
LAST and SECOND
Second Object is now FIRST and LAST
SECOND and LAST
|
|
|
|
| Re: Procedure that has OUT parameter as vArray giving error :ORA-06531: Reference to uninitialized [message #91594 is a reply to message #91060] |
Wed, 04 December 2002 04:54  |
Khaleel
Messages: 1
Registered: December 2002
|
Junior Member |
|
|
============== SQL PACKAGE==========
rem
rem $header: SEReport.sql, 2002/01/18 $
rem
rem $author :Shaik Khaleel
rem
rem **********************************************************************************************
rem
rem //This package contains all the social enterprenuer of development space related reports
rem
rem
rem //Starting of the package spec
rem
rem
CREATE TYPE NUMLIST IS TABLE OF NUMBER(6);
/
create or replace package TESTPACK as
/* Local type declarations */
TYPE fetchcursor is REF CURSOR;
--TYPE NUMLIST IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER;
FUNCTION CHECK_USERS(uid IN NUMBER, rid IN numlist,rid1 OUT numlist)
RETURN VARCHAR2;
end TESTPACK;
/
show err;
rem
rem
rem //End of the Package spec
rem
rem
rem**************************************************************************************************
rem
rem
rem
rem //Starting of the package body
rem
CREATE OR REPLACE PACKAGE BODY TESTPACK AS
FUNCTION CHECK_USERS(uid IN NUMBER, rid IN numlist,rid1 OUT numlist)
RETURN VARCHAR2 IS
list NUMLIST ;
x VARCHAR2(5):= 'NO';
BEGIN
list := rid;
rid1 := rid;
FOR I IN 1..list.COUNT
LOOP
IF list(i)= uid THEN
x := 'YES';
END IF;
END LOOP;
RETURN X;
END CHECK_USERS;
END TESTPACK;
/
show err;
rem
rem
rem //End of the package
rem
==========================================================================
================== JAVA PROGRAM TO CALL THAT ==============
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
import java.io.*;
public class Java_PlSQL_Array
{
public static void main(String arg[[]])
{
try
{
String connectionString="jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))(CONNECT_DATA = (SID = sid)))";
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection(connectionString,"user","password");
System.out.println("connected");
OracleCallableStatement cs = ( OracleCallableStatement ) con.prepareCall( "{ ? = call TESTPACK.CHECK_USERS(?, ?, ?) }" );
int[[]] rid = { 10000, 20000, 30000, 35642, 40000, 50000 };
ARRAY ary = new ARRAY( ArrayDescriptor.createDescriptor( "NUMLIST", con ), con, rid );
cs.registerOutParameter( 1, Types.VARCHAR );
cs.setInt( 2, 1000 );
cs.setObject( 3, ary, Types.ARRAY );
cs.registerOutParameter( 4, Types.ARRAY,"NUMLIST" );
cs.executeQuery();
System.out.println(cs.getString(1));
ARRAY array = (ARRAY) cs.getObject(4);
ResultSet array_rset = array.getResultSet();
while(array_rset.next())
System.out.println(array_rset.getString(1)+" "+array_rset.getString(2));
/*
ARRAY array = (ARRAY) cs.getObject(4);
Object[[]] elements = array.getArray();
*/
System.out.println("completed");
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
|
|
|
|
Goto Forum:
Current Time: Sat Apr 20 03:39:28 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
