Call UNIX C-Shell Scripts from Oracle sqlplus/stored procedures(!!)

Posted on 2003-03-10
Medium Priority
Last Modified: 2008-01-09
Hi Good person,

I have an Oracle DB, that's heavily relying on c-shell scripts, and we need to have a DIRECT link between Oracle scripts and the c-shell scripts. In other words, we want to run any of our c-shell scripts at any time from the Oracle DB (throught stored procedures, or preferrably, embedded SQL code in PowerBuilder)


Any word regarding

1- Easiest way to call the scripts
2- minimum login requirements(one id/one password for both DB & application)Without using OS Authentication method in Oracle
3- How to call SQL loader from Oracle SQL (Without having to go to OS)

By the way:

Application scripts are on machine A
DB Server is on machine B
Client invoking scripts will be from a PC(Powerbuilder application)

Highly appreciate it,

Question by:tootootoo
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

gljr earned 200 total points
ID: 8105136

on the surface this is a simple question, but depending on your requirements and the answer you choose the security implications can be enormous.

if you're using Oracle8i or later, the simple answer is to write a JAVA wrapper procedure (Tom Kyte's book: "expert one on one: Oracle" includes a code snippet,) then you can call that procedure as you would any other PLSQL procedure.  Its really quite simple.  The security on this is that you must grant access to individual UNIX commands (or c-shell scripts).. and those commands must be able to execute as the UNIX use who starts your database.

In my opinion, JAVA is THE answer if you have a finite number of pre-identified commands you'd like to run.  Even though a large number of commands might entail significant maintenance on your part.

If you're more inclined to open up the database to any UNIX command, or ..?  you'll generally want a PRO*C program.  With Oracle9i you can write an "External Procedure" that gets linked into the database so you can call it directly from any PLSQL procedure.  Or with prior versions of Oracle you can write a daemon process to listen for and execute command requests.   The difficulty here is that of deciding who gets to run which programs and making sure nobody deletes your database or anything else important.  External procedures can run as any UNIX user you choose which helps, but there's no inherent security.

So, if this didn't answer your question, tell me a little more about your problem and I'll go into more detail - I've done this a few times :-)


Expert Comment

ID: 8106515
Pls clearify your question. What kind of scripts? What is your Oracle version? What do you mean by without having to go OS to use SQLLoader? etc.

LVL 17

Expert Comment

ID: 10337147
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: gljr {http:#8105136}

Please leave any comments here within the next four days.

EE Cleanup Volunteer

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

752 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