?
Solved

Need help with Automating Procedure

Posted on 2006-04-03
10
Medium Priority
?
1,378 Views
Last Modified: 2012-05-05
Hello,
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?

Thx!
Ken
0
Comment
Question by:kencrest
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 7

Expert Comment

by:RainMan82
ID: 16363199
robotask.....

http://www.robotask.com/
0
 

Author Comment

by:kencrest
ID: 16363350
...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.

Ken
0
 
LVL 35

Accepted Solution

by:
johnsone earned 600 total points
ID: 16363444
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Assisted Solution

by:pbocanegra
pbocanegra earned 300 total points
ID: 16363943
Test with Oracle Job
in the field of next_date
write:
 SYSDATE+5/1440      <---- each 5 minutes.

For the initial command create a CRON (Unix)
or scheduled Task for WINDOWS
0
 
LVL 4

Expert Comment

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


Rgds,
HR
0
 
LVL 4

Assisted Solution

by:marper
marper earned 600 total points
ID: 16365083
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
consuming.

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


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:

shell.c
=======
#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/demo_rdbms.mk extproc_nocallback \
SHARED_LIBNAME=shell.so 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>/shell.so';
/


4. Create the PL/SQL wrapper procedure:


CREATE OR REPLACE PROCEDURE shell (cmd IN CHAR)
AS EXTERNAL
   NAME "sh"
   LIBRARY shell_lib
   LANGUAGE C
PARAMETERS (
    cmd STRING);
/


5. Call a shell script:


SQL> exec shell('sh myscript.sh');

PL/SQL procedure successfully completed.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16372236
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?
:)
0
 
LVL 35

Expert Comment

by:johnsone
ID: 16373235
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.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16373745
johnsone,

        second it.

Acton
0
 

Author Comment

by:kencrest
ID: 16420546
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...
Ken


BATCH SCRIPT:
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"

PAUSE


SQLPLUS SCRIPT:
host sqlldr system/password@clientdb control=load_metadata_image_rev2.ctl log=ImageMetaData.log
execute clientschema.pr_load_editorial
exit;

0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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…
Suggested Courses

840 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