calling a cmd file from pl/sql

[javi]

Hello all,
I am trying to call (execute) a command file from inside a pl/sql procedure.

If say..the .cmd file is located in C:\temp, how would I include this path and filename and call it from inside my pl/sql code?

Please help

Thanks,
Jav

[mber]

At least two ways:
1. use external program like C
2. use java code and load into system, then call this piece of java excutable code form PL/SQL procedure.

Take care

[miritika]

Hello mber,

Could u please give one example to solve the problem as mentioned by javi.

Thanks

[kong]

Hi guys,

Oracle Databases are servers not clients.

RE: mber 1) creating an external program is a real pain, you will find an example of using extproc at Oracle's TechNet site.

2) Look up an article at http://www.oramag.com they described calling java packages through PL/SQL in one of the articles towards the end of last year.

Perhaps you could rewrite the logic by calling the procedure through a script (ksh say) and execute it upon completion, eg:

#!/usr/bin/ksh

if sqlplus username/passwd@DB << EOF | grep "^ORA-"
exec my_procedure
exit
EOF
then
echo "Oracle Error occured"
exit(1)
fi

#assuming my_procedure writes to /tmp an exe file called runme.exe

/tmp/runme.exe

Since you are on a DOS/NT system, you can either learn DOS SHELL or download an emulator.

Cheers.

CM

[javi]

Thanks all so much for your responses.

But I don't follow why I would need to create C or java programs to accomplish this.

I already have a command file in place and all I want to do is to call that file from my code.

Is there simpler way to do this?

[vbaskar]

Hi Jav...

Even i had a similar problem and got the package which is already developed and working from http://www.revealnet.com

http://revealnet.com/Pipelines/PLSQL/archives.htm

[ahsan]

Hi vbaskar

I have a similar issue. Could u give the name of the package?

Hi Jav...

Even i had a similar problem and got the package which is already developed and working from http://www.revealnet.com

http://revealnet.com/Pipelines/PLSQL/archives.htm

[davey23uk]

ahsan - look at the date of the post - it was over 5 years ago, that user hasnt been on here for 3 and half years

[tabreaz]

Here is the java class u can use to run any OS command:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED Command 
AS
import java.io.*;
import java.util.*;
public class Command{
public static void run(String cmdText) 
                     throws IOException, InterruptedException
{
  int rtn;
       Runtime rt = Runtime.getRuntime();
  Process prcs = rt.exec(cmdText);
  rtn =  prcs.waitFor();
}
}

Then you can publish a PL/SQL procedure to be used in ur applications.

CREATE OR REPLACE PROCEDURE runoscommand(cmd IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Command.run(java.lang.String)'

Now you can use the above procedure to run any batch files:

For example to unzip files:

CREATE OR REPLACE PROCEDURE unzipfiles(pdirname IN VARCHAR2, 
                pfilename IN VARCHAR2, poutdirname IN VARCHAR2)
IS
    loscmd  VARCHAR2(120);
BEGIN
  loscmd := 'f:\data\7za e -o' || pdirname || ' ';
  loscmd := loscmd || poutdirname || ' ' || pfilename;
  runoscommand(loscmd);
END;

Hope this example will be helpful.