CODEWITHSUNDEEP
sqloracle-sqldevelopersubstitution
Stochastika
Stochastika

Reputation: 305

Oracle SQL substitution variable, the scope of substitution

I have been pondering about what and how much I can actually put into the substitution variable. can we only have it to replace a single 'entity' but not a string of command? I found if I want to substitute a block of actual SQL, it did not work (in SqlDeveloper).

I wonder how I can get around this...

/*------test--------------*/
CREATE TABLE Test_Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255)
);

INSERT INTO Test_Persons
    (PersonID,LastName,FirstName)
    values(1,'LN_1','FN_1');

INSERT INTO Test_Persons
    (PersonID,LastName,FirstName)
    values(2,'LN_2','FN_2');

INSERT INTO Test_Persons
    (PersonID,LastName,FirstName)
    values(3,'LN_21','FN_2');

commit;

--------------sub-table, worked!---
set define #;

define first_name_input = 'FN_2';
define last_name_input = 'LN_2';
define tbl_Test_Persons = 'Test_Persons';
define temp_tbl_Test_Persons_FN = 'Test_Persons_FN';

with 
#temp_tbl_Test_Persons_FN as
(
    select * from #tbl_Test_Persons tp 
    where tp.FIRSTNAME = '#first_name_input'
)
select * from #temp_tbl_Test_Persons_FN tp 
    where tp.LASTNAME = '#last_name_input';

set define off;


--------------sub-table, replace the entire with clause, does NOT work - 'Invalid value for DEFINE command.'!---
set define #;

define first_name_input = 'FN_2';
define last_name_input = 'LN_2';
define tbl_Test_Persons = 'Test_Persons';
define temp_tbl_Test_Persons_FN = 'Test_Persons_FN2';
define with_clause = '
with 
#temp_tbl_Test_Persons_FN as
(
    select * from #tbl_Test_Persons tp 
    where tp.FIRSTNAME = ''#first_name_input''
)
';

#with_clause
select * from #temp_tbl_Test_Persons_FN tp 
    where tp.LASTNAME = '#last_name_input';

set define off;

Upvotes: 1

Views: 207

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

From the documentation (for SQL*Plus but applies to SQL Developer too):

If the value of a defined variable extends over multiple lines (using the SQL*Plus command continuation character), SQL*Plus replaces each continuation character and carriage return with a space.

SQL Developer actually behaves slightly differently, and leaves the continuation character inside the string, which isn't helpful. But is also leaves the line breaks, so you can double-up the continuation character from - to --to stop it causing a problem, as that is then treated a comment; so you can do:

define with_clause = '--
with--
#temp_tbl_Test_Persons_FN as--
(--
    select * from #tbl_Test_Persons tp--
    where tp.FIRSTNAME = ''#first_name_input''--
)--
';

But there is a further complication, because #with_clause isn't recognised as a command - as you aren't yet in a command substitution doesn't happen. You can get around that, messily, with a subquery:

select * from (
#with_clause
select * from #temp_tbl_Test_Persons_FN tp 
    where tp.LASTNAME = '#last_name_input'
);

which isn't ideal. But if you do that, the script output window shows the substitution happening and you get a row back:

old:select * from (
#with_clause
select * from #temp_tbl_Test_Persons_FN tp 
    where tp.LASTNAME = '#last_name_input'
)
new:select * from (
--
with--
Test_Persons_FN2 as--
(--
    select * from Test_Persons tp--
    where tp.FIRSTNAME = 'FN_2'--
)--

select * from Test_Persons_FN2 tp 
    where tp.LASTNAME = 'LN_2'
)

  PERSONID LASTNAME                                                                                                                                                                                                                                                        FIRSTNAME                                                                                                                                                                                                                                                      
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         2 LN_2                                                                                                                                                                                                                                                            FN_2

(Also, interestingly, this still doesn't work in SQL*Plus if you use # as the substitution character - you get "ORA-24333: zero iteration count". It doesn't get that error with & though; I haven't tried any others. And you have to go back to a single concatenation character. But even with those changes it still doesn't substitute the with clause properly. Not sure why yet.)

Upvotes: 1

Related Questions