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 :
Am getting the Following Error at "ARRAY ora_current_state_array = new ARRAY (oracleVarchar2Collection, conn, card_current_state_array);" :
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.
Joined: Jul 24, 2012
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;
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.
Joined: Jul 24, 2012
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 :