OraFAQ Forum: Unix » automating a script

Home » Infrastructure » Unix » automating a script (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

automating a script [message #622497]

Wed, 27 August 2014 03:05

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

Report message to a moderator

Re: automating a script [message #622498 is a reply to message #622497]

Wed, 27 August 2014 03:21

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Code: [Select all] [Show/ hide]

BEGIN 
    FOR i IN(SELECT sql_id 
             FROM   V$SQL_PLAN) LOOP              
        dbms_output.Put_line(i.sql_id); 
    END LOOP; 
END;
/

Regards,
Lalit

Report message to a moderator

Re: automating a script [message #622499 is a reply to message #622498]

Wed, 27 August 2014 03:22

Lalit Kumar B
Messages: 3174
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/

Report message to a moderator

Re: automating a script [message #622507 is a reply to message #622499]

Wed, 27 August 2014 04:06

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

Code: [Select all] [Show/ hide]

SELECT sql_id FROM  V$SQL_PLAN

to file and then do something like below

Code: [Select all] [Show/ hide]

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

thats the reason i posted it in unix

Report message to a moderator

Re: automating a script [message #622508 is a reply to message #622507]

Wed, 27 August 2014 04:07

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: automating a script [message #622509 is a reply to message #622508]

Wed, 27 August 2014 04:12

farooqomar57
Messages: 3
Registered: August 2014

Junior Member

dear michel,i have already specified in the first post

Report message to a moderator

Re: automating a script [message #622510 is a reply to message #622509]

Wed, 27 August 2014 04:13

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: automating a script [message #622514 is a reply to message #622507]

Wed, 27 August 2014 05:21

Lalit Kumar B
Messages: 3174
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
Code: [Select all] [Show/ hide]
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.

Report message to a moderator

Previous Topic:	syntax error near unexpected token
Next Topic:	Unable to initiate crontab Job

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 03:02:53 CDT 2025