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 Go to next message
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 Go to previous message
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();
}

}

}
Previous Topic: writeToFile error in xml parser for pl/sql ( oracle xdk),urgent help
Next Topic: how to insert large value in CLOB field
Goto Forum:
  


Current Time: Sat Jul 24 15:51:43 CDT 2021