Re: esql - oracle stored procedure returns resultset

[Frank Taffelt]

- Original Message -
From: "Luca Morandini" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, July 31, 2002 11:36 AM
Subject: RE: esql - oracle stored procedure returns resultset


> just for the record: I use SQLTransformer, which runs smoothly even with
multiple cursors returned by SPs.
> I used this method for a couple of projects and for some dozens SPs... not
a glitch.

thanks, tried it out . works quit well.

Christian, i'm pretty sure that the current esql is not able to run oracle
sp in that way.
It would be possible if i could write something like that:



The esql:parameter is restricted to java.sql.Type

Frank




-
Please check that your question  has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>

To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail:   <[EMAIL PROTECTED]>

RE: esql - oracle stored procedure returns resultset

[Luca Morandini]

Frank,

just for the record: I use SQLTransformer, which runs smoothly even with multiple 
cursors returned by SPs. 
I used this method for a couple of projects and for some dozens SPs... not a glitch.

Best regards,

- 
   Luca Morandini 
   GIS Consultant 
  [EMAIL PROTECTED] 
http://utenti.tripod.it/lmorandini/index.html 
-
 

> -Original Message-
> From: Frank Taffelt [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, July 31, 2002 11:12 AM
> To: [EMAIL PROTECTED]
> Subject: Re: esql - oracle stored procedure returns resultset
> 
> 
> - Original Message -
> From: "Christian Haul" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, July 30, 2002 7:42 PM
> Subject: Re: esql - oracle stored procedure returns resultset
> 
> 
> > I'm certain that others do use oracle sps the way described. Actually,
> > the esql sp support started that way...
> 
> it would be nice, if other oracle sp users can confirm this and even better
> send a small
> example how they do it.
> 
> thanks in advance
> Frank Taffelt
> 
> 
> -
> Please check that your question  has not already been answered in the
> FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
> 
> To unsubscribe, e-mail: <[EMAIL PROTECTED]>
> For additional commands, e-mail:   <[EMAIL PROTECTED]>
> 

-
Please check that your question  has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>

To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail:   <[EMAIL PROTECTED]>

Re: esql - oracle stored procedure returns resultset

[Frank Taffelt]

- Original Message -
From: "Christian Haul" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, July 30, 2002 7:42 PM
Subject: Re: esql - oracle stored procedure returns resultset


> I'm certain that others do use oracle sps the way described. Actually,
> the esql sp support started that way...

it would be nice, if other oracle sp users can confirm this and even better
send a small
example how they do it.

thanks in advance
Frank Taffelt


-
Please check that your question  has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>

To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail:   <[EMAIL PROTECTED]>

Re: esql - oracle stored procedure returns resultset

[Christian Haul]

On 30.Jul.2002 -- 04:34 PM, Frank Taffelt wrote:
> > So you need to return a cursor as first parameter and use
> > @resultset-from-object="1"
> 
> yes but the to code to achive this is oracle dependent:
> IMHO code to achive this should look like:
> 
> 1 CallableStatement cstmt = conn.prepareCall(query);
>  2 cstmt.registerOutParameter(1,OracleTypes.CURSOR);
> 3 cstmt.execute();
> 4 ResultSet rset = (ResultSet)cstmt.getObject(1);
> 
> the problem is line 2 which uses OracleType.CURSOR and no standard jdbc type
> . i think this
> code is not suitable for esql-logicsheet or ist it ?
> 
> without registering the cursor the oracle driver assumes a different
> signature for the procedure and therefore cannot find it (this explains the
> error from my last try).

I'm certain that others do use oracle sps the way described. Actually,
the esql sp support started that way...

Chris.
-- 
C h r i s t i a n   H a u l
[EMAIL PROTECTED]
fingerprint: 99B0 1D9D 7919 644A 4837  7D73 FEF9 6856 335A 9E08

-
Please check that your question  has not already been answered in the
FAQ before posting. 

To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail:   <[EMAIL PROTECTED]>

Re: esql - oracle stored procedure returns resultset

[Frank Taffelt]

> So you need to return a cursor as first parameter and use
> @resultset-from-object="1"

yes but the to code to achive this is oracle dependent:
IMHO code to achive this should look like:

1 CallableStatement cstmt = conn.prepareCall(query);
 2 cstmt.registerOutParameter(1,OracleTypes.CURSOR);
3 cstmt.execute();
4 ResultSet rset = (ResultSet)cstmt.getObject(1);

the problem is line 2 which uses OracleType.CURSOR and no standard jdbc type
. i think this
code is not suitable for esql-logicsheet or ist it ?

without registering the cursor the oracle driver assumes a different
signature for the procedure and therefore cannot find it (this explains the
error from my last try).

so i think it's not yet the time for a summary  ...

P.S. i'm not that jdbc-oracle-expert maybe somebody has a solution for this


-
Please check that your question  has not already been answered in the
FAQ before posting. 

To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail:   <[EMAIL PROTECTED]>

Re: esql - oracle stored procedure returns resultset

[Christian Haul]

On 30.Jul.2002 -- 03:15 PM, Frank Taffelt wrote:
> the final chapter:
> "...
> Oracle itself can only return result sets from a 'naked' query, or via a
> cursor as a stored procdure output parameter. The latter is not available
> from
> standard JDBC, only the extension to JDBC,
> CallableStatement.getResultSet(int paramindex)
> available from Oracle drivers. ..."

Ah, this is where @resultset-from-object="1" comes back into play:
Have that parameter registered as object and automagically the first
(just noticed a bug: currently only the first) parameter is turned
into the current ResultSet! :-)

So you need to return a cursor as first parameter and use
@resultset-from-object="1"

Chris.

Please follow up summarizing your problem and which suggested solution
/ information worked for you when you consider your problem
solved. Add "SUMMARY: " to the subject line. This will make FAQ
generation and searching the list easier. In addition, it makes
helping you more fun. Thank you.

-- 
C h r i s t i a n   H a u l
[EMAIL PROTECTED]
fingerprint: 99B0 1D9D 7919 644A 4837  7D73 FEF9 6856 335A 9E08

-
Please check that your question  has not already been answered in the
FAQ before posting. 

To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail:   <[EMAIL PROTECTED]>

Re: esql - oracle stored procedure returns resultset

[Frank Taffelt]

the final chapter:
"...
Oracle itself can only return result sets from a 'naked' query, or via a
cursor as a stored procdure output parameter. The latter is not available
from
standard JDBC, only the extension to JDBC,
CallableStatement.getResultSet(int paramindex)
available from Oracle drivers. ..."

found in a newsgroup  :-(

it seems that there is only a DBMS-specific solution to get things working.

for those who are interested - the oracle way in a short summary:
http://www.enterprisedt.com/publications/oracle/result_set.html





-
Please check that your question  has not already been answered in the
FAQ before posting. 

To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail:   <[EMAIL PROTECTED]>

Re: esql - oracle stored procedure returns resultset

[Christian Haul]

On 30.Jul.2002 -- 02:37 PM, Frank Taffelt wrote:
> ok second try with:
> 
> 
> {call sp_get_stocks( direction="in"type="Int">20) }
> 
> 
>  
>  
>   
> 
> 
> results in :
> 
> Error executing statement: {call sp_get_stocks(?) }: java.sql.SQLException:
> ORA-06550: line 1, column 7:
> PLS-00221: 'SP_GET_STOCKS' is not a procedure or is undefined
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> 
> this error message is confusing, because i have written a small java-jdbc
> applicaion which calls this procedure
> and receives a resultset from it.

Does your code equal (more or less) the one produced by esql?

> any hints ?

Visibility problems, like connection is made using a different user?

Chris.
-- 
C h r i s t i a n   H a u l
[EMAIL PROTECTED]
fingerprint: 99B0 1D9D 7919 644A 4837  7D73 FEF9 6856 335A 9E08

-
Please check that your question  has not already been answered in the
FAQ before posting. 

To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail:   <[EMAIL PROTECTED]>

Re: esql - oracle stored procedure returns resultset

[Frank Taffelt]

ok second try with:


{call sp_get_stocks(20) }


 
 
  


results in :

Error executing statement: {call sp_get_stocks(?) }: java.sql.SQLException:
ORA-06550: line 1, column 7:
PLS-00221: 'SP_GET_STOCKS' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

this error message is confusing, because i have written a small java-jdbc
applicaion which calls this procedure
and receives a resultset from it.

any hints ?


-
Please check that your question  has not already been answered in the
FAQ before posting. 

To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail:   <[EMAIL PROTECTED]>

Re: esql - oracle stored procedure returns resultset

[Christian Haul]

On 30.Jul.2002 -- 10:44 AM, Frank Taffelt wrote:
> Hi,
> 
> i'm trying to get an oracle stored procedure working, that returns a
> resultset.
> but i always get a :
> 
> java.lang.RuntimeException: Error executing statement: { call ? :=
> sp_get_stocks(?) } : java.sql.SQLException: ORA-01008: not all variables
> bound
> 
> here is my code:
> 
>  
> { call ? := sp_get_stocks( type="Int">20) }

Try without the @resultset-from-object attribute, it serves a
different purpose. Try 
  { sp_get_stocks(20) }

I believe, oracle doesn't need @needs-query.

Chris.
-- 
C h r i s t i a n   H a u l
[EMAIL PROTECTED]
fingerprint: 99B0 1D9D 7919 644A 4837  7D73 FEF9 6856 335A 9E08

-
Please check that your question  has not already been answered in the
FAQ before posting. 

To unsubscribe, e-mail: <[EMAIL PROTECTED]>
For additional commands, e-mail:   <[EMAIL PROTECTED]>