Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

Shell Commands From PL/SQL - Use this simple method to perform shell commands from within PL/SQL.



Raj Srivastva said...

Great piece of code.

I could get it to run on Windows. But when I tried on a unix server I get the following error:

SQL> BEGIN Host_Command (p_command => 'touch /tmp/test1.txt'); END;
2 /
Process err :/bin/sh: touch: not found

PL/SQL proced

Raman said...

Hi,

It used to work excellent till a month ago ....

Now I am getting this error, any idea??

11:20:02 DB:gd9i/USER:gisteam> execute sde_registration('PARKS_REG1','SHAPE','NM','a');
The handle is invalid.
The handle is invalid.
The handle is invalid.
The handle is invalid.
The handle is invalid.
The handle is invalid.

PL/SQL procedure successfully completed.

thanks

Tim... said...

Open a question on the forum under the development section:

http://www.oracle-base.com/forums/viewforum.php?f=2

We'll discuss it there. I could do with knowing what exactly you are passing to the host command and what has changed in your system recently, like patches/upgrades etc.

Cheers

Tim...

Dharmendra said...

Excellent document and keep the good work .

Neil said...

Works like a bought one

Kiran Kodithala said...

Tim,

This is perfect code, it works great.

I have one question, I dont know if it is possible to do it or not. Apparently the user that executes the command on the OS seems to be ORACLE user itself. Is there anyway to change the user who runs the code to a different user? may be setup a shell script to do su ? or are there any other preferred ways.

Thanks for everything you do. You opened a whole new dimension for me.

Tim... said...

Sorry, but the answer is no. It's "oracle" or nothing.

Cheers

Tim...
PS. Please ask questions on the forum :)

Kiran Kodithala said...

Tim,

Thanks for the response.

I have been trying to use su to switch user and I am having problem sending the prompt values (from within the shell script). I used the redirect symbol which works grand when passing prompt values for calling sqlplus from shell, but from shell to shell it doesnt.

Here's my excerpt
su user1 <userpass
eof

I put it in a shell script and executed the shell from command prompt with no avail.

Remi said...

You should solve the zombie problem by waiting for the process you created to execute the command. Just add...
pr.waitFor()
...near the end of the method.

Tim... said...

Hi.

I've added that modification in the article.

Cheers

Tim...

Jim - Torrance,CA said...

Hi Tim

I am trying to incorporate your pl/sql into a 9i database, but i want to use a function call to retrieve the data output by the OS command I am using. Can you explain if this is possible and how to go about it.

Thanks

Jim - Torrance, CA said...

Sorry Tim

I entered question on forum per your advice

da...........

Sanjiv said...

Granting permission to all file is Ok.In this sase we can use th copy command.

DBMS_JAVA.grant_permission('SCOTT', 'java.io.FilePermission', '<>', 'read ,write, execute, delete');

But can we grant permission to a particular file under the folder and then use the copy command.Its not working, Can anybidy help me here

ankit said...

excellent

Pallavi said...

Excellant desription but whenever i am trying to run this using Toad Its is going to infine loop and doesnot come out.Suggestions will be Welcome.

Tim... said...

Hi.

I'm not a TOAD user. Does it work for you from SQL*Plus? If so, then it is a problem with TOAD.

Cheers

Tim...

DO NOT ask technical questions here, that's what my forum is for!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

Add your comments here.
Name
Comment
(max 400 chars - plain text)