Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

715 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