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 or unix script to loop through your array and call your SQL script with parameters — Mottor
– Mottor, Commented Jun 30, 2016 at 14:00
@Mottor I need to see the result in SQL Developer or something, not just fire and forget — Belun
– Belun, Commented Jun 30, 2016 at 15:22
Then redirect the script output to a file and view the file when you want to review it. — MT0
– MT0, Commented Jun 30, 2016 at 15:33

MT0 · Accepted Answer · 2016-06-30 11:44:22Z

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, 2016 at 11:44

MT0

173k12 gold badges69 silver badges136 bronze badges

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

3 Comments

Belun Over a year ago

you missed the point. where is a substitution variable defined in your solution? you cannot do : select count(1) from my_table

dvsoukup Over a year ago

Yes you can with dynamic sql and bind variables. sql := 'select count(*) into :1 from' || my_table; execute immediate sql USING myCountVar;

Belun Over a year ago

that is not a bind variable. it is a normal variable

RayCW · Accepted Answer · 2016-06-30 12:51:41Z

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, 2016 at 12:51

RayCW

1842 silver badges10 bronze badges

4 Comments

Belun Over a year ago

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)

RayCW Over a year ago

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.

dvsoukup Over a year ago

@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.

Belun Over a year ago

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.

TenG · Accepted Answer · 2016-06-30 16:28:19Z

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

Mottor · Accepted Answer · 2016-07-01 08:39:29Z

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.

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

4 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

4 Comments

Comments

Comments

Linked

Related