calling a cmd file from pl/sql

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

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

Hello mber,

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

Thanks

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

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?

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

Hi vbaskar

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

Quote:

---

Originally Posted by 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 - look at the date of the post - it was over 5 years ago, that user hasnt been on here for 3 and half years

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

Code:

---


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.

Code:

---


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:

Code:

---


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.