calling a cmd file from pl/sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: calling a cmd file from pl/sql

  1. #1
    Join Date
    Apr 2001
    Posts
    4
    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

  2. #2
    Join Date
    Oct 2000
    Posts
    123
    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

  3. #3
    Join Date
    Apr 2001
    Posts
    51
    Hello mber,

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

    Thanks

  4. #4
    Join Date
    Apr 2001
    Posts
    17
    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

  5. #5
    Join Date
    Apr 2001
    Posts
    4

    calling a command file from pl/sql code

    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?



  6. #6
    Join Date
    Jan 2001
    Posts
    153
    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

    Vijay.s

  7. #7
    Join Date
    Sep 2006
    Posts
    1

    ahsan

    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

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  9. #9
    Join Date
    Sep 2005
    Posts
    278
    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.
    Last edited by tabreaz; 09-14-2006 at 04:54 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width