Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Problems with return arraytypes Oracle9i........

5 views
Skip to first unread message

juan lizana

unread,
Nov 28, 2003, 3:41:04 PM11/28/03
to

I have a problem to invoking procedure on oracle 9i/BEA-WEBLOGIC 8

CREATE OR REPLACE PACKAGE BODY CLIPKG_PARENTESCOS_NW AS
PROCEDURE SRV_RECTIPOPARENT2
(sp_des_parentesco IN OUT s_des_parent_arr) IS
TYPE cur_typ IS REF CURSOR;
c_tipoparent cur_typ;
r_demog r_tipoparentesco;
sv_query VARCHAR2(1000);
nv_num_reg NUMBER;
BEGIN
nv_num_reg := 0;
sv_query :=' ';
sv_query := 'SELECT TIPA_DESCRIPCION FROM CLIT_TIPOSPARENTESCO';
OPEN c_tipoparent FOR sv_query;
LOOP
FETCH c_tipoparent INTO r_demog;
EXIT WHEN c_tipoparent%NOTFOUND;
nv_num_reg := nv_num_reg + 1;
sp_des_parentesco(nv_num_reg) := r_demog.s_des_parentesco;
END LOOP;
CLOSE c_tipoparent;
END;


END CLIPKG_PARENTESCOS_NW;
/


CREATE OR REPLACE PACKAGE CLIPKG_PARENTESCOS_NW IS
TYPE s_des_prev_arr IS TABLE OF PARA_PREVISION.PREV_DESCRIPCION%TYPE INDEX
BY BINARY_INTEGER;
END CLIPKG_PARENTESCOS_NW;
/

The class java contains the following source.......


package alemana.test;

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


class Array
{

public static void main(String args[]) throws Exception
{

DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());

Connection conn =
DriverManager.getConnection
("jdbc:oracle:thin:@200.6.70.37:1521:dw2ks6",
"clientes",
"clientes");

OracleCallableStatement stmt =
(OracleCallableStatement)conn.prepareCall
( "{call CLIPKG_PARENTESCOS_NW.SRV_RECTIPOPARENT2( ?)}" );

stmt.registerOutParameter( 1,OracleTypes.ARRAY,"s_des_prev_arr"); //xxx
stmt.executeUpdate();

ARRAY simpleArray = stmt.getARRAY(1);

/* comments
System.out.println ("Array is of type " +
simpleArray.getSQLTypeName());

System.out.println ("Array element is of type code " +
simpleArray.getBaseType());

System.out.println ("Array is of length " +
simpleArray.length());


String[] values = (String[])simpleArray.getArray();

for( int i = 0; i < values.length; i++ )
System.out.println( "row " + i + " = '" +
values[i] + "'" );
comments*/

stmt.close();
conn.close();
}
}

and the return exception is:

java.sql.SQLException: patrón de nombre no válido: CLIENTES.s_des_prev_arr
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:527)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:407)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1952)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:199)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:118)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:169)
at alemana.test.Array.main(Array.java:27)
Exception in thread "main"

In the line mark xxx occurss the exception.
I review codes in internet and I do not have solution
i can't modify procedure because it belongs to another person

What alternative I can occupy to solve my problems ????????

Joe Weinstein

unread,
Nov 28, 2003, 5:08:31 PM11/28/03
to juan lizana

juan lizana wrote:

Hi Juan. I don't see how this relates to weblogic yet, but
I suggest changing the line:

stmt.registerOutParameter( 1,OracleTypes.ARRAY,"s_des_prev_arr"); //xxx

to

stmt.registerOutParameter( 1,OracleTypes.ARRAY); //yyy

Joe

0 new messages