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]
RE: esql - oracle stored procedure returns resultset
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
- 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: esql:parameter direction=out type=oracle.jdbc.driver.OracleTypes.CURSOR/ 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
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:
esql:call resultset-from-object=1 needs-query=true
{ call ? := sp_get_stocks(esql:parameter direction=in
type=Intxsp:expr20/xsp:expr/esql:parameter) }
Try without the @resultset-from-object attribute, it serves a
different purpose. Try
esql:call{ sp_get_stocks(esql:parameter direction=in
type=Intxsp:expr20/xsp:expr/esql:parameter) }/esql:call
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. 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
ok second try with:
esql:call
{call sp_get_stocks(esql:parameter
direction=intype=Intxsp:expr20/xsp:expr/esql:parameter) }
/esql:call
esql:call-results
esql:row-results
outesql:get-string column=1//out
/esql:row-results
/esql:call-results
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. 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
On 30.Jul.2002 -- 02:37 PM, Frank Taffelt wrote:
ok second try with:
esql:call
{call sp_get_stocks(esql:parameter
direction=intype=Intxsp:expr20/xsp:expr/esql:parameter) }
/esql:call
esql:call-results
esql:row-results
outesql:get-string column=1//out
/esql:row-results
/esql:call-results
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. 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
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. 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
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. 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
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. http://xml.apache.org/cocoon/faq/index.html To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
