Linux

General discussion

Oracle PLSQL Stored Procs in Unix Script

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

Oracle PLSQL Stored Procs in Unix Script

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.

Oracle PLSQL Stored Procs in Unix Script

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.

Oracle PLSQL Stored Procs in Unix Script

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 and2a updates other table or whatever you need to do2b 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.

Create a new discussion

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

Post type

Subject title

Topic Tags

Select up to 3 tags (1 tag required)

Cloud

Piracy

Security

Apple

Microsoft

IT Employment

Google

Open Source

Mobility

Social Enterprise

Community

Smartphones

Operating Systems

Windows

Mac

Malware

Tablets

Networking

Browser

Hardware

Software

Web Developerment

Linux

Off Topic

Message Body

Track this discussion and email me when there are updates

Please note: Do not post advertisements, offensive material, profanity, or personal attacks. Please remember to be considerate of other members. If you're new to the TechRepublic Forums, please read our TechRepublic Forums FAQ. All submitted content is subject to our Terms Of Use.