Parametrizing entire sections of an sql script [message #259419] |
Wed, 15 August 2007 10:14 |
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 #259503 is a reply to message #259434] |
Wed, 15 August 2007 14:37 |
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>
|
|
|