Home » Other » Client Tools » How to automate the scripts
| How to automate the scripts [message #567241] |
Tue, 25 September 2012 09:46  |
 |
sulabhagra
Messages: 57
Registered: April 2012
|
Member |
|
|
Hello,
I have a proc created which dynamically creates scripts to be executed, e.g. using DBMS_OUTPUT.PUT_LINE it creates the following scripts to be executed:
EXEC D_Q_TST1('PARAM1','PARAM2','PARAM3');
EXEC D_Q_TST2('PARAM1','PARAM2','PARAM3');
EXEC D_Q_TST3('PARAM1','PARAM2','PARAM3');
EXEC D_Q_TST4('PARAM1','PARAM2','PARAM3');
EXEC D_Q_TST5('PARAM1','PARAM2','PARAM3','PARAM4','PARAM5');
Now, what I am really looking for is to explore options where we can spool the results into a file and run another proc to execute all of these proc through it.
Any help on it will be highly appreciated, and let me know if my request is not clear.
Thanks,
S
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to automate the scripts [message #567248 is a reply to message #567244] |
Tue, 25 September 2012 10:16  |
|
sulabhagra
Messages: 57
Registered: April 2012
|
Member |
|
|
There is a script which picks them from the tables created for them. there was a need to choose EXEC only because the source tables to be referenced were also dynamic.
SELECT A.SEQ_ID,
A.UNIQUE_ID,
a.SOURCE_SYSTEM,
a.FIELD,
a.RULE,
a.PARAMETER1,
a.PARAMETER2,
a.PARAMETER3,
a.PARAMETER4,
a.PARAMETER5,
a.PARAMETER6,
a.PARAMETER7,
b.rule_name
FROM MAPPED_RULES a
inner join
RULE_DESC_TABLE b
on a.rule = b.rule
where a.rule > 100
order by source_system
and here a.SOURCE_SYSTEM is the Table Name which is populated using the following script
DBMS_OUTPUT.PUT_LINE('EXEC ' || REC01.rule_name || '( '''|| REC01.unique_id||''' ,'||
REC01.seq_id||' ,'''|| REC01.source_system||''', '''||rec01.field||''', '''||
REC01.parameter1||''', '''|| REC01.parameter2||''', '''|| REC01.parameter3||''', '''||
REC01.parameter4||''', '''|| REC01.parameter5||''', '''|| REC01.parameter6||''');');
All the diff combinations are mapped into the table and based on the qry above, the script creates all the proc to be executed.
Thanks,
S
[Updated on: Tue, 25 September 2012 11:18] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: How to automate the scripts [message #567250 is a reply to message #567248] |
Tue, 25 September 2012 11:20 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Set heading off
Set feedback off
Set pagesize 0
Set termout off
Set trimout on
Set trimspool on
Set recsep off
Set linesize 100
Set echo off
spool tmp.sql
Select 'exec '||REC01.rule_name || '( '''|| REC01.unique_id||''' ,'||
REC01.seq_id||' ,'''|| REC01.source_system||''', '''||rec01.field||''', '''||
REC01.parameter1||''', '''|| REC01.parameter2||''', '''|| REC01.parameter3||''', '''||
REC01.parameter4||''', '''|| REC01.parameter5||''', '''|| REC01.parameter6||''');'
FROM MAPPED_RULES a
inner join
RULE_DESC_TABLE b
on a.rule = b.rule
where a.rule > 100
order by source_system
/
spool off
Set termout on
Set echo on
@tmp.sql
Regards
Michel
[Updated on: Tue, 25 September 2012 11:21] Report message to a moderator |
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Feb 05 23:51:11 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
