How to "scale" scripts with substitution variables using loops?

Question

I am often using substitution variables in my scripts to not have to duplicate things :

select count(*) from &&my_table;
select * from &&my_table;
-- etc.
-- a ton more queries here that are used often in together to check and run things

where :

define my_table = 'dual'; -- bad example

But I often have to change my_table when I switch to another flow (as another table become the central point of my queries).

Can I make arrays of these type for variables? (Obviously I searched around and did not found how, but what can I do be able to loop through a set of values, tables names in my case, fill in the each table in the variable and run all the queries again, and again?)

Use a [batch file](http://stackoverflow.com/questions/18434254/how-to-iterate-over-array-in-batch-for-key-value-item) or [unix script](http://stackoverflow.com/questions/8880603/loop-through-array-of-strings-in-bash-script) to loop through your array and call your [SQL script with parameters](http://stackoverflow.com/questions/2236201/how-can-i-pass-parameter-to-sql-script) — Mottor, Jun 30 '16 at 14:00
@Mottor I need to see the result in SQL Developer or something, not just fire and forget — Belun, Jun 30 '16 at 15:22
Then redirect the script output to a file and view the file when you want to review it. — MT0, Jun 30 '16 at 15:33

score 0 · Answer 1 · answered Jun 30 '16 at 11:44

0

Can I make arrays of these type for variables?

Yes, use Oracle's collection types:

DEFINE
  TYPE table_name_table IS TABLE OF VARCHAR2(30);
  table_names  table_name_table := table_name_table( 'TABLE1', 'TABLE2', 'DUAL' );
  my_table VARCHAR2(30);
BEGIN
  FOR i IN 1 .. table_names.COUNT LOOP
    my_table := table_names(i);
    -- process your script...
  END LOOP;
END;
/

answered Jun 30 '16 at 11:44

MT0

143,790
11
59
117

you missed the point. where is a substitution variable defined in your solution? you cannot do : select count(1) from my_table – Belun Jun 30 '16 at 12:02
Yes you can with dynamic sql and bind variables. sql := 'select count(*) into :1 from' || my_table; execute immediate sql USING myCountVar; – dvsoukup Jun 30 '16 at 14:47
that is not a bind variable. it is a normal variable – Belun Jun 30 '16 at 15:23

score 0 · Answer 2 · answered Jun 30 '16 at 12:51

0

I guess that you want to use dynamic sql so you can substitute the table name.

https://docs.oracle.com/cloud/latest/db112/LNPLS/dynamic.htm#LNPLS011

answered Jun 30 '16 at 12:51

RayCW

174
2
10

I would like to avoid code generation and the "execute" command, if possible. Substitution variables have been fine until now, but I want to improve further on my scripts (I want to loops) – Belun Jun 30 '16 at 12:59
You don't have to do code generation, just use the table name as a parameter to your call to a PL/SQL package/procedure. – RayCW Jun 30 '16 at 13:03
1

@Belun There's nothing wrong with using the execute command. It's there for reasons such as what you're trying to achieve in your question. – dvsoukup Jun 30 '16 at 14:51
i have to output text myself. if i make a select, i have to output the results. and i do not want this. i want to selects in the nice table view that ide (oracle sql developer) have to offer. – Belun Jun 30 '16 at 15:36

score 0 · Answer 3 · answered Jun 30 '16 at 16:28

Not entirely sure what you want to do, but there isn't a simple construct in SQL*Plus for this. The closest you could do is as follows:

Your worker.sql script that runs the queries for a given table:

define &tabname='&1'

PROMPT &tabname

SELECT count(1) FROM &tabname;

Next the gendriver.sql script to accept your list of tables and drive the worker script:

ACCEPT tablist CHAR PROMPT 'Enter table comma separated list: '

spool driver.sql
SET SERVEROUTPUT ON
set feedback off
set verify off
DECLARE
   L_TABLE        DBMS_UTILITY.UNCL_ARRAY;
   L_TABLELEN     BINARY_INTEGER;
BEGIN
   DBMS_UTILITY.COMMA_TO_TABLE ('&tablist', L_TABLELEN, L_TABLE);
   FOR I IN L_TABLE.FIRST .. L_TABLE.LAST
   LOOP
      IF l_table(i) IS NOT NULL THEN
         DBMS_OUTPUT.PUT_LINE( '@worker.sql ' || l_table(i) );
      END IF;
   END LOOP;
END;
/
spool off

Run as and enter a comma separated list of tables, e.g:

Enter table comma separated list: tab1, tab2, tab3, tab4

The resulting driver.sql script will look like this:

@worker.sql tab1
@worker.sql  tab2
@worker.sql  tab3
@worker.sql  tab4

Then just run the driver.sql

@driver.sql

score 0 · Answer 4 · answered Jul 01 '16 at 08:39

There is no loop command in SQLPlus. To fill variables, you can use something like this:

SET verify OFF
column t1 new_value tname1
column t2 new_value tname2
select &table_name from dual;
-- Your script
select count(*) from &&tname1;
select * from &&tname1;
-- Your script again
select count(*) from &&tname2;
select * from &&tname2;

and then use "'dual' t1, 'dual' t2" as value for table_name parameter.

Yes, I know that this is not a solution. I do not understand what you want to achieve. If you want to execute the same set of commands for different tables, use SQL Editor Code Templates in SQL Developer with variables or with placeholders or use different Snippets for different flows. If you want reports, use PL/SQL, dynamic sql, pipeline functions, script generation.

How to "scale" scripts with substitution variables using loops?

4 Answers4