General discussion

Locked

Oracle PLSQL Stored Procs in Unix Script

By Doris.K.Sanocki ·
I need to execute an Oracle PL/SQL stored procedure from within a Unix shell script. How does one go about this in Unix for Oracle? I've done this using a Sybase stored procedure but Unix for Oracle seems to be different. ANY HELP WOULD BE APPRECIATED!!

This conversation is currently closed to new comments.

12 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Oracle PLSQL Stored Procs in Unix Script

by Doris.K.Sanocki In reply to Oracle PLSQL Stored Procs ...

Point value changed by question poster.

Collapse -

Oracle PLSQL Stored Procs in Unix Script

by Gary_W In reply to Oracle PLSQL Stored Procs ...

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

Collapse -

Oracle PLSQL Stored Procs in Unix Script

by Doris.K.Sanocki In reply to Oracle PLSQL Stored Procs ...

The question was auto-closed by TechRepublic

Collapse -

Oracle PLSQL Stored Procs in Unix Script

by Gary_W In reply to Oracle PLSQL Stored Procs ...

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 \
<<EOF|grep [A-Z*]
exit
EOF
`
return $name
}
getdbname
echo "Database name is $name"
====== End ========

pl/sql script:
==== Start ======
set heading off;
select name
from v$database;
exit;
====== End =======

Collapse -

Oracle PLSQL Stored Procs in Unix Script

by Gary_W In reply to Oracle PLSQL Stored Procs ...

I forgot to mention, name the .sql file getdbname.sql. This example will call a procedure and capture the output in the script.

Collapse -

Oracle PLSQL Stored Procs in Unix Script

by Doris.K.Sanocki In reply to Oracle PLSQL Stored Procs ...

The question was auto-closed by TechRepublic

Collapse -

Oracle PLSQL Stored Procs in Unix Script

by Doris.K.Sanocki In reply to Oracle PLSQL Stored Procs ...

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.

Collapse -

Oracle PLSQL Stored Procs in Unix Script

by Gary_W In reply to Oracle PLSQL Stored Procs ...

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

Collapse -

Oracle PLSQL Stored Procs in Unix Script

by Doris.K.Sanocki In reply to Oracle PLSQL Stored Procs ...

The question was auto-closed by TechRepublic

Collapse -

Oracle PLSQL Stored Procs in Unix Script

by marv732 In reply to Oracle PLSQL Stored Procs ...

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.

Back to Linux Forum
12 total posts (Page 1 of 2)   01 | 02   Next

Operating Systems Forums