cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Martin Samm" <mart...@image.net>
Subject RE: Esql and Oracle Functions
Date Thu, 09 Oct 2003 14:51:48 GMT
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:colin.wilson@metoffice.com]
Sent: 09 October 2003 15:40
To: users@cocoon.apache.org
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: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org


Mime
View raw message