Solved

how to pass an String[] from Java to PL/SQL and vice versa

Posted on 2003-10-27
26
2,985 Views
Last Modified: 2008-02-26
Hi
   Does any one know how to pass an String[] from Java to PL/SQL and vice versa.

Details:
1. Lets say you have a java program that return a string[]
    sample signature: public static String[] myfunction();

2. loaded the java file using loadjava...
3. Then how would you write a wrapper to handle the returned string[] from java.?

4. I was successfull in passing String, Integer back and forth from PL/SQL to Java..

Any help is highly appreciated.

Thanks.

 
0
Comment
Question by:peddhapati
  • 11
  • 11
  • 3
  • +1
26 Comments
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
This is an example for other problem, but it exchanges strings between Java and PL/SQL in both directions.

-----------
ExecuteCmd.java
----------
* ExecuteCmd.java
* This is a sample application that uses the Runtime Object
* to execute a program.
*
*/

/* Import the classes needed for Runtime, Process, and Exceptions */
import java.lang.String;
import java.lang.Runtime;
import java.lang.Process;
import java.io.IOException;
import java.lang.InterruptedException;

public class ExecuteCmd {

public static String execute(String cmd) {

String error;

try {
/* Execute the command using the Runtime object and get the
Process which controls this command */

error="0";

Process p = Runtime.getRuntime().exec(cmd);

/* Use the following code to wait for the process to finish
and check the return code from the process */

try {
p.waitFor(); /* Handle exceptions for waitFor() */
}
catch (InterruptedException intexc) {
error=intexc.getMessage();
}
/* Handle the exceptions for exec() */
}
catch (IOException e) {
error=e.getMessage();
}
return error;
};
};
/
------------------------------

Then i wrap it in PL/SQL with the following wrapper function:

--------------------------------------
CREATE OR REPLACE FUNCTION execute
( cmd VARCHAR2)
RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'ExecuteCmd.execute(java.lang.String) return java.lang.String';
/
-------------------------------------

After that, i give the user of the database the permission needed, and then i execute a PL/SQL block:

-------------------------------------

declare
x varchar2 (2000);
begin
x:=execute('/usr/bin/ls > a.txt');
dbms_output.put_line(x);
end;

------------------------------------

 
0
 

Author Comment

by:peddhapati
Comment Utility
Hi Schwertner
   Thanks for your comments, as I said earlier I was able to pass Strings/varchar2 and Integers/int between the PL/SQL and Java with out any problems.

What I want is, how to pass String[] (string Array) back and forth or atleast one-way.

Thanks
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
peddhapati:

I wonder why you need to pass the String[] to PL/SQL since Java is better at it.

but if you still want, you can follow this approach:

1. write the java class with a function to handle processing the String[].
2. load the java class into the database.
3. create a PL/SQL store procedure wrapper around java class
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
if you  need more details, let me know or google it at the website.
0
 

Author Comment

by:peddhapati
Comment Utility
Hi seazodiac,
     I agree with you that Java can handle arrays better than PL/SQL, but I have a need
where I have to return list of things to PL/SQL (preferbaly a String[]).

As I said in my first post, I have done all you have said. But I thing I do not undestand is how do I write a wrapper to handle string[] in pl/sql I get the following errror:

ORA-00932: inconsistent datatypes: expected Unsupported conversion
=========================================================
Detail of what I did:
1. Created the below Java file:

public class StringArrayTest {
public static String[] getArray(Integer arraySize) {
String[] myString = new String[arraySize.intValue()];

for (int i=0; i<arraySize.intValue(); i++) {
myString[i] = "Entry " + (i+1);
}
return(myString);
}
}

2. Laoded into the database using "loadjava" utility

3. Wrote a wrapper (see below) to call it from pl/sql

3A) CREATE OR REPLACE TYPE SimpleArrayType AS TABLE OF VARCHAR2(100)

3B)CREATE OR REPLACE FUNCTION stringArrayTest(vSize IN NUMBER) RETURN SimpleArrayType IS
LANGUAGE JAVA NAME 'StringArrayTest.getArray(java.lang.Integer) return java.lang.String[]';
/

AND I GET THE FOLLOWIG ERROR:
ORA-00932: inconsistent datatypes: expected Unsupported conversion.


