Home » SQL & PL/SQL » Client Tools » How to automate the scripts
How to automate the scripts [message #567241] Tue, 25 September 2012 09:46 Go to next message
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 #567242 is a reply to message #567241] Tue, 25 September 2012 09:54 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
>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 is a SQL*plus command & is at best an inelegant "solution" for any Production environment.
Why don't you just directly invoke the procedures?
Re: How to automate the scripts [message #567243 is a reply to message #567242] Tue, 25 September 2012 10:05 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
Hello,

The procedures are there but the parameters are dynamic, so how you think is best to handle then, as a single proc is run say 5 times with diff parameters every time.

Thanks,
S
Re: How to automate the scripts [message #567244 is a reply to message #567243] Tue, 25 September 2012 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the parameters come from?

Regards
Michel
Re: How to automate the scripts [message #567246 is a reply to message #567244] Tue, 25 September 2012 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
>as a single proc is run say 5 times with diff parameters every time.
How many different combination of parameters exist?
Re: How to automate the scripts [message #567248 is a reply to message #567244] Tue, 25 September 2012 10:16 Go to previous messageGo to next message
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 #567249 is a reply to message #567248] Tue, 25 September 2012 11:18 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
Above is a "elegant" & flexible "design that results in non-scalable performance and a challenging Quality Assurance nightmare.

It can be made to "work", for some loose definition of "work", by (ab)using EXECUTE IMMEDIATE.
Re: How to automate the scripts [message #567250 is a reply to message #567248] Tue, 25 September 2012 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: How to automate the scripts [message #567251 is a reply to message #567250] Tue, 25 September 2012 11:48 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
I see the EXECUTE IMMEDIATE thing, need to polish it a bit now, as you said it will definitely be a QA nightmare. But this spool thing, can we add all this into the proc script ? So that a well formatted script is written into a file, ready to be executed through a UNIX job or something.

Thanks,
S
Re: How to automate the scripts [message #567262 is a reply to message #567251] Tue, 25 September 2012 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The SQL script I provided is a stand-alone one and can be executed From a Unix shell script that you can put in crontab.

Regards
Michel

[Updated on: Tue, 25 September 2012 12:42]

Report message to a moderator

Re: How to automate the scripts [message #567272 is a reply to message #567262] Tue, 25 September 2012 13:25 Go to previous message
sulabhagra
Messages: 57
Registered: April 2012
Member
Ok, thanks Michel, will speak to the DBA if they are confortable to set it up for us. Thanks again.
Previous Topic: how can i edit the timeout for each user
Next Topic: PL/SQL Developer ORA-12514:TNS:listner doesnot Problem
Goto Forum:
  


Current Time: Fri Oct 31 18:53:14 CDT 2014

Total time taken to generate the page: 0.08361 seconds