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

From: Abraham Leolo (abraham.leolo_at_nospam.org)
Date: 12/14/03 

Date: Sun, 14 Dec 2003 21:43:59 GMT

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

Relevant Pages

  • ASP Button to open link
    ... The more I google how to do this, I keep seeing to do it via javascript ... I have the url as a string in vb. ... Is there a command I can ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: utility to remove CR/LF characters
    ... What you need is a "command line hex editor". ... Check Google ... for this string. ...
    (microsoft.public.win2000.general)
  • Re: ASP Button to open link
    ... button to my page and when selected, to open a url in a seperate page. ... The more I google how to do this, I keep seeing to do it via javascript ... I have the url as a string in vb. ... Is there a command I can ...
    (microsoft.public.dotnet.framework.aspnet)
  • TIP #185: Null Handling
    ... nulls, and command modifications for manipulating them. ... Tcl deals with strings, the universal medium for representing data. ... is know and it is an empty string, but if a respondent forgets to give ...
    (comp.lang.tcl)
  • Re: Determine how Word was launched
    ... inspect the command line used to launch Word. ... Private Declare Function GetCommandLineA Lib "kernel32" As Long ... Public Function GetCommandLineAs String ... Dim lngCmdLinePtr As Long ...
    (microsoft.public.office.developer.vba)