Home » Infrastructure » Unix » automating a script (11g)
automating a script [message #622497] Wed, 27 August 2014 03:05 Go to next message
farooqomar57
Messages: 3
Registered: August 2014
Junior Member
I have a .sql script which takes two input parameters

DEF input_license = '&1';

DEF sql_id = '&2';

input license will always be Y
and i get the sqlid from the below query

select sql_id from v$sql_plan

say if i have 50 sqlids i have to input every id's manually ,so im thinking to automate this that way i can avoid inputting values manually.any thoughts how can this be done
Re: automating a script [message #622498 is a reply to message #622497] Wed, 27 August 2014 03:21 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
BEGIN 
    FOR i IN(SELECT sql_id 
             FROM   V$SQL_PLAN) LOOP              
        dbms_output.Put_line(i.sql_id); 
    END LOOP; 
END;
/


Regards,
Lalit
Re: automating a script [message #622499 is a reply to message #622498] Wed, 27 August 2014 03:22 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
Forgot the moderation bit,

Welcome to the forum!

It is your first post, in future,
Please read and follow the forum guidelines, to enable us to help you: http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: automating a script [message #622507 is a reply to message #622499] Wed, 27 August 2014 04:06 Go to previous messageGo to next message
farooqomar57
Messages: 3
Registered: August 2014
Junior Member
thanks ,i already tried using loops that worked,but i wanted to do it unix ie. spool the output of
SELECT sql_id FROM  V$SQL_PLAN
to file and then do something like below

v1='cat /spool1.lst| tail -1| awk '{print $1}''; export v1 


thats the reason i posted it in unix
Re: automating a script [message #622508 is a reply to message #622507] Wed, 27 August 2014 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And so what is the problem?
Clarify and specify it in details.

Re: automating a script [message #622509 is a reply to message #622508] Wed, 27 August 2014 04:12 Go to previous messageGo to next message
farooqomar57
Messages: 3
Registered: August 2014
Junior Member
dear michel,i have already specified in the first post
Re: automating a script [message #622510 is a reply to message #622509] Wed, 27 August 2014 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If it was clear I'd not ask for it.
So try again.

Re: automating a script [message #622514 is a reply to message #622507] Wed, 27 August 2014 05:21 Go to previous message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
farooqomar57 wrote on Wed, 27 August 2014 14:36
but i wanted to do it unix ie. spool the output of
SELECT sql_id FROM  V$SQL_PLAN
to file


Just use SPOOL in a SQL*plus session. In your shell script open a sqlplus session, SPOOL location:\filename, your query and finally spool off.
Previous Topic: syntax error near unexpected token
Next Topic: Unable to initiate crontab Job
Goto Forum:
  


Current Time: Fri May 29 16:11:06 CDT 2020