IF THE CHANGE THE SIGNATURE TO TAKE STRING, INSTEAD OF STRING ARRAY, AND CHANGE THE WRAPPER TO HANDLE STRING IT WORKS FINE.

HOW CAN I HANDLE/PASS STRING ARRAYS BACK AND FORTH.....
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
You can workaround that by returning a comma separated string instead of a STring array.

then you parse and process that comma-delimited string in PL/SQL
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
this is Not what you are looking for, but it does solve your problem.

1. in java class, you return a comma-delimited string as opposed a string array .
2. in PL/SQL stored procedure, you converted the comma-delimited string back to PL/SQL varray or table.
3. processing it in PL/SQL.

the most similar thing in Oracle PL/SQL to String Array in Java will be PL/SQL Varray or PL/SQL table.

you can easily parse the comma-delimited string in plsql to load into the PL/SQL table.
0
 

Author Comment

by:peddhapati
Comment Utility
Hi seazodiac

That is exactly what I am doing right now, returning a huge string with "," as seperator instead of an string array.

But, we are hitting the upper limit of varchar2, which is 32767 (I believe)

How do I deal with this upper limit, using CLOB is an option again for this I need to rewrite my Java program to include "oracle.slq.clob" or "java.sql.Clob" packages... and it is not straight forward.

I believe the best way out of this should be an array, and I dont know how to write a pl/sql wrapper to handle arrays.

Thanks for all your input.
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
peddhapati:

Is there a reason that you have to use the hybrid model , ie, JAVA + PL/SQL?
can you accomplish your task either just in Java or just in PL/SQL?

I think you can do it purely in PL/SQL or JAVA , but using  both is creating the problem , not solving the problem? Maybe I am wrong, but you are more than welcome to share your logic here as to why you have to use both.

you can handle an array of VARCHAR or a table of VARCHAR just like string array in Java.

tell me what you need exactly, I will be more than glad to help?
0
 

Author Comment

by:peddhapati
Comment Utility
seazodiac,
    I appreciate your help on this.  To keep the long story short, I have SOAP APIs written in Java
which will do most of the user maintenance (adding users, roles, binding users to roles etc) on a server lets say "XYZ". I have my business logic which is sitting in the database, the database gets updated every night with new users, roles and updates to the existing users.

Based on the changes to the database I need to update the users, roles etc on the server "XYZ" via PL/SQl.

Also,the same SOAP-API are being used by various application (JSP from end) etc to do the same, so I want to keep the SOAP-API generic, and re-usable across other apps.

What I exactly need to know is simple "IS IT POSSIBLE TO PASS STRING ARRAYS BETWEEN JAVA AND PL/SQL" thats it. Based on my research it looks like its not possible.

I hope this clears your concerns....


0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
What I exactly need to know is simple "IS IT POSSIBLE TO PASS STRING ARRAYS BETWEEN JAVA AND PL/SQL" thats it. Based on my research it looks like its not possible.


---->Well, for this question, I can give you a definitive answer, String Array IS NOT SUPPORTed in PL/SQL. Simple, clear as that.


but Indeed, I have done using a comma-delimited string to pass an string array to Pl/SQL. that works fine for me bacause (now i know) it's shorter than 32Kb limit of PL/SQL variable.

I think the next step for you to go is pass in a comma-delimited CLOB, don't be afraid, Once you cast it to the PL/SQL CLOB from java CLOB, it's really easy to manipulate CLOB object in PL/SQL now with dbms_lob package.

I will delve into this as a fun project too....

0
 
LVL 5

Expert Comment

by:FBIAGENT
Comment Utility
The following code has not been tested but you should get the idea

create or replace type O_SIMPLESTRING as OBJECT(
 MYSTRING       VARCHAR2(100),
 )
;

create or replace type T_SIMPLESTRINGARRAY as TABLE of O_SIMPLESTRING
;


