OUT as Table in StoreProcedure

[Prashant Saraf]

Hi,
I have an oracle store procedure which return TABLE RECORD
here is procedure

 TYPE chlrecord IS RECORD 
  (
        instance_id             csi_ii_relationships.object_id%type,
        relationship_id         csi_ii_relationships.relationship_id%type,
        relationship_type_code  csi_ii_relationships.relationship_type_code%type,
  };
TYPE chltable IS TABLE OF chlrecord    INDEX BY BINARY_INTEGER;

PROCEDURE getChildRelationsByInstanceId (
                                             p_object_id_in      IN  NUMBER,
                                             outchalrel     OUT      chltable                                     
);

I want to know how to read OUT of above procedure.

Thanks

[Jan Cumps]

Do you want to know how to read this OUT parameter in Java, with JDBC?

[Prashant Saraf]

Jan Cumps wrote:Do you want to know how to read this OUT parameter in Java, with JDBC?

I do know how to read OUT for Procedure. I want to know how to read the OUT of RECORD TABLE type?

[Jan Cumps]

Prashant,

my excuses, but I still don't understand it.
Where do you want to read the OUT parameter?

In a pl/sql program?

In a java program?

Somewhere else?

Can you post the piece of code where you are trying to read the OUT parameter?

[Prashant Saraf]

Jan Cumps wrote:Prashant,

my excuses, but I still don't understand it.
Where do you want to read the OUT parameter?

In a pl/sql program?

In a java program?

Somewhere else?

Can you post the piece of code where you are trying to read the OUT parameter?

Sorry Jan, I am using JDBC to read the OUT Parameter.

Java Code is

String proc3StoredProcedure = "{ call proc3(?, ?, ?) }";
        CallableStatement cstmt = conn.prepareCall(proc3StoredProcedure);        
        cstmt.setLong(1, instanceId.longValue());
        [b]cstmt.registerOutParameter()[/b]

I want to know what Type to use in registerOutParameter method.

Thanks
Prashant

[Jan Cumps]

The procedure has 2 parameters and is called getChildRelationsByInstanceId , and the JDBC code shows three parameters (question marks) and is called proc3.

[Jan Cumps]

Here is the way to retrieve user defined types:
http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/objects/ObjectTypes.html

If you can write your stored procedure so that it returns a cursor to that table, you can programs your JBDC like this:
http://www.enterprisedt.com/publications/oracle/result_set.html

[Prashant Saraf]

hi
here is my code

            String proc3StoredProcedure = "{?=call MY_PKG.MyInstanceId(?)}";
            CallableStatement cstmt = conn.prepareCall(proc3StoredProcedure);                    
            cstmt.registerOutParameter(1, OracleTypes.STRUCT);
            cstmt.setLong(2, instanceId.longValue());
            cstmt.execute();

On cstmt.execute(); it throws following exception.

java.sql.SQLException: ORA-03115: unsupported network datatype or representation

my procedure returns TABLE of RECORD.

Please help