CODEWITHSUNDEEP
javaoracle-databasestored-proceduresarrays
rzysia
rzysia

Reputation: 737

Passing byte array as parameter to stored procedure in oracle

I have function, which I need to put in Oracle Database (I'm using 11g) as stored procedure. Suppose that this function looks like this:

public static BLOB useByteArray(byte[] byteArray){
    //do something with this byte array, return BLOB with something
}

So how should wrapper function looks? I know it will be something around this

CREATE OR REPLACE FUNCTION USE_BYTE_ARRAY(byteArray ???) RETURN BLOB IS
 LANGUAGE JAVA NAME 'com.example.something.useByteArray(byte[]???)';

but I have no idea how can I pass this byte array between wrapper and java function. Perhaps, encode it in Base64, pass as string and then encode in useByteArray method?

Thanks in advance :)

Upvotes: 1

Views: 2659

Answers (2)

ChiranjeeviIT
ChiranjeeviIT

Reputation: 529

For this kind requirement, you can use SYS REF CURSOR from oracle. We can send ARRAY of values to stored procedure.

1) SYS REF CURSOR Declaration:

CREATE OR REPLACE PACKAGE TEST_CURSOR AS

TYPE testCursorType is REF CURSOR; END;

2) Create ORACLE type of table for accepting ARRAY

CREATE OR REPLACE TYPE tabletype AS TABLE OF NUMBER(10);

3) Create your procedure

 CREATE OR REPLACE PROCEDURE testProc
(adeptno tabletype,
testFetch IN OUT test_cursor.testCursorType) AS
BEGIN
OPEN testFetch FOR 
SELECT * 
FROM emp
WHERE deptno IN (SELECT *
FROM TABLE(CAST(adeptno AS tabletype)));
END;

4) And finally JAVA code

private static final String ARRAY_PROCEDURE = "call testProc(?,?)";
int arrayElements[] = {10,20,30,40};
//Create an Array Descriptor
ArrayDescriptor deptdesc = ArrayDescriptor.createDescriptor("TABLETYPE",connObj);
//Define the Array (Descriptor,connection, Elements)
ARRAY deptarray = new ARRAY(deptdesc, connObj, arrayElements);
this.executePrepareQuery(conn, ARRAY_PROCEDURE,deptarray);

Upvotes: 0

Swapnil
Swapnil

Reputation: 8328

I guess you can use OracleTypes.BLOB to pass the data from your java application and you can consume it with the blob data type in your stored procedure.

Upvotes: 1

Related Questions