EAserver - Using oracle.sql.ArrayDescriptor in Java component

Author

Using oracle.sql.ArrayDescriptor in Java component

Doug Porter

2005-12-21, 11:24 am

I am trying to call a stored procedure in Oracle 9.2.0.4 that takes a parameter that is a user defined type in the database that is
a table of name/value pairs (really a two dimensional array of strings). I have a sample standalone Java class that can
successfully call it, but when doing the same thing in a Java component, it coughs up a hairball.

//Sample Code
//_conn is the connection object in EAServer
String[][] elems = { { "foo", "bar" }, { "foo2", "bar2" } };
ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("SS_ARGARRAY", _conn);
ARRAY argArray = new ARRAY(arraydesc, _conn, elems);
CallableStatement stmt = _conn.prepareCall("{ call my_pkg.doIt(?) }");
stmt.setArray(1, argArray);
stmt.execute( );

//From Jaguar Log:
//It dies on the ARRAY argArray = ... line
//or also it dies if I try and call the ArrayDescriptor.getBaseName( ) and
//ArrayDescriptor.descType( ) methods on arraydesc
//these two methods work fine in my standalone java client
Dec 21 10:00:48 2005: java.sql.SQLException: Internal Error
Dec 21 10:00:48 2005: at oracle.jdbc.dbaccess.DBError. throwSqlException(DB
Error.java:134)
Dec 21 10:00:48 2005: at oracle.jdbc.dbaccess.DBError. throwSqlException(DB
Error.java:179)
Dec 21 10:00:48 2005: at oracle.jdbc.dbaccess.DBError. throwSqlException(DB
Error.java:269)
Dec 21 10:00:48 2005: at oracle.jdbc.oracore. OracleTypeCOLLECTION
. initCollElemTypeName
(OracleTypeCOLLECTIO
N.java:1014)
Dec 21 10:00:48 2005: at oracle.jdbc.oracore. OracleTypeCOLLECTION
. getAttributeType(Ora
cleTypeCOLLECTION.java:1044)
Dec 21 10:00:48 2005: at oracle.jdbc.oracore.OracleNamedType. getFullName(OracleNa
medType.java:110)
Dec 21 10:00:48 2005: at oracle.jdbc.oracore.OracleNamedType. getFullName(OracleNa
medType.java:93)
Dec 21 10:00:48 2005: at oracle.sql.ArrayDescriptor. getBaseName(ArrayDes
criptor.java:366)
Dec 21 10:00:48 2005: at oracle.sql.ArrayDescriptor. descType(ArrayDescri
ptor.java:542)
Dec 21 10:00:48 2005: at oracle.sql.ArrayDescriptor. descType(ArrayDescri
ptor.java:501)
Dec 21 10:00:48 2005: at com.dailyaccess.report.ReportGeneratorImpl. getReport(ReportGene
ratorImpl.java:110)
Dec 21 10:00:48 2005: at com.dailyaccess.report.ReportGeneratorImpl. getXmlOutput(ReportG
eneratorImpl.java:233)
Dec 21 10:00:48 2005: at com.dailyaccess.report. _sk_Report_ReportGen
erator. invoke(_sk_Report_Re
portGenerator.java:558)

Any ideas or thoughts would be greatly appreciated.

Doug Porter
DailyAccess Corporation

Doug Porter

2005-12-21, 11:24 am

Found my own solution. Unfortunately the problem was between the chair and the keyboard :-)

For others benefit: The user defined type I was trying to use ("SS_ARGARRAY") was not visible from the db user in EAServer
connection cache. I could see this by doing desc ss_argarray in SQLPlus. It just gave an object does not exist error message. My
standalone java client was using a different db user and thus succeeeded. So I need to add a grant so that this object is visible.
Here's to less than descriptive error messages. Problem solved, everyone back to work.

Doug Porter
DailyAccess Corporation

"Doug Porter" < doug_porterATdailyac
cessDOTnospamDOTcom> wrote in message news:43a9833f$1@foru
ms-2-dub...
> I am trying to call a stored procedure in Oracle 9.2.0.4 that takes a parameter that is a user defined type in the database that
is
> a table of name/value pairs (really a two dimensional array of strings). I have a sample standalone Java class that can
> successfully call it, but when doing the same thing in a Java component, it coughs up a hairball.
>
> //Sample Code
> //_conn is the connection object in EAServer
> String[][] elems = { { "foo", "bar" }, { "foo2", "bar2" } };
> ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("SS_ARGARRAY", _conn);
> ARRAY argArray = new ARRAY(arraydesc, _conn, elems);
> CallableStatement stmt = _conn.prepareCall("{ call my_pkg.doIt(?) }");
> stmt.setArray(1, argArray);
> stmt.execute( );
>
>
> //From Jaguar Log:
> //It dies on the ARRAY argArray = ... line
> //or also it dies if I try and call the ArrayDescriptor.getBaseName( ) and
> //ArrayDescriptor.descType( ) methods on arraydesc
> //these two methods work fine in my standalone java client
> Dec 21 10:00:48 2005: java.sql.SQLException: Internal Error
> Dec 21 10:00:48 2005: at oracle.jdbc.dbaccess.DBError. throwSqlException(DB
Error.java:134)
> Dec 21 10:00:48 2005: at oracle.jdbc.dbaccess.DBError. throwSqlException(DB
Error.java:179)
> Dec 21 10:00:48 2005: at oracle.jdbc.dbaccess.DBError. throwSqlException(DB
Error.java:269)
> Dec 21 10:00:48 2005: at oracle.jdbc.oracore. OracleTypeCOLLECTION
. initCollElemTypeName
(OracleTypeCOLLECTIO
N.java:1014)
> Dec 21 10:00:48 2005: at oracle.jdbc.oracore. OracleTypeCOLLECTION
. getAttributeType(Ora
cleTypeCOLLECTION.java:1044)
> Dec 21 10:00:48 2005: at oracle.jdbc.oracore.OracleNamedType. getFullName(OracleNa
medType.java:110)
> Dec 21 10:00:48 2005: at oracle.jdbc.oracore.OracleNamedType. getFullName(OracleNa
medType.java:93)
> Dec 21 10:00:48 2005: at oracle.sql.ArrayDescriptor. getBaseName(ArrayDes
criptor.java:366)
> Dec 21 10:00:48 2005: at oracle.sql.ArrayDescriptor. descType(ArrayDescri
ptor.java:542)
> Dec 21 10:00:48 2005: at oracle.sql.ArrayDescriptor. descType(ArrayDescri
ptor.java:501)
> Dec 21 10:00:48 2005: at com.dailyaccess.report.ReportGeneratorImpl. getReport(ReportGene
ratorImpl.java:110)
> Dec 21 10:00:48 2005: at com.dailyaccess.report.ReportGeneratorImpl. getXmlOutput(ReportG
eneratorImpl.java:233)
> Dec 21 10:00:48 2005: at com.dailyaccess.report. _sk_Report_ReportGen
erator. invoke(_sk_Report_Re
portGenerator.java:558)
>
> Any ideas or thoughts would be greatly appreciated.
>
> Doug Porter
> DailyAccess Corporation
>
>

Sponsored Links