Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Aug 2003
    Posts
    40

    Unanswered: Return Java String Array to PL/SQL Stored Function

    Is this possible. Currently my test code to attempt to place a simple
    String array into a stored Oracle function is provided. Note that
    the Oracle "type" is also stored in the database instance as a
    PL/SQL table of VARCHAR2(100).

    My problem is probably because I'm trying to return from java
    the array typed as "java.lang.String[]" which isn't contained in the
    "lang" library. Do I need to create some sort of wrapper or is this
    type of functionality not support in Oracle???

    The code I'm currently testing with is below and is placed into the
    database in the sequence depicted.

    a) Type declaration
    CREATE OR REPLACE TYPE SimpleArrayType AS TABLE OF VARCHAR2(100)
    /

    b) Stored Java class
    CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "StringArrayTest" AS

    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);
    }
    }
    /

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

    Here's the PL/SQL implementation block. When run in 9i, I receive
    ORA-00932: inconsistent datatypes: expected Unsupported conversion.

    SET LINE 200
    SET PAGES 200
    SET SERVEROUTPUT ON
    DECLARE
    vArraySize NUMBER := 10;
    vArray SimpleArrayType;
    BEGIN
    vArray := stringArrayTest(vArraySize);
    DBMS_OUTPUT.ENABLE(1000);
    FOR i IN 1..vArraySize LOOP
    DBMS_OUTPUT.PUT_LINE(vArray(i));
    END LOOP;
    END;
    /
    SET SERVEROUTPUT OFF

  2. #2
    Join Date
    Oct 2003
    Location
    Bay Area
    Posts
    2

    Re: Return Java String Array to PL/SQL Stored Function

    Hi
    I am trying to do the same, can you let me know if you were successull in passing an array from pl/sql to Java

    email: peddhapati@hotmail.com

    Thanks
    Bhaskar


    Originally posted by olerag
    Is this possible. Currently my test code to attempt to place a simple
    String array into a stored Oracle function is provided. Note that
    the Oracle "type" is also stored in the database instance as a
    PL/SQL table of VARCHAR2(100).

    My problem is probably because I'm trying to return from java
    the array typed as "java.lang.String[]" which isn't contained in the
    "lang" library. Do I need to create some sort of wrapper or is this
    type of functionality not support in Oracle???

    The code I'm currently testing with is below and is placed into the
    database in the sequence depicted.

    a) Type declaration
    CREATE OR REPLACE TYPE SimpleArrayType AS TABLE OF VARCHAR2(100)
    /

    b) Stored Java class
    CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "StringArrayTest" AS

    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);
    }
    }
    /

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

    Here's the PL/SQL implementation block. When run in 9i, I receive
    ORA-00932: inconsistent datatypes: expected Unsupported conversion.

    SET LINE 200
    SET PAGES 200
    SET SERVEROUTPUT ON
    DECLARE
    vArraySize NUMBER := 10;
    vArray SimpleArrayType;
    BEGIN
    vArray := stringArrayTest(vArraySize);
    DBMS_OUTPUT.ENABLE(1000);
    FOR i IN 1..vArraySize LOOP
    DBMS_OUTPUT.PUT_LINE(vArray(i));
    END LOOP;
    END;
    /
    SET SERVEROUTPUT OFF

  3. #3
    Join Date
    Oct 2003
    Posts
    1

    Re: Return Java String Array to PL/SQL Stored Function

    Hey...I just had to do this myself...basically PL/SQL has no mapping for String arrays, so instead you will have to parse your variables into one giant string and have them each separated with a delimiter.

  4. #4
    Join Date
    Oct 2003
    Location
    Bay Area
    Posts
    2

    Re: Return Java String Array to PL/SQL Stored Function

    Originally posted by Grandpoohpa
    Hey...I just had to do this myself...basically PL/SQL has no mapping for String arrays, so instead you will have to parse your variables into one giant string and have them each separated with a delimiter.

    Thank for the response. That is what I ended up doing now, but we are hitting the upper limit of Varchar2 which is 32KB (32767 bytes). String array is what I prefer... any ideas

  5. #5
    Join Date
    Dec 2003
    Posts
    4

    Unhappy

    I've also hit this 'wall'. I've posted an article on Oracle's metaLink PL/SQL forum about it (http://metalink.oracle.com/), but so far no reply.

    The idea of concatenating a set of strings into one long delimited string, occurred to me, and is do-able, except that once the string is back in PL/SQL-land, there isn't a 'SPLIT' function (8.1.x.y) to seperate it back out again, which Is why I want to pass an array.

    Actually, I want to pass back a 2-D array, and have it populate a collection of 100 arrays, each being an array of 6 VarChars...
    Last edited by radgoate; 12-19-03 at 08:28.

  6. #6
    Join Date
    Aug 2003
    Posts
    40
    For Oracle 9i developers...

    1. Create an Oracle type, such as:
    CREATE OR REPLACE TYPE SimpleStringArrayType AS TABLE OF VARCHAR2(500)
    /

    2. Make a Java class that returns a simple String array. The class
    must be stored in the database.

    CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ObtainStringArrayFromJava" AS
    import java.io.*;
    import java.sql.*;
    import java.math.*;
    import oracle.sql.*;
    import oracle.jdbc.driver.*;

    public class ObtainStringArrayFromJava {
    public static void returnStringArray(oracle.sql.ARRAY[] vReturnArray) throws
    java.sql.SQLException, IOException {
    int arrayLength = 10;
    String[] vTestArray = new String[arrayLength];

    for (int i=0; i<arrayLength; i++) {
    vTestArray[i] = "Java Entry " + (i+1);
    }

    Connection conn = new OracleDriver().defaultConnection();
    ArrayDescriptor desc =
    ArrayDescriptor.createDescriptor("SIMPLESTRINGARRA YTYPE",conn);
    vReturnArray[0] = new ARRAY(desc,conn,vTestArray);
    }
    }
    /

    3. Create the Oracle procedure that obtains the string array...

    PROCEDURE ObtainStringArrayFromJava(vReturnArray OUT SimpleStringArrayType) AS
    LANGUAGE JAVA NAME 'ObtainStringArrayFromJava.returnStringArray(oracl e.sql.ARRAY[])';

    4. To implement, you can run this test in an SQL session. This assumes
    that the procedure was a "public" procedure placed in the
    "JavaTestPackage" package.

    SET serveroutput ON SIZE 2000
    DECLARE
    vArray SimpleStringArrayType := SimpleStringArrayType();
    BEGIN
    JavaTestPackage.ObtainStringArrayFromJava(vArray);

    DBMS_OUTPUT.PUT_LINE(CHR(09));
    DBMS_OUTPUT.PUT_LINE('Test Example #1, Return String array from Java.');
    DBMS_OUTPUT.PUT_LINE(CHR(09));
    FOR i IN 1..vArray.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('PL/SQL element ' || i || ' obtains the value "' || vArray(i) || '".');
    END LOOP;
    END;
    /
    SET serveroutput OFF


    You can also create PL/SQL tables that can be accessed by
    Java simple string arrays. For this, you'll still utilize the type created
    in #1, above, and also include a Java wrapper class that maps to the
    type.

  7. #7
    Join Date
    Dec 2003
    Posts
    4

    Unhappy

    Thanks, OleRag.

    I tried this (Oracle 8.1.4), but I'm getting an error stating PLS-00225 subprogram or cursor 'OBTAINSTRINGARRAYFROMJAVA' is out of scope...

    Assuming that I can get this to work, can I also get an array returned from Java as the return value of a function like:

    Function FooBar(Input IN varchar2)
    LANGUAGE JAVA NAME 'Foo.Bar(java.lang.String)
    REURN oracle.sql.ARRAY[]';

    or do I have to have an OUT param as in your example?

    Furthermore, how would I obtain a 2-D array? (An array(100) of array(6) of varchar2(100) ) ?

  8. #8
    Join Date
    Aug 2003
    Posts
    40
    No, unfortunately, to both. I've only seen working examples of
    Java "simple" arrays and the process is a "procedure"; not a
    function.

    For this to work, your going to need to run Oracle 9i.

    The "kicker"?? The examples I found were not in the Oracle
    forums but in others. Oracle only appeared to offer returning
    primitive types (String,Integer,Number,Double). These can be
    used to return as Oracle functions but not a single-dim array.

    For a 2-D array (as you indicated), I can only come up with making
    your Oracle "type" somewhat large and use a delimiter between your
    element contents.

    If anyone knows of resolutions to this or, if they believe I'm in error,
    please correct me. I'd really like to be able to return multi-dim
    arrays to Oracle but, when you think about it, Oracle doesn't have
    a multi-dimensional array type in PL/SQL (at least not that I'm aware
    of).

  9. #9
    Join Date
    Dec 2003
    Posts
    4

    Talking

    ... but it can handle collections of collection types, so if I build one collection of say six VarChars, and then 'wrap' that with a collection of size 100, I get a 100 x 6 array of varchars....

  10. #10
    Join Date
    Aug 2003
    Posts
    40
    Are you "asking" or "telling"? If the later, please provide some
    support code that permits a Java String[][] object (previously
    populated and stored in the database) to transfer its results to
    an Oracle object.

    Seriously, I'd love to see it.

  11. #11
    Join Date
    Dec 2003
    Posts
    4

    Wink

    ...so would I !

    I was just stating that it is possible to build a sort-of multi-dimension array by creating a collection of collections. I know this isn't a real 2D-aray, but would work in respect of holding the data in the way required.

    My question really is 'can this structure be populated from Java', and it seems that you've already said it can't.
    Oh, well, back to the drawing board...

  12. #12
    Join Date
    Aug 2003
    Posts
    40
    Probably better instead of "can't" is "I don't know and have not, as
    yet, seen any examples".

    However, if the time were taking to...

    1. Write a new Oracle object for the storage.
    2. Return the Java simple array into a large PL/SQL table, containing
    some delimited chars.
    3. Extract the contents of the PL/SQL table into the object of 1, above.

    then object in 1, above, could represent a multi-dim array.

    The workload would be in having a number of arrays created to handle
    the amount of elements in the second element position and running
    a string extraction process to fill those elements.

  13. #13
    Join Date
    Jan 2004
    Posts
    1

    Thank you for the solution

    Hi OleRag,

    Thank you.
    I used your solution in Oracle 8i without problem.

  14. #14
    Join Date
    Mar 2004
    Posts
    1
    Hi,

    I managed to get multi-dimentional arrays working, by using a combination of arrays and structs. The struct
    represents my Object type, while the array is the nested table of objects type.

    I have two types defined.

    CREATE TYPE admo_identifier_person AS OBJECT
    (
    person_id NUMBER(10),
    surname VARCHAR2(30),
    given_names VARCHAR2(40),
    birth_dt DATE,
    identifier VARCHAR2(30)
    )
    /

    CREATE TYPE admo_identifier_person_t
    AS TABLE OF admo_identifier_person
    /

    My test java stored procedure to populate a instance of the ADMO_IDENTIFIER_PERSON is as follows, based on example previously given. This java stored procedure method is in ADMO_IDENTIFIER class in au.com.callista.ws package.

    public static void returnStringArray(oracle.sql.ARRAY[] vReturnArray) throws java.sql.SQLException, IOException
    {
    Connection conn = new OracleDriver().defaultConnection();
    StructDescriptor personIdentifierDesc = StructDescriptor.createDescriptor("ADMO_IDENTIFIER _PERSON", conn);
    ArrayDescriptor personIdentifierTableDesc = ArrayDescriptor.createDescriptor("ADMO_IDENTIFIER_ PERSON_T", conn);

    Object[] obj = new Object[5];
    obj[0] = new Integer(43131231);
    obj[1] = "PELL";
    obj[2] = "JASON";
    obj[3] = new java.sql.Date(1974,01,14);
    obj[4] = "31312312131";

    vReturnArray[0] = new ARRAY(personIdentifierTableDesc,conn,new Object[]{new STRUCT(personIdentifierDesc, conn, obj)});
    }


    My plsql object member looks like:

    STATIC PROCEDURE ObtainStringArrayFromJava(vReturnArray OUT ADMO_IDENTIFIER_PERSON_T) AS
    LANGUAGE JAVA NAME 'au.com.callista.ws.ADMO_IDENTIFIER.returnStringAr ray(oracle.sql.ARRAY[])'

    My plsql tester script looks like:

    DMO_IDENTIFIER.ObtainStringArrayFromJava(v_Array);

    FOR i IN 1..v_Array.COUNT LOOP
    dbms_output.put_line('record found! ');
    dbms_output.put_line('Person_ID: '||TO_CHAR(v_Array(1).person_id));
    END LOOP;


    I really appreciate the help this particular forum topic shed on my problem, so I thought my further success might help someone else.

    Regards
    Jason

  15. #15
    Join Date
    Aug 2003
    Posts
    40
    Looks good Jason - I'm gonna play with this next week. The silly thing
    (well, 2 silly things):

    1. I was so wrapped up with Java->Oracle, I never thought about
    attempting with an object as the oracle.sql.ARRAY[] type implies a
    simply array. I should try things without "listening" to Oracle, but thats
    my fault.

    2. Now that 10g is out, I wonder if alot of this will be improved and/or
    streamlined? Will probably find the answer when Oracle releases 11
    (or whatever) because they (Oracle) sure don't like to publish clear and
    concise instructions.

    Actually, your Java example displays:

    Object[] obj = new Object[5];
    obj[0] = new Integer(43131231);
    obj[1] = "PELL";
    obj[2] = "JASON";
    obj[3] = new java.sql.Date(1974,01,14);
    obj[4] = "31312312131";

    What I'll try to produce is something like...

    Object[][] obj = new Object[2][5];
    obj[0][0] = new Integer(43131231);
    obj[0][1] = "PELL";
    obj[0][2] = "JASON";
    obj[0][3] = new java.sql.Date(1974,01,14);
    obj[0][4] = "31312312131";

    obj[1][0] = new Integer(88888888);
    obj[1][1] = "LAST NAME";
    obj[1][2] = "FIRST NAME";
    obj[1][3] = new java.sql.Date(2004,01,15);
    obj[1][4] = "99999999999";
    Last edited by olerag; 03-04-04 at 12:50.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •