Accessing oracle data type from java

Question

i want to access a data type defined in a package from java when executing function.Please help me.

Package and function:

create or replace package types as 
    TYPE name_array IS VARRAY(100000) OF VARCHAR2(200);
end types;



create or replace function test_pack1 return types.name_array as 
names types.name_array := types.name_array(); 

begin

for emp in (Select state from test where test_ID BETWEEN 1 AND 120000) loop
 names.extend;
 names(names.count) := emp.state;
  end loop;
return names;
end test_pack1;

java code

cstmt = (OracleCallableStatement) con.prepareCall("begin ? :=test_pack1; end;");
cstmt.registerOutParameter(1, OracleTypes.ARRAY,"NAME_ARRAY");
cstmt.execute();

In the above i am getting error at the second line. Err msg below: java.sql.SQLException: invalid name pattern: xyz.NAME_ARRAY

function is getting executed in Oracle successfully.But when we try execute from java its throwing above exception.

I would not consider myself an expert on this topic, but: What happens if you use ARRAY instead of NAME_ARRAY? — PhilW
– PhilW, Commented Jan 3, 2012 at 11:40
I'm not an expert either, but aren't you missing package name in your java code - like: cstmt.registerOutParameter(1, OracleTypes.ARRAY,"types.NAME_ARRAY"); ? — david a.
– david a., Commented Jan 3, 2012 at 12:42

Alec · Accepted Answer · 2013-09-05 15:25:05Z

2

Newer versions of Oracle (since 12c Release 1) claim to support types inside of packages (so they can be accessed via JDBC).

All PL/SQL package types are mapped to a system-wide unique name that can be used by JDBC to retrieve the server-side type metadata. The name is in the following form:

[SCHEMA.]<PACKAGE>.<TYPE>

from here: http://docs.oracle.com/cd/E16655_01/java.121/e17657/apxref.htm#CHEIIJCC

edited Sep 5, 2013 at 15:25

answered Sep 5, 2013 at 15:16

Alec

1,5262 gold badges14 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marcin Wroblewski · Accepted Answer · 2012-01-03 13:27:31Z

1

You have to use SQL type for this purpose, not PL/SQL type, so you need to define the type outside of PL/SQL (i.e. on schema level, not package level):

CREATE OR REPLACE TYPE  NAME_ARRAY AS VARRAY(100000) OF VARCHAR2(200);

and use it in your function

create or replace function test_pack1 return name_array as 
   names name_array := name_array(); 
...

answered Jan 3, 2012 at 13:27

Marcin Wroblewski

3,57020 silver badges29 bronze badges

2 Comments

Chandran Over a year ago

i don have privelage to create type @ schema level .please suggest any other ways..

Marcin Wroblewski Over a year ago

Try to use some existing type, for example in 10g there is DBMSOUTPUT_LINESARRAY which is VARRAY(2147483647) OF VARCHAR2(32767)

ivantod · Accepted Answer · 2012-01-03 13:25:19Z

0

For this to work it is not possible for the array type to be defined in a package, as much as it is convenient to do so. Oracle JDBC driver is not able to "see" package types in this manner (this applies not only to arrays, but to all custom defined types you may want to return via a JDBC call).

You need to define the array as a schema level type instead, after which this sort of code should be able to work fine.

answered Jan 3, 2012 at 13:25

ivantod

7733 silver badges9 bronze badges

Collectives™ on Stack Overflow

Accessing oracle data type from java

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related