RE: Esql and Oracle Functions

[Martin Samm]

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]