Multiple arrays to SP

[arun shankar]

Hi Java experts,

Am beginning to learn Java recently. I am currently writing a program which will read a file line by line, split the line based on delimiter store in array and finally send multiple arrays to Oracle SP as input for inserts in to tables. The Java code is as below :

public class test_connection {  
   
    public static void main(String[] argv) throws FileNotFoundException {  
        File f = new File("C:/input_mtnn.txt");  
        if (f.exists())  
        {  
            BufferedReader br = null;  
            try {  
                 br = new BufferedReader(new FileReader("C:/input_mtnn.txt"));  
                String strLine;  
                java.util.List<String> dn = new ArrayList<String>();  
                java.util.List<String> current_state = new ArrayList<String>();  
                Integer i = 0;  
                @SuppressWarnings("unused")  
                String line = null;  
                //Read File Line By Line  
                while ((strLine = br.readLine()) != null)   {  
                    // Print the content on the console  
                    //System.out.println (strLine);  
                    System.out.println ("Line read is " + strLine);  
                    Pattern p = Pattern.compile("\\|");  
                    String[] result = p.split(strLine);  
                    dn.add(result[0]);  
                    current_state.add(result[1]);  
                }  
                System.out.println("The content of DN is: " + dn);  
                System.out.println("The size of current_state is: " + current_state.size());  
                  
                try {  
                       
                    Class.forName("oracle.jdbc.driver.OracleDriver");  
           
                } catch (ClassNotFoundException e) {  
           
                    System.out.println("Where is your Oracle JDBC Driver?");  
                    e.printStackTrace();  
                    return;  
           
                }  
           
                System.out.println("Oracle JDBC Driver Registered!");  
           
                try {  
           
                    Connection conn = DriverManager.getConnection(  
                            "jdbc:oracle:thin:@fattony.evolving.com:1565:cpacni", "ni06",  
                            "ni061");  
                      
                    ArrayDescriptor oracleVarchar2Collection = ArrayDescriptor.createDescriptor("VARCHAR_LIST",conn);  
                    String[] card_dn_array = new String[dn.size()];  
                    String[] card_current_state_array  = new String[current_state.size()];  
                    for (int i1 = 0; i1 < dn.size(); i1++) {  
                        if((dn.get(i1).equals(null))){  
                        }  
                        else    {  
                            card_dn_array[i1] = dn.get(i1);  
                        }  
                    }  
                    for (int i1 = 0; i1 < current_state.size(); i1++) {  
                        System.out.println("Value got : " + current_state.get(i1));  
                        if((current_state.get(i1) == null)){  
                            System.out.println("Got Null");  
                        }  
                        else    {  
                            System.out.println("Not null");  
                            card_current_state_array[i1] = current_state.get(i1);  
                        }  
                    }  
                    for (int i1 = 0; i1 < card_current_state_array.length; i1++) {  
                        System.out.println(" card_current_state_array array " + card_current_state_array[i1]);  
                    }  
                    ARRAY ora_current_state_array = new ARRAY (oracleVarchar2Collection, conn, card_current_state_array);  
                    ARRAY ora_dn_number    = new ARRAY (oracleVarchar2Collection, conn, card_dn_array);  
                    System.out.println("Call started");  
                      
                    OraclePreparedStatement stmt =  (OraclePreparedStatement)conn.prepareStatement  
                                                    ("begin return_output_talend.return_output( ? , ? ); end;");  
                    System.out.println("Call completed");  
                    stmt.setObject(1, ora_dn_number);  
                    stmt.setObject(2, ora_current_state_array);  
                    stmt.execute();  
                      
                } catch (SQLException e) {  
           
                    System.out.println("Connection Failed! Check output console");  
                    e.printStackTrace();  
                    return;  
                }  
  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
            finally {  
                try {  
                    if (br != null)br.close();  
                } catch (IOException ex) {  
                    ex.printStackTrace();  
                }  
            }  
        }  
        else {  
            System.out.println("File not found!");  
        }  
    }  
   
}  

Am getting the Following Error at "ARRAY ora_current_state_array = new ARRAY (oracleVarchar2Collection, conn, card_current_state_array);" :

 
  
Exception in thread "main" java.lang.NumberFormatException: For input string: "Ar"  
    at java.lang.NumberFormatException.forInputString(Unknown Source)  
    at java.lang.Integer.parseInt(Unknown Source)  
    at java.lang.Integer.parseInt(Unknown Source)  
    at oracle.sql.NUMBER.toBytes(NUMBER.java:1943)  
    at oracle.sql.NUMBER.stringToBytes(NUMBER.java:3494)  
    at oracle.sql.NUMBER.<init>(NUMBER.java:283)  
    at oracle.jdbc.oracore.OracleTypeNUMBER.toNUMBER(OracleTypeNUMBER.java:536)  
    at oracle.jdbc.oracore.OracleTypeNUMBER.toNUMBERArray(OracleTypeNUMBER.java:577)  
    at oracle.jdbc.oracore.OracleTypeNUMBER.toDatumArray(OracleTypeNUMBER.java:66)  
    at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDescriptor.java:1314)  
    at oracle.sql.ARRAY.<init>(ARRAY.java:152)  
    at test_connection.main(test_connection.java:94)  

Can anyone please help.

Thanks & BR,
arun

[arun shankar]

The contents of input file is as below :

1000|Arun
1001|shankar
1006|Guruji
100020|Amma
1111|Sainatha

[Martin Vajsar]

Welcome to the Ranch!

