Solved

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

Posted on 2007-11-28
4
3,066 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

630 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