CODEWITHSUNDEEP
javasqloracle-databaseplsql
Lai
Lai

Reputation: 482

How should I pass key-values to a PL/SQL procedure?

First, I have to apologize if this is a silly question but I am new to PL/SQL.

I am working on a project that requires some interaction with an Oracle database using java and PL/SQL. I have an undeterminable list of key-values which I would like to pass to the database, my initial research seems to point me to oracle.sql.STRUCT and oracle.sql.ARRAY.

I am not totally in favour of STRUCT and ARRAY objects because they require existing objects/tables types in the database side.

Edit:

in my pl/sql 

CREATE OR REPLACE TYPE myItem AS object (c1 VARCHAR2(10), c2 VARCHAR2(30)); 
CREATE OR REPLACE TYPE text_table AS TABLE OF myItem ;

in my java code

java.sql.Connection connection = myGetConnection();
// create variables for the oracle.sql.ARRAY
ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("TEXT_TABLE", connection);
Object[] arrayObject = new Object[2];

//create STRUCT objects
StructDescriptor descriptor = StructDescriptor.createDescriptor("MYITEM", connection);

// Loop through the map and put objects in the arrayObject
for(int i = 0; i < myMap.size(); i++){
String key = "key"+1;
String value = "value"+2;
Object[] obj = {key,value};
STRUCT structObj = new STRUCT(descriptor, connection, obj);

// put the struct object in the array object
arrayObject[i] = structObj;
}

// create the oracle.sql.ARRAY
ARRAY array = new ARRAY(arraydesc, connection, arrayObject);

//..
oracleCallableStatement = connection.prepareCall("{call myPackage.myProcedure(?)}");
oracleCallableStatement.setArray(1, array);
oracleCallableStatement.execute();
oracleCallableStatement.close();

The fact that I have to excplictly declare a type in my oracle database makes me think there must be an easier way of doing things. These are very primitive objects.

Is this the right way to go about it and is this really my only option?

Upvotes: 1

Views: 2941

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

It's not a silly question, and you're doing it the way I would (so I think it's right, at least!). You have to be able to associate the ARRAY type with a schema-level collection type, so that has to be created with the SQL CREATE TYPE; it can't be a PL/SQL collection defined in a package, say, which might be a little neater if it were possible. There is no magic way for Oracle to map your Java array to an arbitrary collection - you have to define and associate the type so the data can be sent through JDBC in a recognizable stream.

The only thing I might consider doing differently is to create the collection as a table type rather than a varray:

CREATE TYPE text_varray AS table OF myItem ;

... so you don't have to constrain the size; but the order of the elements might be important and is only retained by varray.

Another option would be to use two varrays, one for keys and one for values, and pass two arguments to your procedure; you then have to convert your original Java two-dimensional array to two separate arrays. The advantage of that is that you could then use one of the built-in types - sys.odcivarchar2list - and not have to create your own varray or object types. But as it's more complicated on the Java side, and maintaining your own types isn't that much of a hardship, it's probably not worth it; and isn't an option if you change from varray to table as the elements wouldn't necessarily be in the same order in both lists.

If the string lengths in your object definition are correct, you could potentially (ab)use sys.odciobject and sys.odciobjectlist, since that would give you a similar set-up; but it's still safer to use your own types in case the system ones change in the future, or more likely your requirements change to need longer strings.

Upvotes: 2

Related Questions