CODEWITHSUNDEEP
javaoracle-databasestored-proceduressqlplusjava-stored-procedures
DavidOl
DavidOl

Reputation: 1

Using Stored Procedures via java and sql plus

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?

Upvotes: 0

Views: 209

Answers (1)

Jon Heller
Jon Heller

Reputation: 36798

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;
/

Upvotes: 1

Related Questions