Path: news.easynews.com!easynews!news-out.cwix.com!newsfeed.cwix.com!newscon02.news.prodigy.com!prodigy.com!pln-e!spln!dex!extra.newsguy.com!newsp.newsguy.com!E
From: E@E.COM (steve)
Newsgroups: comp.databases.oracle.misc
Subject: Re: Tracing ref_cursors
Date: Thu, 13 Dec 2001 06:26:56 +0800
Organization: http://extra.newsguy.com
Lines: 58
Message-ID: <1f4ccbl.hiwoqiuqovowN%E@E.COM>
References: <18b0424d.0112051859.2a05f067@posting.google.com> <9unmf302tdo@drn.newsguy.com> <18b0424d.0112061515.2810f31d@posting.google.com> <9url1e0ggm@drn.newsguy.com>
NNTP-Posting-Host: p-502.newsdawg.com
User-Agent: MacSOUP/2.4.6
Xref: easynews comp.databases.oracle.misc:73695
X-Received-Date: Wed, 12 Dec 2001 15:51:15 MST (news.easynews.com)

why can sql not pass a ref cursor back to java?
below i have java code that calls an pl/SQL procedure

procedure (xyz out ref_table, dummy1 in varchar2, dummy2 in varchar2);
open xyz select * from table.
return

                
         String The_qry="{call external_user.PACKAGE_01.GET_NOTE(?,"+
"'"+ caf_pointer.trim()+
"','"+"NOTHING"+                //OUR SECURITY KEY GOES HERE
"')}";                  

  cstmt=(OracleCallableStatement)my_vars.dbconn.prepareCall(The_qry);
   cstmt.registerOutParameter(1,OracleTypes.CURSOR);
   cstmt.execute();     
    // Get the return value and covert it into a JDBC STRING
      
/*      
 
 rset = (ResultSet)cstmt.getObject(1);
        display( rset );







Thomas Kyte <tkyte@us.oracle.com> wrote:

> In article <18b0424d.0112061515.2810f31d@posting.google.com>,
> manish.shah@emagine-int.com says...
> >
> >>You need to invoke the plsql from java, not have the plsql invoke the javain
> >> this case.
> >
> >I'm not sure I understood your response. 
> >
> >My requirement is:
> >
> >I'd like to write a stored proc that takes any ref_cursor and prints
> >out its contents to dbms_output.
> >
> >Since PL/SQL cannot do this, I thought maybe I could pass the
> >ref_cursors to a Java class that can. You say that this is not
> >possible, so it would seem that my requirement cannot be fulfilled.
> >
> 
> PLSQL cannot pass a ref cursor to java.
> 
> Java can call PLSQL, get a ref cursor cursor BACK and print it out.
> 
> So, you need to invoke the plsql from java.
> 
> You can write a very simple test harness that calls the PLSQL from a java
> client, and dumps the cursor out.  that is what I was trying to say.
>