How can I execute only selective statements from an SQL file?

Question

I have a .sql file that has a sequence of create table statements, for example:

crtab.sql

 define ll='&1'; define ul='&2'; create table TAB1 (...); create table TAB2 (...); create table TAB3 (...); ... create table TAB1000 (...); 

The user passes two parameters as arguments - the lower limit of ll and the upper limit of ul , which indicate the range of table numbers to be created.

---

For instance,

 sqlplus crtab.sql 3 67 

should only create tables TAB3 to TAB67 .

How can I implement this logic in a .sql file?

Answer 1

• Use an associative array , and insert all queries into this array as follows:

myArray(1) := 'CREATE TAB 1 ...';

...

myArray(1000) := 'CREATE TAB 1000 ...';

2. Iterate through ll to ul and execute these queries, that is.

 for i in ll..ul loop @sql = myArray(i); exec @sql; end loop; 

I apologize for any syntax errors.

Answer 2

Maybe like these

 CREATE OR REPLACE PROCEDURE TABS_V1 (ll number, ul number) IS BEGIN IF 1 BETWEEN ll AND ul THEN EXECUTE IMMEDIATE 'create table TAB1 (...)'; END IF; IF 2 BETWEEN ll AND ul THEN EXECUTE IMMEDIATE 'create table TAB2 (...)'; END IF; IF 3 BETWEEN ll AND ul THEN EXECUTE IMMEDIATE 'create table TAB3 (...)'; END IF; ... IF 1000 BETWEEN ll AND ul THEN EXECUTE IMMEDIATE 'create table TAB1000 (...)'; END IF; END; CREATE OR REPLACE PROCEDURE TABS_V2 (ll number, ul number) IS TYPE tabs IS TABLE OF VARCHAR2(4000) INDEX BY NUMBER; tabs all_tabs; BEGIN all_tabs(1) = 'create table TAB1 (...)'; all_tabs(1) = 'create table TAB2 (...)'; all_tabs(1) = 'create table TAB3 (...)'; ... all_tabs(1000) = 'create table TAB1000 (...)'; FOR cnt IN ll .. ul LOOP EXECUTE IMMEDIATE all_tabs(cnt); END LOOP; END; 

Answer 3

Assuming the table structures are the same, you'd better create and execute dynamic sql statements in a loop based on your ll and ul. e.g. (in sql server syntax)

 declare @sql varchar(1000) declare @i int set @i = @ll while @i <= @ul begin set @sql = 'create table TAB' + cast( @i as varchar ) + '(...);' exec @sql set @i = @i + 1 end 

If the table structures are different from each other, just put the if statement from each creation.

Answer 4

There are several ways to do this. First, you can use an anonymous block with a nested statement table or an associative array of statements. A nested table approach requires a slightly smaller change to an existing script, but there is a risk that the numbers will fail. Note that I am using an alternative citation mechanism, for example. q '[character] ... [character]', in case your DDL contains some apostrophes.

This code is similar to some other answers, but with Oracle syntax and does not require the creation of additional objects.

Nested table script:

 --Only create the tables between the two values (nested table) declare type varchar_tab is table of varchar2(32767); table_statements varchar_tab := varchar_tab( q'!create table tab1 (test1 number)!', q'!create table tab2 (test1 number)!', q'!create table tab3 (test1 number)!', q'!create table tab4 (test1 number)!' ); begin for i in &1 .. &2 loop execute immediate table_statements(i); end loop; end; / 

Associative array script:

 --Only create the tables between the two values (associative array) declare type varchar_tab is table of varchar2(32767) index by number; table_statements varchar_tab; begin table_statements(1) := q'!create table tab1 (test1 number)!'; table_statements(2) := q'!create table tab2 (test1 number)!'; table_statements(3) := q'!create table tab3 (test1 number)!'; table_statements(4) := q'!create table tab4 (test1 number)!'; --Only create the tables between the two values for i in &1 .. &2 loop execute immediate table_statements(i); end loop; end; / 

If you want your script to be almost identical to its current form, another approach would be to run the entire script and then add an anonymous block at the end that removes unnecessary tables. This keeps the top of your script very simple, but obviously there may be some problems with automatically dropping tables.

 --Drop all the tables except for those within the range declare table_does_not_exist exception; pragma exception_init(table_does_not_exist, -00942); begin for i in 1 .. 1000 loop if i between &1 and &2 then null; else begin execute immediate 'drop table tab'||i; exception when table_does_not_exist then null; end; end if; end loop; end; / 

Answer 5

One hacky way to do this is to use the C preprocessor and #if directives to enable or disable each baed statement on some macros that you define on the command line. Then run the pre-processed file, not the original.