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]