Hi Ahmad,
In short the answer is - map your parameter as "java.sql.Array" (see
JavaDocs for java.sql.Array for more information on what you can do
with it once you get it).
As I never used SQL Array before, I figured I'd stage a test. Trying
your procedure code on my Oracle 9i instance failed (and I have no time
to debug that, besides my PL/SQL is a bit rusty), so instead I created
a simple table:
CREATE OR REPLACE TYPE andrus.x as varray(2) of varchar2(30)
CREATE table andrus.y ( name varchar(20), list andrus.x )
and then tried a SELECT with Cayenne, mapping "list" column as
java.sql.Array. It works and brings back an array of values as
expected. I don't see a reason why stored procedure OUT parameter
shouldn't behave the same way.
Andrus
On Oct 4, 2004, at 4:09 AM, Ahmad Ruswandi wrote:
> Dear all,
>
> I'm trying to use Cayenne to call stored procedure in Oracle.
>
> I have the following stored procedure, where I have out parameter
> "out1" with type varchar2 (equivalent to String() in java).
> -----------------------------------------------------------
> procedure PROC1(test_type varchar2, out1 out varchar2) is
> begin
> out1 := 'aaa';
> end;
> -----------------------------------------------------------
>
> I call procedure PROC1 sucessfully using code below:
> -----------------------------------------------------------
> ProcedureQuery query = new ProcedureQuery("PROC1");
> query.addParameter("test_type", "ANY TEST");
> query.addParameter("out1", new String());
>
> QueryResult resultsCollection = new QueryResult();
> context.performQuery(query, resultsCollection);
> ...
> ...
> -----------------------------------------------------------
>
>
>
> Now, I would like to have a procedure like this:
> -----------------------------------------------------------
> TYPE EmpTabTyp is table of varchar2(30);
> /
>
> CREATE OR REPLACE
> procedure PROC2(test_type varchar2, out1 out varchar2,
> out2 out EmpTabTyp) is
>
> emp_tab EmpTabTyp;
>
> begin -- main
>
> SELECT 'Ahmad' INTO emp_tab(1) FROM dual ;
> SELECT 'Ruswandi' INTO emp_tab(2) FROM dual ;
>
> out1 := 'aaa';
> out2 := emp_tab;
> end;
> /
> -----------------------------------------------------------
>
> How to access out parameter "out2" that has a list of value. What is
> the
> equivalent type in Java/cayenne?
>
> regards,
>
> ahmad
>
>
>
This archive was generated by hypermail 2.0.0 : Mon Oct 04 2004 - 23:36:21 EDT