RE: before I re-invent the wheel

Andrew,
 
    Have you looked into dbms_sql package?  You can easily write a procedure 
that extracts the sql from your table, r=executes that sql and then use 
utl_file to write out the results.
 

Dick Goulet 
Senior Oracle DBA 

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Andrew Kerber
Sent: Thursday, January 13, 2011 9:02 AM
To: mcdonald.connor@xxxxxxxxx
Cc: ORACLE-L
Subject: Re: before I re-invent the wheel


I think that is the last step, or pretty close to it.? But I have to read the 
data from Oracle and write out those sqlplus files also.? I am sure I can do 
it, but was hoping somebody already has something similar.? The basics steps 
are (as I see them):

1. Read record from the table
2. Write out .sql file with the command.
3. Proceed to next record
4. After all .sql files are generated, run the .sql files 

I could probably run each .sql after generating it also, either method would 
work.


On Thu, Jan 13, 2011 at 3:02 AM, Connor McDonald <mcdonald.connor@xxxxxxxxx> 
wrote:




        On Thu, Jan 13, 2011 at 11:18 AM, Andrew Kerber 
<andrew.kerber@xxxxxxxxx> wrote:
        

                Before I write this shell script for myself, I thought I would 
ask if anyone has done this before.? I have a table of SQL queries (single 
queries) that have to be run against various schemas and instances.? The table 
contains all the information I need to run it, including the destination file 
for each query, I just have to write the shell script that will read the table, 
and run the query.? I suppose it will actually probably need to generate and 
call the individual sql files that will be run now that I think about it.? 
Anyway, has anyone written something like this and have a sample?? Oracle 
10.2.0.4, Solaris and OEL.? I could also run it on the client machine if 
necessary, which is running windows 7.
                
                -- 
                Andrew W. Kerber
                
                'If at first you dont succeed, dont take up skydiving.'
                


        Sorry, am I misinterpreting the question ? ?Isn't this just standard 
usage of SQL Plus parameters ?
        

        cat file | while read parm1 parm2 parm3 parm4 
        do
        ?? sqlplus blah/blah $parm1 $parm2 $parm3 $parm4
        done
        
        

        -- 
        Connor McDonald
        ===========================
        email: connor_mcdonald@xxxxxxxxx
        web:?? http://www.oracledba.co.uk
        
        "Semper in excremento, sole profundum qui variat"
        




-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Other related posts: