Home » Other » Client Tools » Parametrizing entire sections of an sql script
Parametrizing entire sections of an sql script [message #259419] Wed, 15 August 2007 10:14 Go to next message
MG537
Messages: 2
Registered: August 2007
Location: Montreal, Québec
Junior Member
Hello,

I would like to know if there is a way to include multiple lines of sqlplus lines through the use of a CLOB variable read from a table. In the example below, could I define a CLOB variable in my script, read into that variable and then include it into the desired section of my script instead of hard-coding the section in red?

VARIABLE col_settings CLOB;
begin
select column_settings into :col_settings from COL_SET_TBL;
end;
/

What do I do next to include the text in my sql?

Here's an example of my script.

SET ECHO OFF
-- Other set stmts go here

SET MARKUP HTML ON SPOOL ON HEAD '<TITLE> Rapport EDC </TITLE>' -
BODY 'TEXT=black bgcolor=white align=left'-
TABLE 'align=center width=99% border=3 bordercolor=black bgcolor=white'

COLUMN TEST_NAME Format A18 HEADING "Table cible" JUSTIFY LEFT
COLUMN TEST_RSLT_STTS Format A02 HEADING "OK" JUSTIFY LEFT
COLUMN DE Format A10 HEADING "Execution" JUSTIFY LEFT
COLUMN TEST_RSLT_ATTR_01 Format 9999999999 HEADING "Nbr enreg" JUSTIFY LEFT


SELECT TEST_NAME, TEST_RSLT_STTS, to_char(TEST_RSLT_DATE_EXEC,'YYYY-MM-DD HH24:MI') DE, TEST_RSLT_ATTR_01
FROM SOME_TABLE
ORDER BY 1,2,3,4;

SET HEADING OFF
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON

exit;
Re: Parametrizing entire sections of an sql script [message #259422 is a reply to message #259419] Wed, 15 August 2007 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
AFAIK, no but alternatively you could spool it to a file & then
@new_spool_file
Re: Parametrizing entire sections of an sql script [message #259434 is a reply to message #259422] Wed, 15 August 2007 10:56 Go to previous messageGo to next message
MG537
Messages: 2
Registered: August 2007
Location: Montreal, Québec
Junior Member
OK thanks.
I will spool out the various parts into a new .sql file and then execute it.
Re: Parametrizing entire sections of an sql script [message #259503 is a reply to message #259434] Wed, 15 August 2007 14:37 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
create table tt (col1 varchar2(1000));

insert into tt values ('COLUMN TEST_NAME Format A18 HEADING "Table cible" JUSTIFY LEFT
COLUMN TEST_RSLT_STTS Format A02 HEADING "OK" JUSTIFY LEFT
COLUMN DE Format A10 HEADING "Execution" JUSTIFY LEFT
COLUMN TEST_RSLT_ATTR_01 Format 9999999999 HEADING "Nbr enreg" JUSTIFY LEFT');

set verify off
set heading off pagesize 0
set feedback off

column col1 new_val col1_new noprint
select col1 from tt;

prompt ### now you see it 1=1...
select '&col1_new' from dual where 1=1;

prompt ### now you dont 1=0...
select '&col1_new' from dual where 1=0;

prompt ### now see it using prompt...
prompt &col1_new


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production


Table dropped.


Table created.


1 row created.


### now you see it 1=1...
COLUMN TEST_NAME Format A18 HEADING "Table cible" JUSTIFY LEFT
COLUMN TEST_RSLT_STTS Format A02 HEADING "OK" JUSTIFY LEFT
COLUMN DE Format A10 HEADING "Execution" JUSTIFY LEFT
COLUMN TEST_RSLT_ATTR_01 Format 9999999999 HEADING "Nbr enreg" JUSTIFY LEFT

### now you dont 1=0...
### now see it using prompt...
COLUMN TEST_NAME Format A18 HEADING "Table cible" JUSTIFY LEFT
COLUMN TEST_RSLT_STTS Format A02 HEADING "OK" JUSTIFY LEFT
COLUMN DE Format A10 HEADING "Execution" JUSTIFY LEFT
COLUMN TEST_RSLT_ATTR_01 Format 9999999999 HEADING "Nbr enreg" JUSTIFY LEFT
SQL> 


Previous Topic: output formatting
Next Topic: questions in using Toad
Goto Forum:
  


Current Time: Fri Mar 29 16:50:58 CDT 2024