ah, looking at the Oracle error message, the function has out variables as well as returning the value. I would recommend only using a function when you want to return 1 value, not if you want to return a value AND have out parameters
-----Original Message----- From: Christopher Painter-Wakefield [mailto:[EMAIL PROTECTED] Sent: 10 October 2003 14:46 To: [EMAIL PROTECTED] Subject: RE: Esql and Oracle Functions That makes sense. What is the java code that you would use to directly access this function? How does it differ from the code generated by ESQL? Once you know that, it should be pretty easy to extend ESQL to generate the proper code. An alternative, of course, would be to wrap the function in a procedure.... -Christopher |---------+----------------------------> | | "Wilson, Colin" | | | <[EMAIL PROTECTED]| | | office.com> | | | | | | 10/10/2003 03:00 | | | AM | | | Please respond to| | | users | | | | |---------+----------------------------> >--------------------------------------------------------------------------------------------------------------| | | | To: [EMAIL PROTECTED] | | cc: | | Subject: RE: Esql and Oracle Functions | >--------------------------------------------------------------------------------------------------------------| I get: ORA-06572 function name has out arguments Cause: A SQL statement references either a packaged or a stand-alone PL/SQL function that contains an OUT parameter in its argument list. PL/SQL functions referenced by SQL statements must not contain the OUT parameter. If anyone has any other ideas ...? -----Original Message----- From: Martin Samm [mailto:[EMAIL PROTECTED] Sent: 09 October 2003 15:52 To: [EMAIL PROTECTED] Subject: RE: Esql and Oracle Functions i haven't used ESQL, but getting the result of a function should be the same as performing a select. E.g. select 1 from dual For a function it would be select package.function(param1,param2,...) from dual; Then retrieve the first column (getInt(1), say in JDBC) as you would with the column values from a normal select statement. -----Original Message----- From: Wilson, Colin [mailto:[EMAIL PROTECTED] Sent: 09 October 2003 15:40 To: [EMAIL PROTECTED] Subject: Esql and Oracle Functions Is there anyway that I can get results from an Oracle Function (as opposed to an Oracle Procedure). I have tried : <esql:connection> <esql:pool>oracle</esql:pool> <esql:execute-query> <esql:call>begin mypackage.myfunction( 'text', 'text2', <esql:parameter direction="out" type="String" />, <esql:parameter direction="out" type="String" />, <esql:parameter direction="out" type="String" />, <esql:parameter direction="out" type="Int" />, <esql:parameter direction="out" type="Int" />, <esql:parameter direction="out" type="String" /> ); end; </esql:call> <esql:call-results> <esql:get-int column="1" /> </esql:call-results> </esql:execute-query> </esql:connection> But I get : executing statement: begin mypackage.myfunction( 'text', 'text2', ?, ?, ?, ?, ?, ? ); end; : java.sql.SQLException: ORA-06550: line 1, column 18: PLS-00221: 'MYFUNCTION' is not a procedure or is undefined ORA-06550: line 1, column 18: PL/SQL: Statement ignored Which is quite true of course. The Oracle function is defined as: FUNCTION myfunction ( p_a VARCHAR2, p_b VARCHAR2, p_c OUT NUMBER, p_d OUT VARCHAR2, p_e OUT VARCHAR2, p_f OUT NUMBER, p_g OUT NUMBER, p_h OUT VARCHAR2 ) RETURN NUMBER And has been used many times before from java etc. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
