cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Wilson, Colin" <colin.wil...@metoffice.com>
Subject RE: Esql and Oracle Functions
Date Fri, 10 Oct 2003 13:52:45 GMT
I have done just that, by rewriting the function as a procedure, but I
am still getting problems elsewhere which I can't be sure are connected.
If I do get a usable solution, I will post it on this thread.




-----Original Message-----
From: Christopher Painter-Wakefield [mailto:paint007@mc.duke.edu] 
Sent: 10 October 2003 14:46
To: users@cocoon.apache.org
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


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:martins@image.net]
Sent: 09 October 2003 15:52
To: users@cocoon.apache.org
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: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