Skip to Main Content
  • Questions

  • referencing types in pl/sql package from java

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, keith.

Asked: July 09, 2001 - 5:46 pm UTC

Last updated: October 19, 2006 - 2:06 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

hi, i've read all (i think) your examples concerning pl/sql varrays, nested tables etc., to and from java (with the JVM (JSDK 1.3.1) outside of oracle via thin jdbc (classes12.zip)), but one thing that i can't seem to find out from the examples or the docs is how to, when calling in the java code ArrayDescriptor.createDescriptor(blah, conn) reference a user-defined type if it's defined in a package.

e.g., i have

create or replace package blahpkg as
type blahtype is table of number;
end;

and then let's say i create a procedure (inside or pkg or not, doesn't matter)

procedure blahproc (p in blahpkg.blahtype) as
i int;
begin
-- create table test_1 (x number);
for i in 1..p.count loop
insert into test_1 (x) values (p(i));
end loop;
end;

the procedure works fine and it behaves as expected if i execute it from within another pl/sql block.

however, when i try to execute the sp from java using OracleCallableStatement and then passing in a oracle.sql.ARRAY, i get an error when i do the aforementioned createDescriptor.

e.g., i try

ARRAY blahArray = new ARRAY(ArrayDescriptor.createDescriptor("BLAHPKG.BLAHTYPE", conn), conn, elements);

but i get

Exception in thread "main" java.sql.SQLException: invalid name pattern: BLAHPKG.BLAHTYPE
<stack trace>

everything works fine and dandy if i create the type outside of a package. any ideas? tia,

k.


and Tom said...

See
</code> http://docs.oracle.com/cd/A87860_01/doc/java.817/a83724/indexby.htm#1000888 <code>

it shows how in 817 and up using the OCI (thick -- not thin) drivers you can do this.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

The link is broken

A reader, November 18, 2003 - 2:47 pm UTC


Tom Kyte
November 21, 2003 - 10:57 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/java.920/a96654/oci_func.htm#1017512 <code>



referencing types in pl/sql package from java

mahi, October 25, 2004 - 3:55 pm UTC

I am facing the similar problem now. Its saying "Sql Exception is:invalid name pattern: "medart_dev.mypkg.DestQtrArray_type".
I have created a type inside a pkg specification mypkg.
Here is my java code snippet
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("mypkg.DestQtrArray_type", conn );
I am using oracle 9i and thin driver for connecting to the database.


Tom Kyte
October 25, 2004 - 4:00 pm UTC

type must be a sql type - not a plsql type.

you can only use plsql index by tables (link above shows how to do that) or sql types.

Oracle Interconnect Database adapter error

Faheem, October 19, 2006 - 10:29 am UTC

Tom,

I having similar kind of problem, but I have type created outside of the pl/sql as "Table" here it is

CREATE OR REPLACE TYPE demosub_Address_OAI_V1 IS OBJECT (
City VARCHAR2(1000),
State VARCHAR2(1000),
Zip VARCHAR2(1000)
);

create TYPE demosub_Address_OAI_V1_Arr IS table of demosub_Address_OAI_V1;

I am using collection type demosub_Address_OAI_V1_Arr in a stored proc which is called by Oracle Inter Connect Subscription database adapter.


Here is the proc

CREATE OR REPLACE PACKAGE BODY Customer AS

PROCEDURE sub_CreateCustomer_OAI_V1(
ID IN NUMBER,
Address IN demosub_Address_OAI_V1_Arr
)
AS
dummy NUMBER;
-- fill declarations here
BEGIN
-- fill code here
dummy:= 0;
insert into results values (id, address);
insert into executed values(sysdate);
commit;
END sub_CreateCustomer_OAI_V1;
END Customer ;


So this proc I beleived is called by the Oracle Interconnect database adapter and I think is called through Java which is trying to pass a message as input argument as Array Data type. and I am getting following.

***********
Error: db_bridge_writer_1 could not write the message to the database because of an application error (java.sql.SQLException: invalid name pattern: OAI_USER.DEMOSUB_ADDRESS_OAI_V1_ARR) in the application stored procedure. Please check your stored procedure. (Customer.CreateCustomer:OAI/V1,OAI/V1,false,0

ID: 10

Address[0]

City: SFO

State: CA

Zip: 94040

Address[1]

City: Reno

State: NV

Zip: 93949

Address[2]

City: SJC

State: CA

Zip: 95117

) was not written to the database.

java.sql.SQLException: invalid name pattern: OAI_USER.DEMOSUB_ADDRESS_OAI_V1_ARR

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:161)

at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:411)

at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:326)

at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1299)

at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:155)

at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:112)

at oracle.oai.agent.adapter.database.DBMessageWriter.createOracleArray
********************************

Tom Kyte
October 19, 2006 - 2:06 pm UTC

sorry, I've never used that tool myself.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library