SQL & PL/SQL

You'll need a wrapper function/procedure.
JAVA can do that, and you can call that from PL/SQL.
See:
http://www.oracle-base.com/articles/8i/ShellCommandsFromPLSQL.php
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:952229840241

Run the UNIX commands with a ! prefix.

For example:

SQL>
SQL> !date
Tue Apr 13 17:13:04 EDT 2010

SQL>

Is that what you wanted?

Hi,
you can use DBMS_PIPE to achieve this

CREATE OR REPLACE FUNCTION exec_host_command( lc_cmd IN VARCHAR2 )
RETURN INTEGER IS
ln_status NUMBER;
lc_errormsg VARCHAR2(80);
lc_pipe_name VARCHAR2(30);
BEGIN
lc_pipe_name := ‘HOST_PIPE’;
dbms_pipe.pack_message( lc_cmd );
ln_status := dbms_pipe.send_message(lc_pipe_name);
RETURN ln_status;
END;
/

Alternatively you can write a java stored procedure to do the same

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Cmd" AS
--==========================================================--
-- Class Cmd
--==========================================================--

import java.io.BufferedReader;
import java.io.InputStreamReader;

public class Cmd {
/**
*Static method to invoke host system commands
*input variable String Command
*Returns int. 0 for success and any other number
*for failure of the command
*/
public static int executeCommand(String s_command) {
try {
/*Get the runtime shell and execute the system command
*Wait till the command is executed
*/
final Process pr = Runtime.getRuntime().exec(s_command);
pr.waitFor();
return pr.exitValue();
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
return -1;
}
}/*End of function executeCommand*/
};
/
SHOW ERRORS
/
EXIT;

Grant the permissions
--Grant Host File permissions to the APPS schema
DBMS_JAVA.grant_permission ('<USER>', 'java.io.FilePermission',
'<<ALL FILES>>', 'read ,write, execute, delete');

--Grant Runtime Write permissions to the APPS schema
DBMS_JAVA.grant_permission ('<USER>', 'SYS:java.lang.RuntimePermission',
'writeFileDescriptor', '');

--Grant Runtime Read permissions to the APPS schema
DBMS_JAVA.grant_permission ('<USER>', 'SYS:java.lang.RuntimePermission',
'readFileDescriptor', '');


and create a wrapper plsql call
FUNCTION Cmd (p_command IN VARCHAR2)
RETURN NUMBER
AS LANGUAGE JAVA
NAME 'Cmd.executeCommand (java.lang.String) return int';

Edited by: navsriva on Apr 13, 2010 3:21 PM

You can also use dbms_scheduler to run an OS command. Note that the command will be run by the scheduler. You can use the run_job procedure to run it synchronously.

Edited by: thtsang on Apr 14, 2010 1:19 PM

http://asktom.oracle.com/pls/asktom/f?p=100:11:8892251278807846::::P11_QUESTION_ID:952229840241

No, pls i dont need any window related task i was asking running unix command like copying file names(*.dat) into a specific file(test.txt) using plsql. Any help? i tried oscommand_run and got SYS executed by permissions but my code isnt doing the job......any help?

Edited by: koloo on Apr 15, 2010 12:25 PM

Please re-read my question.

Is there anything wrong with the 2 links I gave you in my first reply to you?

I tried this code as below, it returned 1 but never executed my script:

Set Serverout On
Declare
x Varchar2(2000);
Begin
x := Cmd('/test/exectest.sh');
DBMS_OUTPUT.Put_Line(x);
End;
/

I called it as below but in never worked :

Set Serverout On
Declare
x Varchar2(2000);
Begin

host_command (p_command => '$HOME/ls *.* > /spnldr/test/t1.txt');

DBMS_OUTPUT.Put_Line(x);
End;
/

Edited by: koloo on Apr 15, 2010 12:29 PM

depend on what command you want to run.
If you want to copy, why don't you just use utl_file.fcopy ('SOURCE_DIR','SOURCE_NAME','DEST_DIR','DEST_NAME');
this is PL/SQL command.

thanks

Sorry for not being specific but what iam trying to do is this: in a specific directory eg /UITools/*.dat i want to copy all the names of .dat files into test.txt available in the same location. utl_file cannot do that....pls help if you can.

Well, both links in my first reply to you contain copy-paste examples enabling you to execute any OS-command you want to from PL/SQL.

No, and that's why I suggested you to use a little JAVA here, create a JAVA routine, and a simple PL/SQL routine.
Did you check/read them? And: if you did, and you believe those examples are not suitable: why not?

But, another way, now that your requirement is more clear:
Use DIR_LIST, it will give you a directory listing in PL/SQL, in a (temporary) table (you can tweak it as desired ofcourse) and then you could use UTL_FILE to copy the names of the files to another file by simply reading the table contents.
You can read about it and get the code here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:439619916584

hoek,
I successfully executed all the given files, also i had my DBA execute the 4 permissions + COMMIT. Actually everything looked great, but the last line here is not doing the work, where iam wrong?


Create the Java Stored Procedure : successful

Publish the Java call specification: successful

Grant Privileges: successful done by DBA from SYS schema

Test It
SET SERVEROUTPUT ON SIZE 1000000
CALL DBMS_JAVA.SET_OUTPUT(1000000);
BEGIN
host_command (p_command => ' $HOME/test/ls *.dat > /dir1/dir2/dir3/test2.txt');
END;
/

this executes well but my test2.txt is empty, also i provided chmod 777 *


I'll try the second option you have given and give you credit if it works......thanks.

Edited by: koloo on Apr 15, 2010 2:45 PM

I used the link you just provided and all objects created successfully like before but my code below generated this error, what am i not doing right????

exec get_dir_list( 'lopt/applic//mydevappl/yccw/11.5.0/datasrc/hr' );


ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException
ORA-06512: at "APPS.GET_DIR_LIST", line 1
ORA-06512: at line

Take a breath and calm down.
And tell us your DB-version and how you are calling the wrapper.
For example: who's schema-owner APPS?
Are you using Forms and some application server?
Please be as complete as you can be and don't cut off error messages when posting them.
Always post the full error message. Every error comes with a message.

Using 10gR2, yes my schema is APPS and DBA executed permissions from SYS. Every thing looks correct but the script is not doing the work(not populating test.txt), i have been trying this thing the entire day.......what a pain and i cant calm down its driving me crazy.

The codes are here:
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OSCommand" AS
import java.io.*;
public class OSCommand{
public static String Run(String Command){
try{
Runtime.getRuntime().exec(Command);
return("0");
}
catch (Exception e){
System.out.println("Error running command: " + Command +
"\n" + e.getMessage());
return(e.getMessage());
}
}
}
/

CREATE or REPLACE FUNCTION OSCommand_Run(Command IN STRING)
RETURN VARCHAR2 IS
LANGUAGE JAVA
NAME 'OSCommand.Run(java.lang.String) return int';
/

Execute dbms_java.grant_permission( 'APPS','SYS:java.io.FilePermission','<<ALL FILES>>','execute');
execute dbms_java.grant_permission( 'APPS','SYS:java.lang.RuntimePermission', 'writeFileDescriptor','*' );
execute dbms_java.grant_permission( 'APPS','SYS:java.lang.RuntimePermission','readFileDescriptor', '*' );


Set Serverout On
Declare
x Varchar2(2000);
Begin

x := OSCommand_Run('/dir1/testappl/xxxfem/dir3/dir4/dir5/gl/who > test.txt');
DBMS_OUTPUT.Put_Line(x);
End;
/

Edited by: koloo on Apr 15, 2010 4:52 PM

Edited by: koloo on Apr 15, 2010 5:00 PM