Using Stored Procedures via java and sql plus

Question

We manage a java application that allows to select lines by setting boxes and validate.

I want to create a second process to reproduce the same actions of the java application but in shell. Looking in the java code, I find that the application uses sql procedures.

The problem in java processing takes only a few seconds but in sql plus 15 or 30 minutes.

It seems to me that in java the procedure is executed once in block but in shell I constituted a sql script and I execute line by line, so the processing time is variable according to the number of lines to be executed.

in java : 
       public static void insertTable(String Id, String[] List, String group)
                     throws SQLException {
              CallableStatement statement = null;
              Connection conn = null;
              try {
                     String procedure = "begin insertTable(?,?, ?); end;";
                     conn = DBManager.getConnection();
                     statement = conn.prepareCall(procedure);
                     for (int i = 0; i < List.length; i++) {
                           statement.setString(1, Id);
                           statement.setString(2, List[i]);
                statement.setString(3, group);
                           statement.execute();
                     }

in script sql : 

set serveroutput on
whenever sqlerror exit SQL.SQLCODE
exec insertTable ('1575','TATA-11','FFF');
exec insertTable ('1574','TOTO-22','RRR');
exec insertTable ('1573','TITI-33','NNN');
exec insertTable ('1572','JOJO-44','UUU');
.....
exit 0;

The lines to be executed are variable, one can have from 2000 to 10 000 lines, thus an average speed of execution of 15 lines by 2 seconds ... also a writing in a file log ... all that slows down the treatments.

Is there a way to execute the procedure once for all lines? A way to speed up the execution of the procedure?

why not to pass an array to a pl/sql proc?

hotfix
– hotfix

2019-12-05 21:04:48 +00:00
Commented Dec 5, 2019 at 21:04 — hotfix
– hotfix, Commented Dec 5, 2019 at 21:04

Jon Heller · Accepted Answer · 2019-12-06 04:37:17Z

1

Use a PL/SQL block instead of SQL*Plus commands. SQL*Plus commands are sent one-at-a-time and can incur a lot of overhead, whereas a PL/SQL block will be sent as a single command.

Change the script from:

exec insertTable ('1575','TATA-11','FFF');
exec insertTable ('1574','TOTO-22','RRR');
exec insertTable ('1573','TITI-33','NNN');
exec insertTable ('1572','JOJO-44','UUU');

To:

begin
    insertTable ('1575','TATA-11','FFF');
    insertTable ('1574','TOTO-22','RRR');
    insertTable ('1573','TITI-33','NNN');
    insertTable ('1572','JOJO-44','UUU');
end;
/

answered Dec 6, 2019 at 4:37

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Stored Procedures via java and sql plus

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related