package StringArrayTestPackage;
import java.util.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class StringArrayTest
{
   public static oracle.sql.ARRAY getArray(Integer ArraySize)
   {
      String[] myString = new String[arraySize.intValue()];

      for (int i=0; i<arraySize.intValue(); i++) {
           myString[i] = "Entry " + (i+1);
      }
      return convertStringArrayToARRAY( myString, "O_SIMPLESTRING",
            "T_SIMPLESTRINGARRAY" );
   }

   public static oracle.sql.ARRAY convertStringArrayToARRAY(String[] myStrArray, String objectType, String tableType ) throws SQLException
   {
        Connection conn = new OracleDriver().defaultConnection();
        ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor( "SCHEMA_OWNER." + tableType.toUpperCase(), conn );
        return new ARRAY ( arrayDesc, conn, myStrArray );
   }
}


create or replace package P_StringArrayTest AS
    FUNCTION getArray RETURN T_SIMPLESTRINGARRAY;
    FUNCTION F_getArray RETURN T_SIMPLESTRINGARRAY;
end;
/

create or replace package body P_StringArrayTest AS

 FUNCTION getArray(p_num in number) RETURN T_SIMPLESTRINGARRAY
     as language JAVA
     NAME 'StringArrayTestPackage.StringArrayTest.getArray(java.lang.Integer) return oracle.sql.ARRAY';

 FUNCTION F_getArray(p_num in number) RETURN T_SIMPLESTRINGARRAY IS
  ret_value     T_SIMPLESTRINGARRAY;

 BEGIN
    return(getArray(p_num));
 END;

end P_StringArrayTest;
/

select a.mystring
  from table (
              select cast(P_StringArrayTest.F_getArray(8) as T_SIMPLESTRINGARRAY)
                from dual
             ) a
;
0
 

Author Comment

by:peddhapati
Comment Utility
Hi FBIAGENT,
       Your solution souds good, I will test it and let you know by tomorrow.

Thanks
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:peddhapati
Comment Utility
Hi FBIAGENT,
       Your solution sounds good, I will test it and let you know by tomorrow.

Thanks
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
peddhapati:

just in case the above code does not work for you.
I have done a little homework to test passing String array in and out from Java to plsql via Oracle SQL array.

the code detail

-----
---create a SQL string array type object
CREATE OR REPLACE TYPE strArray AS TABLE OF VARCHAR2(255);

--create a java class

create or replace and compile java source named "ArrayTest"
AS
import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class ArrayTest extends Object
{
      public static void passStrArray(oracle.sql.ARRAY p_instr, oracle.sql.ARRAY[] p_outstr)
      {
               String[] values = (String[]) p_instr.getArray();
               for (int i=0; i<p_instr.length(); i++)
               {
                          System.out.println("p_instr[" + i + "] = " + values(i));
               }
               Connection conn= new OracleDriver().defaultConnection();
               ArrayDescriptor desc = ArrayDescriptor.createDescriptor(p_instr.getSQLTypeName(), conn);
               p_outstr[0] = new ARRAY( descriptor, conn, values);
      }
}
/

---create a wrapper around java class;

create or replace procedure sp_strArray (p_in IN strArray, p_out OUT strArray)
AS language java name 'ArrayTest.passStrArray(oracle.sql.ARRAY, oracle.sql.ARRAY[])';

/

--My little test to demonstrate that you can pass the string Array in and out from java to plsql

SQL>declare
        l_instr strArray := strArray();
        l_outstr strArray :=strArray();
       begin
                for i IN 1..5 LOOP
                        l_instr.extend;
                        l_instr(i) := 'String '||i;
                 END LOOP;
                sp_strArray(l_instr, l_outstr);
                 for i IN 1..l_outstr.count LOOP
                 dbms_output.put_line('l_outstr(' || i ||') =' || l_outstr(i));
                 END LOOP;
       END;
       /
0
 

Author Comment

by:peddhapati
Comment Utility
Hi seazodiac,
   Looks like your solution works for me. Do you know which jar file I need to include in the classpath to use the following packages
import oracle.sql.*;
import oracle.jdbc.driver.*;

I am usning JDK 1.3.1_03



Thanks for your help.
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
you need these jar files to load in your classpath, these should be located under <oracle_home>/jdbc/lib/ directory.

classes12.jar classes12_g.jar, you just need these two for loading Oracle.sql* and oracle.jdbc.driver.*.
you can confirm these by using "jar" command,
for example:

c:\>jar -tvf classes12.jar

