OraFAQ Forum: SQL & PL/SQL » Execute a sql script depending on number of records in a table

Home » SQL & PL/SQL » SQL & PL/SQL » Execute a sql script depending on number of records in a table (11.2.0.4 on Linux)

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

Execute a sql script depending on number of records in a table [message #639006]

Mon, 29 June 2015 08:22

orausern
Messages: 826
Registered: December 2005

Senior Member

Hi,

I have a requirement to execute a sql script as many no. of times as the number of records in a table. Is it something that can be accomplieshed? Lets say that there is a script like the below and it has to be executed as many times as the nubmer for rows in a table like emp (it may have 10 records or 12 ...that keeps varying.

select 1 from dual;

This is a simplified version of an issue I am having. If this can be done, it will help me to solve the actual issue of which this is an example.

Thanks,
OrauserN

Report message to a moderator

Re: Execute a sql script depending on number of records in a table [message #639007 is a reply to message #639006]

Mon, 29 June 2015 08:25

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I would use the Scheduler. Create an array of lightweight jobs, each executing the same script, and use te number of rows to determine te size of the array.

Report message to a moderator

Re: Execute a sql script depending on number of records in a table [message #639008 is a reply to message #639006]

Mon, 29 June 2015 08:44

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can also generate a call of the script for each row into a spool and then execute the spool file:

set termout off echo off verify off lines 1000 pages 0 feedback off
spool exec.sql
select '@myscript' from emp;
spool off
@exec

Report message to a moderator

Re: Execute a sql script depending on number of records in a table [message #639009 is a reply to message #639008]

Mon, 29 June 2015 08:46

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Ah, yes. My solution is perhaps a little over-engineered Sad

Report message to a moderator

Re: Execute a sql script depending on number of records in a table [message #639010 is a reply to message #639009]

Mon, 29 June 2015 08:48

orausern
Messages: 826
Registered: December 2005

Senior Member

Thank you John and Michel!

Is there some other way like we capture the count of the table in a variable and make the script exit when the count value is reached?

Thanks again.

Report message to a moderator

Re: Execute a sql script depending on number of records in a table [message #639012 is a reply to message #639010]

Mon, 29 June 2015 11:02

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

My way generates the EXACT number of calls, just add "prompt exit" before "spool off" and then you will exit after the exact number of calls you want.

Report message to a moderator

Re: Execute a sql script depending on number of records in a table [message #639013 is a reply to message #639012]

Mon, 29 June 2015 11:07

orausern
Messages: 826
Registered: December 2005

Senior Member

Got it! Thank you!!!

Report message to a moderator

Re: Execute a sql script depending on number of records in a table [message #639014 is a reply to message #639013]

Mon, 29 June 2015 11:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

row by row is slow by slow

Report message to a moderator

Re: Execute a sql script depending on number of records in a table [message #639015 is a reply to message #639014]

Mon, 29 June 2015 11:12

orausern
Messages: 826
Registered: December 2005

Senior Member

Very true but i have a typical situation. Not something that is sql and pl/sql stuff alone but something ...but I got the answer I was looking for and thanks for pointing out BlackSwan.

[Updated on: Mon, 29 June 2015 11:13]

Report message to a moderator

Previous Topic:	Oracle Table last access detail
Next Topic:	Trigger on table to update other table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 12:39:15 CDT 2024