Oracle: Calling multiple procedures in a batch

Question

I need to call a number of different procedures within an Oracle package successively within a transaction. In fact, I need to call them about 5000 times. I was wondering what syntax I could use to batch these calls to Oracle, so I only need one round trip. I've tried "PACKAGE.PROCA :1, :2; PACKAGE.PROCB :3, :4, :5;" but I get back ORA00900: invalid SQL statement.

Any pointers? I know about passing arrays of parameters to procedures but that won't help much in this case unfortunately.

Answer 1

And incur a network round trip for each call? Sounds like death to performance. Might be better to rewrite the stored proc so you can cut down on the network traffic.

Answer 2

Simplest is a anonymous PL/SQL block. EG:

DECLARE
  v_file_loc varchar2(100) := '....';
  v_file_name varchar2(100) := '....';
  v_text varchar2(4000);
BEGIN
    dbms_output.put_line('Starting file read');
    fp := UTL_FILE.FOPEN(v_file_loc,v_file_name,'r');
    LOOP
        utl_file.GET_LINE(fp,v_text);
        dbms_output.put_line(v_text);
    END LOOP;
    UTL_FILE.FCLOSE(fp);
END;
/

Then you can go the extra step and actually create this as a procedure in the DB, and simply call the procedure. PS. The slash at the end tells SQL*Plus or SQL Developer to execute the code. It depends on what you are using to run the SQL.