Re: Ora7.3, JDBC and PL/SQL-Tables?

In article <3897E613.C8B3A7CA_at_zv.fhg.de>,   Werner Ebert <ebert_at_zv.fhg.de> wrote:
>
> Is there a way to retrieve PL/SQL-tables as Java-Arrays
> from stored procedures with Oracle 7.3 ?
>
> If so, what SQL-typemapping has to be used in registerOutParameter?
> Do I have to provide user-defined typemapping?
>
> So far, I tried the following (to no avail):
>
> (1)
> registerOutParameter with Types.ARRAY or Types.OTHER throws
> 'internal type unknown'
>
> (2)
> extending the connection's typemap with an own SqlData-Interface
> and calling registerOutParameter accordingly throws
> 'PLS-00201: Identifier 'DBMS_PICKLER.GET_FORMAT' must be declared'
>
> Any hints would be appreciated
>

Oracle JDBC driver supports only PL/SQL nested tables (collections) and VARRAYs through extention class oracle.sql.ARRAY. So using that in Oracle 7.3 is out of question. In Oracle 8 you can access PLSQL collections ( but not ordinary "INDEX BY BINARYINTEGER" tables ) and VARRAYs and pass them as an input and output parameters. But there is a limitation. Collections or VARRAYs have to be created as Oracle schema objects. For example, following PLSQL procedure can be called from JDBC:

CREATE TYPE VARCHAR_TABLE IS TABLE OF VARCHAR2( 255 ); CREATE PROCEDURE myProc( vt IN VARCHAR2 ) IS BEGIN
 ...
END; Java code might look like:

import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public void callProcedure( String[] tbl, Connection conn )  throws SQLException
{
  CallableStatement cs = null;
  ArrayDescriptor desc = ArrayDescriptor.createDescriptor ("VARCHAR_TABLE", conn);
  oracle.sql.ARRAY paramTable = new ARRAY(desc, conn, tbl);   cs = conn.prepareCall( "BEGIN myProc( ? ); END;" );  ( (OracleCallableStatement)cs ).setArray( 2, paramTable );   cs.execute();
  cs.close();
}

But it is not possible to call procedure like this:

PACKAGE myPackage IS
 TYPE VARCHAR_TABLE IS TABLE OF VARCHAR2( 255 );  PROCEDURE myProc( vt IN VARCHAR_TABLE ); END; Andrei Filimonov

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Feb 09 2000 - 12:18:28 CST