You'll probably have to show us the stored procedure declaration (at least the header) and the SQL type declaration (the VARCHAR_LIST). Also, make sure you've posted the full contents of the source file, I'd say the line numbers in the stack trace are a little bit off.

[arun shankar]

Hello Martin,

Thank you so much for your reply.

The VARCHAR_LIST declaration is as below :

TYPE VARCHAR_LIST IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

The Stored Procedure is as below :

CREATE OR REPLACE PACKAGE return_output_talend as
        TYPE VARCHAR_LIST IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
        PROCEDURE return_output (p_input_iccid_array IN VARCHAR_LIST, p_input_current_state IN VARCHAR_LIST);
    --dml_errors EXCEPTION;
    --PRAGMA EXCEPTION_INIT(dml_errors, -24381);
END return_output_talend;
/
SHOW ERRORS;

CREATE OR REPLACE PACKAGE BODY return_output_talend AS

        PROCEDURE return_output (p_input_iccid_array IN VARCHAR_LIST, p_input_current_state IN VARCHAR_LIST)
        AS
                va_iccid VARCHAR_LIST;
        BEGIN
                BEGIN
                        FORALL idx IN p_input_iccid_array.first..p_input_iccid_array.last
                                INSERT INTO TEMP_TAb1 VALUES (p_input_iccid_array(idx), p_input_current_state(idx));
                                COMMIT;
                EXCEPTION
                        WHEN OTHERS THEN
                        DBMS_OUTPUT.PUT_LINE('Exception occured');
                END;
        END return_output;
END return_output_talend;
/

SHOW ERRORS;
EXIT

The complete output is as below :

Line read is 1000|Arun
Line read is 1001|shankar
Line read is 1006|Guruji
Line read is 100020|Amma
Line read is 1111|Sainatha
The content of DN is: [1000, 1001, 1006, 100020, 1111]
The size of current_state is: 5
Oracle JDBC Driver Registered!
Value got : Arun
Not null
Value got : shankar
Not null
Value got : Guruji
Not null
Value got : Amma
Not null
Value got : Sainatha
Not null
 card_current_state_array array Arun
 card_current_state_array array shankar
 card_current_state_array array Guruji
 card_current_state_array array Amma
 card_current_state_array array Sainatha
Exception in thread "main" java.lang.NumberFormatException: For input string: "Ar"
	at java.lang.NumberFormatException.forInputString(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at oracle.sql.NUMBER.toBytes(NUMBER.java:1943)
	at oracle.sql.NUMBER.stringToBytes(NUMBER.java:3494)
	at oracle.sql.NUMBER.<init>(NUMBER.java:283)
	at oracle.jdbc.oracore.OracleTypeNUMBER.toNUMBER(OracleTypeNUMBER.java:536)
	at oracle.jdbc.oracore.OracleTypeNUMBER.toNUMBERArray(OracleTypeNUMBER.java:577)
	at oracle.jdbc.oracore.OracleTypeNUMBER.toDatumArray(OracleTypeNUMBER.java:66)
	at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDescriptor.java:1314)
	at oracle.sql.ARRAY.<init>(ARRAY.java:152)
	at test_connection.main(test_connection.java:94)

Please suggest

Thanks
Arun

[Martin Vajsar]

Please have a look at this chapter of Oracle docs (at the very least). The document I linked is from the 11gR2 documentation, if you use a different version of JDBC driver, look up the correct version of the doc for your driver at our Oracle FAQ.

The VARCHAR_LIST type is declared in a package. Such a type cannot be used with JDBC. You'll need to create a SQL type using the CREATE TYPE SQL command (look it up in the Oracle docs if you need). Types created this way can be used in SQL, PL/SQL and Java. I believe you'll be limited to the length of 4000 and won't be able to use the INDEX BY BINARY_INTEGER, but I'm not that skilled in (PL/)SQL collections to know for sure.

A few additional notes:

1) You're committing after every insert. Commits in PL/SQL procedures are asynchronous, but even so, this is a performance issue. Moreover, you should commit only logical units of work. In my opinion, the procedure should not commit at all, you should control the transactions from the Java side. Currently, if an error occurs in the middle of the processing, the affected rows are simply skipped without the error being clearly indicated to the caller.

2) You have the EXCEPTION WHEN OTHERS and do not re-raise the exception. Putting it into DBMS_OUTPUT is insufficient, as no one will ever look there in production. I'd consider this a serious bug. Though you could re-raise the exception, my advice is not to handle it at all; that way it will propagate into Java as an SQLException, and you'll even get the PL/SQL stack trace there!

3) What do the p_input_iccid_array and p_input_current_state functions do? If they simply convert the input strings into some other value, my advice would be to move the conversion to the Java side and use PreparedStatement plus statement batching (if you have at least the Oracle 11g JDBC driver). I'd say the performance will be at least as good as your current approach, if not better. If the logic is more complicated, having it in the DB might be better, though.

[arun shankar]

Thank you so much for your inputs. I implemented your suggestions related to Exception handling and I see my code handles exception well now.

The reason for the above error was my fault ( really very silly mistake !!! I think I did not observe correctly ). In the Stored Procedure the type "VARCHAR_LIST" was declared as table of number but am passing string array... Corrected the same i.e changed it to varray(100) of varchar2(32767), but now null (empty) value was getting inserted into the table. After some Google search, found similar issue was faced by many people. So, the Change proposed was to change varchar2 to nvarchar2 (strange really). Below is the link :

http://forum.springsource.org/showthread.php?26598-Problem-with-Arrays-in-StoredProcedure

It works fine now.

As far as I know, the only difference is that nvarchar2 is used for non- unicode characters.

Please let me know if you know the reason for above