• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 719
  • Last Modified:

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

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)

CAN ANY ONE HELP?

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,

Thanks
0
tootootoo
Asked:
tootootoo
1 Solution
 
gljrCommented:
tootootoo,

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 :-)

George
0
 
iozturkCommented:
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.

0
 
walterecookCommented:
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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now