Need help with Automating Procedure

Posted on 2006-04-03
Last Modified: 2012-05-05
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?

Question by:kencrest
    LVL 7

    Expert Comment


    Author Comment

    ...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.

    LVL 34

    Accepted Solution

    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.
    LVL 4

    Assisted Solution

    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
    LVL 4

    Expert Comment

    Are you working on unix .. ?
    If yes, I have a script that I use daily and can share with you.

    LVL 4

    Assisted Solution

    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.
    LVL 19

    Expert Comment

    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?
    LVL 34

    Expert Comment

    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.
    LVL 19

    Expert Comment


            second it.


    Author Comment

    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


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now