aspose file tools*
The moose likes JDBC and the fly likes Multiple arrays to SP Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Multiple arrays to SP" Watch "Multiple arrays to SP" New topic
Author

Multiple arrays to SP

arun shankar
Greenhorn

Joined: Jul 24, 2012
Posts: 13
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 :



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



Can anyone please help.

Thanks & BR,
arun
arun shankar
Greenhorn

Joined: Jul 24, 2012
Posts: 13
The contents of input file is as below :



Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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
Greenhorn

Joined: Jul 24, 2012
Posts: 13
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 :




The complete output is as below :



Please suggest

Thanks
Arun
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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
Greenhorn

Joined: Jul 24, 2012
Posts: 13
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

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Multiple arrays to SP