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.