Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-11-28
4
Medium Priority
?
3,075 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 400 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

824 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