but for oracle jdbc programming, you should also include nls_charset12.jar in.
0
 

Author Comment

by:peddhapati
Comment Utility

Hi seazodiac,

 I get the following error....

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      ArrayTest:9: Exception java.sql.SQLException must be caught, or
         it must be declared in the throws clause of this method.

0/0      ArrayTest:12: Reference to variable values in class ArrayTest as
         if it were a method.

0/0      ArrayTest:16: Undefined variable: descriptor
0/0      Info: 3 errors
0
 
LVL 5

Expert Comment

by:FBIAGENT
Comment Utility
PEDDHAPATI,
Have you tried my solution?  SEAZODIAC's solution is an abbreviated version of my solution.
In any event, regarding your error, the following changes should be made

public static void passStrArray(oracle.sql.ARRAY p_instr, oracle.sql.ARRAY[] p_outstr)
throws SQLException  /* add this throws */
      {
               String[] values = (String[]) p_instr.getArray();
               for (int i=0; i<p_instr.length(); i++)
               {
                          System.out.println("p_instr[" + i + "] = " + values(i));
               }
               Connection conn= new OracleDriver().defaultConnection();
               ArrayDescriptor desc = ArrayDescriptor.createDescriptor(p_instr.getSQLTypeName(), conn);
//               p_outstr[0] = new ARRAY( descriptor, conn, values);
               p_outstr[0] = new ARRAY( desc, conn, values); /* typo here */
      }
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
FBIAGENT is absolutely correct about this. I did this for a short test without looking at PBIAGENT code.
change this line:p_outstr[0] = new ARRAY( descriptor, conn, values);
--->
  p_outstr[0] = new ARRAY( desc, conn, values);

0
 

Author Comment

by:peddhapati
Comment Utility
Hi FBIAGENT,
 
Your java code runs fine but the PL/SQL is givinf me the below error, when I run your script
create or replace package body P_StringArrayTest AS......:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/14     PLS-00323: subprogram or cursor 'GETARRAY' is declared in a
         package specification and must be defined in the package body

3/14     PLS-00323: subprogram or cursor 'F_GETARRAY' is declared in a
         package specification and must be defined in the package body

*************************************************************

Hi seazodiac

I get the following error when I compile your Java code after making the changes you 2 recommended:

ArrayTest.java:15: Reference to variable values in class ArrayTest as if it were a method.
                          System.out.println("p_instr[" + i + "] = " + values(i));

************************************************************

Thank you
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 500 total points
Comment Utility
Sorry, that's another typo. Bad typing..

This line:
System.out.println("p_instr[" + i + "] = " + values(i));

should be changed to :

System.out.println("p_instr[" + i + "] = " + values[i]);


hope this fixes it
0
 

Author Comment

by:peddhapati
Comment Utility
Hi seazodiac
   I got your solution working, thanks for all the help.

0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
You are very welcome. It's nice to hang out here.
0
 
LVL 5

Expert Comment

by:FBIAGENT
Comment Utility
PEDDHAPATI,
Here's the newly updated spec, it should work now.
By the way, if SEAZODIAC's code works, then your problem is resolved.  Good luck.

create or replace package P_StringArrayTest AS
    FUNCTION getArray(p_num in number) RETURN T_SIMPLESTRINGARRAY;
    FUNCTION F_getArray(p_num in number) RETURN T_SIMPLESTRINGARRAY;
end;
/

create or replace package body P_StringArrayTest AS

 FUNCTION getArray(p_num in number) RETURN T_SIMPLESTRINGARRAY
     as language JAVA
     NAME 'StringArrayTestPackage.StringArrayTest.getArray(java.lang.Integer) return oracle.sql.ARRAY';

 FUNCTION F_getArray(p_num in number) RETURN T_SIMPLESTRINGARRAY IS
  ret_value     T_SIMPLESTRINGARRAY;

 BEGIN
    return(getArray(p_num));
 END;

end P_StringArrayTest;
/
0
 

Author Comment

by:peddhapati
Comment Utility
FBIAGENT,

Your solution works too...

THANKS.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Query Syntax 6 84
oracle global variables 4 51
Out of Sequence numbers for today 25 47
Oracle Pivot 2 32
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now