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

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.
CalDudeAsked:
Who is Participating?
 
Jinesh KamdarConnect With a Mentor Commented:
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
 
michaeljoneillCommented:
Every modern operating system has adequate job scheduling functionality (i.e., cron).  Why not use it?
0
 
timhoustontxCommented:
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
 
michaeljoneillCommented:
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
All Courses

From novice to tech pro — start learning today.