Need help with Automating Procedure

I'm a bit of a PL/SQL newbie. I'd like to receive some direction on how to automate some tasks that happen against my Oracle Database. Here are the tasks that I currently perform manually.

1) At the command line, I invoke sqlldr to load a text file into a staging table in Oracle. The syntax is as follows...

'sqlldr system/password@dbinstance control=load_metadata.ctl   log=metadata.log'
The ctl control file sits in the same folder along with the text file that contains all the data.

2) After I execute this at the command prompt, I then go into SQLPLUS and run the following stored procedure which modifies and moves my staging data into my production table. I log into the dbinstance and then type...

'exec schema.procedure_metadata'

3) The control file that I run in sqlldr in Step 1 looks for the same 'named' text file everytime it is invoked which is ok. I'd like automate this process where every 5 minutes a "Hot Folder" is polled looking for this named text file, processes it thru sqlldr, executes the stored procedure, and then deletes the text file after processing. Then waits another 5 minutes for a another new text file to be dropped into the hot folder.

What's the best way to approach automating all of this? Any thoughts?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kencrestAuthor Commented:
...I'd rather not have to purchase a utility to do this. I thought there was a way to automate these tasks within Oracle itself.

johnsoneSenior Oracle DBACommented:
Not within Oracle.

Oracle could handle running a procedure with DBMS_JOB every 5 mintues.  There are caveats to DBMS_JOB and it may not run exactly every five minutes.

If you could write a Java stored procedure to do all the tasks then it could be done in Oracle.  However, running system commands from a stored procedure opens some security risks.  The commands run as the instance owner, and in theory someone could delete all your data files through a procedure.  Not a good idea.

It can certainly be done with a script.  Then that script can be automated.

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Test with Oracle Job
in the field of next_date
 SYSDATE+5/1440      <---- each 5 minutes.

For the initial command create a CRON (Unix)
or scheduled Task for WINDOWS
Are you working on unix .. ?
If yes, I have a script that I use daily and can share with you.

Many tasks have been automated using shell scripting languages. Frequently,
these shell scripts need to be called from an Oracle stored procedure, but no
direct interface to call shell scripts is provided. Oracle does, however,
provide a method for calling C and Java programs via external procedures. Since
many shell programmers have never programmed in C or Java, converting shell
scripts or writing new tasks in either of these languages is difficult and time

This solution provides a C routine that will make OS calls, and these calls can
be commands, shell scripts, or other application programs. Using this method,
no C programming experience is required, and allows existing and new tasks
written as shell scripts to be run from within the database on the server

Note: This note assumes you have configured the extproc listener and are setup
to run external procedures. Also note that the external procedure agent will
only be able to load SO/SL files from $ORACLE_HOME/lib on UNIX operating
systems and DLLs from $ORACLE_HOME\bin on Windows. Placing the DLL in any
other location will require the parameter EXTPROC_DLLS to be set within the
listener.ora file.

Also, since the external procedure is written in C, you will need a C compiler.

From the Oracle server machine:

1. Create a file for the external procedure code:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

void sh(char *);

void sh( char  *cmd )
        int num;

        num = system(cmd);

2. Compile and link the C code into a shared library:

make -f $ORACLE_HOME/rdbms/demo/ extproc_nocallback \ OBJS=shell.o

Log into SQL*Plus to perform the remaining steps.

3. Define the shared library in Oracle:

CREATE LIBRARY shell_lib IS '<full path to shared lib>/';

4. Create the PL/SQL wrapper procedure:

   NAME "sh"
   LIBRARY shell_lib
    cmd STRING);

5. Call a shell script:

SQL> exec shell('sh');

PL/SQL procedure successfully completed.
if in the unix (even in the windows), shell script plus sql script seem to have the ability to do thoese steps, don't they?
johnsoneSenior Oracle DBACommented:
It can definitely be done in a single shell script in UNIX.

I cannot speak to Windows, that is not my area of expertise, but I would assume it could be done in a script or two in Windows as well.

        second it.

kencrestAuthor Commented:
Hi all...
Just want to say thanks for all the terrific comments and feedback. Some of the suggestions were a bit over my head. But what I wound up doing was writing a .bat (batch) script in Windows that had an IF ELSE statement that checked for a file in a folder. I'm using Windows 'Task Scheduler' to run the script every 10 min. If the batch script finds the named file in the folder it launches SQLPLUS with database credentials and launches a sqlplus script @script.

Inside the .sql SQLPLUS script I execute the SQLLDR command using the HOST command within SQLPLUS. After that I run the stored procedure. The tasks are then complete.

I know it seems a bit cheesy but it works just fine.

Here's the code that I wrote to make this happen...

Thx again guys...

IF EXIST "E:\CM MetaData Exports\HOT FOLDER - ImageMetaData\ImageMetaData.txt" (sqlplus system/password@clientdb @vs-sqlbatch-image.sql) ELSE EXIT

Del "E:\CM MetaData Exports\HOT FOLDER - ImageMetaData\ImageMetaData.txt"


host sqlldr system/password@clientdb control=load_metadata_image_rev2.ctl log=ImageMetaData.log
execute clientschema.pr_load_editorial

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.