PL/SQL: Passing an 'array' of strings as argument to sql

Question

Using plsql, I want to run my test.sql multiple times, each time will pass in a different argument to test.sql and also spool that result to a different file.

The file name may not have a relation with the argument being passed.

I'm hoping I can define two 'arrays'; one for the filename to spool to, and the other for the argument value.

declare
my_types sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll('typeA', 'typeB', 'typeC');
my_filenames sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll('fileNameForA', 'fileNameForB', 'fileNameForC');
begin
  for r in my_types.first..my_types.last
  loop
    --dbms_output.put_line(my_types(r));
    --dbms_output.put_line(my_filenames(r));
        spool my_filenames(r)
    @test.sql my_types(r);
        spool off
  end loop;
end;
/

For the spool, it says that it encountered symbol "MY_FILENAMES" when it expected := . < @ % ;. Also, it looks like test.sql is taking the argument I put in literally, so 'my_types(r)' instead of the expected 'typeA'. If there is a completely different and easier way of doing this in plsql then let me hear it. Thanks.

What is your test.sql? What is my_filenames? Where are you running this? spool is a SQL*Plus command... — Ben
– Ben, Commented Jun 2, 2014 at 20:21
@Ben test.sql is just a select statement that uses the argument in the where clause. my_filenames is a list of filenames to create when spooling. The first element in my_filenames should be the file created for running test.sql with the argument being the first element in my_types. — Rickkwa
– Rickkwa, Commented Jun 2, 2014 at 20:26
PL/SQL runs on the database server. SQLPlus is a client application that runs scripts located on the client. You can't call SQLPlus scripts from within PL/SQL, that doesn't make sense as an architecture. You can't put SQL*Plus commands like spool in PL/SQL. A PL/SQL block can generate files using utl_file but those files would exist on the database server not on the client. — Justin Cave
– Justin Cave, Commented Jun 2, 2014 at 21:00
I see. Then I guess this is more of a sqlplus question than a plsql question. Right, I will indeed be running this on sqlplus using a windows command line. — Rickkwa
– Rickkwa, Commented Jun 2, 2014 at 21:44

TenG · Accepted Answer · 2014-06-03 00:00:06Z

1

To get this to work (clunky, ugly), you have have to use the PLSQL to generate a sql scripts with calls to th esql script(s) you are trying to test. e.g.

set serveroutput on
spool run_it.sql
declare
   my_types sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll('typeA', 'typeB', 'typeC');
   my_filenames sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll('fileNameForA', 'fileNameForB', 'fileNameForC');
begin
  for r in my_types.first..my_types.last
  loop
    dbms_output.put_line('spool ' || my_filenames(r) );
    dbms_output.put_line('@test.sql ' || my_types(r) );
    dbms_out.put_line ('spool off' );
  end loop;
end;
/
spool off
@run_it.sql

Not tested/compiled. but I hope you get the idea.

With the above run_it.sql should look like:

spool fileNameForA
@test.sql typeA
spool off
spool fileNameForB
@test.sql typeB
spool off
.
.

answered Jun 3, 2014 at 0:00

TenG

4,0343 gold badges27 silver badges49 bronze badges

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

Collectives™ on Stack Overflow

PL/SQL: Passing an 'array' of strings as argument to sql

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