Oracle PLSQL Stored Procs in Unix Script

Point value changed by question poster.

Well here's a simple way depending on what you need to do.

Note that you may have to set up some environment variables before calling this line in the script:

sqlplus -s login/password @plsql_script.sql

inside plsql_script.sql call the stored procedure.

Note that the password will display on the command line if you do a ps -ef though. There is a way to hide it, if you need that let me know I'll dig it up for you.

Also will you need to capture output for use in the script?

Gary

Here's an example that uses 2 files, the shell script and a .sql file. Run showdbname and it will call sqlplus and get the default database name, then display it. Give it a try.

Gary

Shell script named showdbname:
===== start =========
getdbname()
{
name=`sqlplus -s login/password\
@getdbname.sql \
<

I actually have to detect certain activity on a table with in a specific time frame. If that activity has happend, I need to update the database and send an email. If you could pass along the info to not show the password/id, that would be great. I don't believe I need to capture any output.

Call me butter 'cause I'm on a roll!

Here's how to hide the password from being displayed via the ps -ef command. First a logon has to be set up as 'trusted' with connect only by the system admin. That's all I know about that I'm afraid, see your admin for more info. Anyway, the '/' on the sqlplus command line will auto login based on your unix ID. Inside the script, connect with the username and password.

Ok, I'm going home.

Gary

sqlplus -s / << EOF
connect login/password
@sqlplus_script.sql
EOF

If it's just the changes to a table you need to check for then a trigger rather than a unix script is the best option for you so that...

1 insert or update or delete on table (your choice of any one or more of these)
2 trigger fires and
2a updates other table or whatever you need to do
2b sends mail msg (see the sys.utl_smtp package - you may need permissions set by your DBA for this)
2c trigger ends

If it is necessary to be time based then set up a database job to run every x minutes/hours (be careful how you calculate the timing as if it's a long process then you will find that the next iteration will be out by the amount of time it takes the job to execute) to call the PL/SQL procedure/package (see the sys.dbms_job package andyour DBA again)

Using one of these options will be far more secure since it all runs inside the database so no password problems for you and its the most efficient way but it depends on the version of Oracle - needs 8i or higher.

This question was auto closed due to inactivity