Solved

PLSQL/Job Scheduler execute SQL script file on system drive... possible?

Posted on 2007-11-28
4
3,042 Views
Last Modified: 2013-12-19
I have a .SQL text file with basic SQL commands in it that I need executed by oracle.  I know there are tons of better ways to do this, but for now I just want oracle to execute the script.  In the past, I would have a job scheduler launch a bat file that would call SQLPlus passing the SQL file to launch, along with the instance, username & password.  How can I do this within the oracle job scheduler?  Thanks.
0
Comment
Question by:CalDude
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 100 total points
ID: 20368200
AFAIK, the oracle job scheduler would only execute SPs or direct DDLs. So if you could convert ur .SQL file into a DB stored procedure, then u can create a DBMS job for it and schedule it as periodically as u wish to.
0
 
LVL 1

Expert Comment

by:michaeljoneill
ID: 20369632
Every modern operating system has adequate job scheduling functionality (i.e., cron).  Why not use it?
0
 

Expert Comment

by:timhoustontx
ID: 22735827
no body answers the question?

it's related to a question we've been trying to answer.

Can we do something equivalent to this?

create or delete
procedure runScript as
begin
   @ script.sql
end;

so far, i haven't found anything that explains how to do this.
0
 
LVL 1

Expert Comment

by:michaeljoneill
ID: 22736482
If I answer the question and you don't like the answer are you going to be satisfied?

#1 "create or delete" is nonsense syntax

#2 In SAT-speak: scripts are to operating systems as stored procedures are to databases

#3 Assuming the script file contained only SQL and/or PL/SQL and had no SQL*Plus-specific syntax, you could write the trivial stored procedure code that opens access the script file and parses the content, takes the parsed content and then executes via dynamic SQL.

-- psuedo-code
procedure runScript(scriptPath in varchar2) as
begin
   -- use UTL_FILE to access file
   -- parse the content however you see fit
   execute immediate(theParsedContent);
end;

There are so many good reasons not to ad hoc run code that's flopping around in the file system - which is what you want to do.  It's the most gigantic epic SQL-injection security whole you could imagine.

Good luck.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now