Re: [JDBC] How to Register Stored Procedure Out Parameter for Array Type

thanks.

I am using the thin driver for portability. using your suggestion works. the stored procedure need some adjustment to adapt to the new type definition.

thanks for your help.

"bung ho" <bung_ho_at_hotmail.com> wrote in message news:567a1b1.0209191004.1e5fad59_at_posting.google.com...
> thick or thin jdbc driver? as far as i know, this is only possible
> with the thick driver. with the thin driver, i believe you need to
> use "real" SQL types, not ones defined in PL/SQL packages. that is,
> at the sqlplus prompt, create your record (object) type, and the
> nested table of your record type like so:
>
> SQL> create type MyRec as object (
> 2 ckt number(8), -- whatever
> 3 telephone varchar2(15) -- whatever
> 4 );
> 5 /
>
> Type created.
>
> SQL> create type cktlist is table of myrec;
> 2 /
>
> Type created.
>
> then you should be able to bind to these from your java code.
>
>
> "Chin Hsu" <chinhsu_at_at_remove_this_t.com> wrote in message
 news:<amau2a$rqi6_at_kcweb01.netnews.att.com>...
> > My configuration is Oracle 9i database server and Oracle JDBC driver
 that
> > comes with it.
> >
> > I am trying to register out parameter for a stored procedure defined as
> > follow:
> >
> > PACKAGE for_my_test AS
> > TYPE MyRec IS RECORD (
> > ckt circuit.cktid%TYPE,
> > telephone circuit.telephone%TYPE
> > );
> > TYPE CktList IS TABLE OF MyRec;
> >
> > PROCEDURE getCkt(alist out CktList);
> > PROCEDURE mytest;
> >
> > END for_my_test;
> >
> > my java code is as follow:
> >
> > ...
> >
> > stmt.registerOutParameter(i+1, java.sql.Types.ARRAY,
 "for_my_test.CktList");
> >
> > ...
> >
> > But the jdbc driver is complaining about the invalid
> > name pattern (see output below). Does anyone know what the valid name
> > pattern should be?
> >
> >
> > the output:
> > [java] syntax = {CALL for_my_test.getCkt( ?) }
> >
> > [java] Exception: Database error java.sql.SQLException: invalid name
> > pattern: for_my_test.CktList
> >
> > [java] at StoredProcDAO.execute(StoredProcDAO.java:107)
> >
> > [java] at TestList.process(TestList.java:34)
> >
> > [java] at Test.main(Test.java:28)
Received on Thu Sep 19 2002 - 21:35:04 CEST