Q: PL*SQL stored procedure, oracle objects and Java

Q: PL*SQL stored procedure, oracle objects and Java

Post by Abraham Le » Tue, 16 Dec 2003 06:43:59


Hello,

I've googled hours for detailed information on this topic, but both the
Oracle technical network as well as Google have let me down.

The situation is that I get a resultset (a cursor) from a stored
procedure, but this resultset (a nested table) contains objects created
in PL*SQL.
My question is simple, how to access these objects within the resultset
from Java ?

---

Example:

Calling a PL*SQL stored procedure from Java just like this, which in
this example returns a simple address as string.

// Declare the callable statement
CallableStatement cstmt;

// Declare the command syntax with placeholders
String command = "{call PERSMGT.GETADDRESS(?)}";

// Make the command into a callable statement
cstmt = con.prepareCall(command);

// Initialize command variables
cstmt.registerOutParameter(1, Types.VARCHAR);

// Execute the command
cstmt.execute();

// Retrieving Stored Procedure results
String address = cstmt.getString(1);

// Close the Callable Statement
cstmt.close();

My question is how to access the returned address(es) if the returned
result is not VARCHAR but a nested table of objects like the one shown
below.

CREATE TYPE Address AS OBJECT (
AddrStreet Varchar2(50),
AddrCity Varchar2(30),
AddrZip Number,
AddrCountry Varchar2(10) );

Which type of cstmt.registerOutParameter() is this, and after execution,
how do I access this nested table WITH objects in it? My real problem is
to access the objects within the result. Does anybody know how to do
that? A complete example and source would be very helpful. Thanks in
advance.

Cheers
Abraham
 
 
 

1. Orphan REF cursor returned to VB.NET from Oracle PL/SQL stored procedure?

2. US-TX-ORACLE, PL/SQL, TRIGGERS AND STORED PROCEDURE DEVELOPMENT


-------------------------------------------------------------------------------------
JOB ID:2027107

To reply to this job, please click http://www.yqcomputer.com/
A dedicated web site for ORACLE jobs and resumes!


Company Name:PALADIN Consulting, Inc.

PALADIN Consulting, Inc.

Glen Rose,TX USA



CW-6035-DR

2 people needed ASAP with 5 Years of Oracle, PL/SQL, SQL and a detailed understanding of package features, external
procedures and database triggers.

SQL Language experience and Software Code Analysis and Reverse Engineering Skills. Candidates will develop database
triggers which are part of an existing trigger framework implemented in PL/SQL code and external procedures.

Will be required to analyze the existing trigger framework with minimal documentation to accomplish the assigned task.

This Position is in Glen Rose Texas.
Local Candidates Only, No Sub-Contract and not H-One.
df-os

Oracle,PL/SQL,SQL,triggers,procedures,Reverse Engineering ,Code Analysis

Other desired skills:
Oracle,PL/SQL,SQL,triggers,procedures, Reverse Engineering ,Code Analysis

3. possible to call PL/SQL from Java stored procedure?

4. Java/J2EE PA(Oracle,SQL,PL/SQL)

5. Java object as parameter to Java stored procedure

6. Calling an Oracle Stored Procedure from a Sql Server 2000 Stored P

7. SSIS Oracle error on an execute of PL/SQL procedure

8. Starting PL/SQL Procedure via OLEDB in C++, error with oraoledb.oracle provider

9. How to pass a boolean parameter to an oracle PL/SQL procedure

10. Procedure in PL/SQL and Oracle Application Express

11. Is it possible to run an Oracle PL\Sql procedure within Excel?

12. PL/SQL versus stored procedures

13. Stop a PL/SQL stored procedure

14. Are PL-SQL and "Stored procedures" the same ?

15. Source of stored procedure (PL/SQL)