Home » SQL & PL/SQL » SQL & PL/SQL » DOS command output in pl/sql (11g, server 2003)
DOS command output in pl/sql [message #405748] Fri, 29 May 2009 05:25 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello Gurus,

Actually I want output of DOS command in PL/SQL.
I didn't find any help by googling and from this forum also.

If I specify command PING from pl/sql block as
ping www.google.com

then output it returns should be
Pinging www.l.google.com [209.85.153.104] with 32 bytes of data:

Reply from 209.85.153.104: bytes=32 time=50ms TTL=244
Reply from 209.85.153.104: bytes=32 time=47ms TTL=244
Reply from 209.85.153.104: bytes=32 time=49ms TTL=244
Reply from 209.85.153.104: bytes=32 time=47ms TTL=244

Ping statistics for 209.85.153.104:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 47ms, Maximum = 50ms, Average = 48ms


This output I should get in variable.
Is this possible with pl/sql?
If yes please help me to do so.

regards,
Delna
Re: DOS command output in pl/sql [message #405757 is a reply to message #405748] Fri, 29 May 2009 06:11 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.oracle-base.com/forums/viewtopic.php?t=6007&highlight=#p7442

Hope the above will help you.Ididn`t try this.Try at your Testing DB.

Sriram

[Updated on: Fri, 29 May 2009 06:16]

Report message to a moderator

Re: DOS command output in pl/sql [message #405763 is a reply to message #405748] Fri, 29 May 2009 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I didn't find any help by googling and from this forum also.

You really badly and badly search.

Regards
Michel
Re: DOS command output in pl/sql [message #405768 is a reply to message #405757] Fri, 29 May 2009 06:40 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.orafaq.com/scripts/plsql/oscmd.txt



http://www.orafaq.com/wiki/PL/SQL_FAQ#Can_one_execute_an_operating_system_command_from_PL.2FSQL.3F

Sriram
Re: DOS command output in pl/sql [message #405777 is a reply to message #405748] Fri, 29 May 2009 07:17 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi ramoradba,

I have already gone through those links you provide

SQL>CREATE OR REPLACE FUNCTION host_command( cmd IN VARCHAR2 )
  2     RETURN INTEGER IS
  3       status   NUMBER;
  4       errormsg VARCHAR2(80);
  5       pipe_name VARCHAR2(30);
  6   BEGIN
  7     pipe_name := 'HOST_PIPE';
  8     dbms_pipe.pack_message( cmd );
  9     status := dbms_pipe.send_message(pipe_name);
 10     RETURN status;
 11   END;
 12   /

Function created.

SQL>select host_command('ping www.google.com') from dual;

HOST_COMMAND('PINGWWW.GOOGLE.COM')
==================================
                                 0

1 row selected.

SQL>select host_command('dir') from dual;

HOST_COMMAND('DIR')
===================
                  0

1 row selected.


that is what I don't want.
I want output of the command as I said so far.

I have gone through other links also, but not helped me.
By the way, thanks for that help.

Quote:

You really badly and badly search.

Regards
Michel


If you can good and very good and better search, then why didm't you provide links?

regards,
Delna
Re: DOS command output in pl/sql [message #405780 is a reply to message #405777] Fri, 29 May 2009 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If you can good and very good and better search, then why didm't you provide links?

Because I'm too lazy to search for you when you can do it yourself.

Regards
Michel
Re: DOS command output in pl/sql [message #405784 is a reply to message #405748] Fri, 29 May 2009 07:33 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello Michel sir,

Your comments like
Quote:
Because I'm too lazy to search for you when you can do it yourself.


makes me angry and also, increases respect for you.

As I have said earlier,
Quote:
I didn't find any help by googling and from this forum also.

and
Quote:
I have already gone through those links you provide


and
SQL>CREATE OR REPLACE FUNCTION host_command( cmd IN VARCHAR2 )
  2     RETURN INTEGER IS
  3       status   NUMBER;
  4       errormsg VARCHAR2(80);
  5       pipe_name VARCHAR2(30);
  6   BEGIN
  7     pipe_name := 'HOST_PIPE';
...


After doing all those things, I have come here to post my problem.
Now would you leave your laziness, please? Laughing

regards,
Delna
Re: DOS command output in pl/sql [message #405787 is a reply to message #405784] Fri, 29 May 2009 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
At first thought:
http://asktom.oracle.com/pls/asktom/f?p=100:11:1304413309281278::::P11_QUESTION_ID:952229840241

Regards
Michel
Re: DOS command output in pl/sql [message #405795 is a reply to message #405784] Fri, 29 May 2009 08:33 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
@delna
Quote:
Hi ramoradba,

I have already gone through those links you provide




read the content of given links (3 links) correctly....

You should get atleast

The handle is invalid.

PL/SQL procedure successfully completed.
Re: DOS command output in pl/sql [message #405849 is a reply to message #405748] Sat, 30 May 2009 01:15 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thank you Michel sir and ramoradba for your help.

regards,
Delna
Re: DOS command output in pl/sql [message #426199 is a reply to message #405795] Wed, 14 October 2009 06:40 Go to previous messageGo to next message
JuicyFruit
Messages: 34
Registered: September 2009
Member

I'm getting "The handle is invalid." after performing last script "exec host('"C:\WINDOWS\system32\cmd.exe" /C DIR "');" from here.
What can cause this problem?
Re: DOS command output in pl/sql [message #426202 is a reply to message #426199] Wed, 14 October 2009 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The procedure you took is for Unix, take the procedure from the links provided above.

Regards
Michel

[Updated on: Wed, 14 October 2009 06:47]

Report message to a moderator

Re: DOS command output in pl/sql [message #426209 is a reply to message #426202] Wed, 14 October 2009 07:07 Go to previous messageGo to next message
JuicyFruit
Messages: 34
Registered: September 2009
Member

why is my procedure for unix? can't find for Windows.
Re: DOS command output in pl/sql [message #426215 is a reply to message #426209] Wed, 14 October 2009 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It was clearly written by Frank for Unix.
Firt read the provided links, Tom's one address some specific DOS issues.

Regards
Michel

[Updated on: Wed, 14 October 2009 07:22]

Report message to a moderator

Re: DOS command output in pl/sql [message #426218 is a reply to message #426209] Wed, 14 October 2009 07:33 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
It will work on windows too....
The link you posted is same as the above given.
The blog user added that code of FRANK.
Z:\>sqlplus sys  as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 14 17:45:19 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> EXEC dbms_java.grant_permission('SCOTT', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');

PL/SQL procedure successfully completed.

SQL> EXEC dbms_java.grant_permission('SCOTT', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');

PL/SQL procedure successfully completed.



SQL> conn scott/******
Connected.
SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
  2  import java.io.*;
  3  public class Host {
  4    public static void executeCommand(String command) {
  5      try {
  6        String[] finalCommand;
  7        if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1) {
  8          finalCommand = new String[4];
  9          finalCommand[0] = "C:\\windows\\system32\\cmd.exe";
 10          finalCommand[1] = "/y";
 11          finalCommand[2] = "/c";
 12          finalCommand[3] = command;
 13        } else {                                             // Linux or Unix System
 14          finalCommand = new String[3];
 15          finalCommand[0] = "/bin/sh";
 16          finalCommand[1] = "-c";
 17          finalCommand[2] = command;
 18        }
 19
 20        // Execute the command...
 21        final Process pr = Runtime.getRuntime().exec(finalCommand);
 22
 23        // Capture output from STDOUT...
 24        BufferedReader br_in = null;
 25        try {
 26          br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
 27          String buff = null;
 28          while ((buff = br_in.readLine()) != null) {
 29            System.out.println("stdout: " + buff);
 30            try {Thread.sleep(100); } catch(Exception e) {}
 31          }
 32          br_in.close();
 33        } catch (IOException ioe) {
 34          System.out.println("Error printing process output.");
 35          ioe.printStackTrace();
 36        } finally {
 37          try {
 38            br_in.close();
 39          } catch (Exception ex) {}
 40        }
 41
 42        // Capture output from STDERR...
 43        BufferedReader br_err = null;
 44        try {
 45          br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
 46          String buff = null;
 47          while ((buff = br_err.readLine()) != null) {
 48            System.out.println("stderr: " + buff);
 49            try {Thread.sleep(100); } catch(Exception e) {}
 50          }
 51          br_err.close();
 52        } catch (IOException ioe) {
 53          System.out.println("Error printing execution errors.");
 54          ioe.printStackTrace();
 55        } finally {
 56          try {
 57            br_err.close();
 58          } catch (Exception ex) {}
 59        }
 60      }
 61      catch (Exception ex) {
 62        System.out.println(ex.getLocalizedMessage());
 63      }
 64    }
 65
 66  };
 67  /

Java created.

SQL> CREATE OR REPLACE PROCEDURE host (p_command IN VARCHAR2)
  2     AS LANGUAGE JAVA
  3     NAME 'Host.executeCommand (java.lang.String)';
  4  /

Procedure created.

SQL> CALL DBMS_JAVA.SET_OUTPUT(1000000);

Call completed.

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> exec host('DIR');
the Permission (java.io.FilePermission C:\windows\system32\cmd.exe execute) has
not been granted to SCOTT. The PL/SQL to grant this is
dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission',
'C:\windows\system32\cmd.exe', 'execute' )

PL/SQL procedure successfully completed.

SQL> EXEC dbms_java.grant_permission('SCOTT', 'SYS:java.io.FilePermission', 'C:\windows\system32\cmd.exe','execute');

PL/SQL procedure successfully completed.

SQL> exec host('DIR');
stdout:  Volume in drive C has no label.
stdout:  Volume Serial Number is B8B8-00DF
stdout:
stdout:  Directory of C:\oracle\product\10.2.0\db_3\DATABASE
stdout:
stdout: 10/13/2009  06:24p    <DIR>          .
stdout: 10/13/2009  06:24p    <DIR>          ..
stdout: 10/06/2009  03:16p    <DIR>          archive
stdout: 10/09/2009  08:07p               106
core_+asm_pid_3932_tid_x378_2009_10_9_14_54_7.log
stdout: 10/07/2009  12:27p               106
core_bhanu_pid_408_tid_x1D8_2009_10_6_18_34_4.log
stdout: 10/07/2009  07:37p               106
core_bhanu_pid_972_tid_x494_2009_10_7_12_35_4.log
stdout: 10/13/2009  10:04a               106
core_kalyani_pid_1704_tid_x748_2009_10_12_12_3_49.log
stdout: 10/13/2009  06:24p               106
core_kalyani_pid_1956_tid_xEC_2009_10_13_12_21_15.log
stdout: 10/09/2009  08:07p               106
core_kamal_pid_1076_tid_x4FC_2009_10_9_12_9_26.log
stdout: 10/13/2009  10:04a               106
core_satya_pid_1856_tid_x73C_2009_10_12_12_3_49.log
stdout: 10/13/2009  06:24p               106
core_satya_pid_252_tid_x134_2009_10_13_12_21_23.log
stdout: 10/09/2009  08:07p               106
core_sriram_pid_2656_tid_x160_2009_10_9_16_47_18.log
stdout: 10/09/2009  08:07p             2,048 hc_+asm.dat
stdout: 10/06/2009  03:51p             2,048 hc_bhanu.dat
stdout: 10/12/2009  10:05a             2,048 hc_kalyani.dat
stdout: 10/09/2009  09:43a             2,048 hc_kamal.dat
stdout: 10/12/2009  10:28a             2,048 hc_satya.dat
stdout: 10/09/2009  08:07p             2,048 hc_sriram.dat
stdout: 10/09/2009  04:24p               125 init+ASM.ora
stdout: 10/12/2009  10:14a                62 initkalyani.ora
stdout: 10/12/2009  10:52a                60 initsatya.ora
stdout: 10/09/2009  04:51p               125 initsriram.ora
stdout: 06/25/2005  03:18a            31,744 oradba.exe
stdout: 10/14/2009  12:28p             1,920 oradim.log
stdout: 10/09/2009  04:26p             1,536 PWD+ASM.ora
stdout: 10/12/2009  10:15a             1,536 PWDkalyani.ora
stdout: 10/14/2009  05:49p             1,536 PWDsatya.ora
stdout: 10/09/2009  04:47p             2,560 PWDsriram.ORA
stdout: 10/07/2009  11:45a         7,061,504 SNCFBHANU.ORA
stdout: 10/09/2009  05:15p             1,536 SPFILE+ASM.ORA
stdout: 10/14/2009  12:23p             3,584 SPFILEKALYANI.ORA
stdout:               28 File(s)      7,121,070 bytes
stdout:                3 Dir(s)   4,656,644,096 bytes free

PL/SQL procedure successfully completed.



AS the user not able to find the code for windows Even link specified,I posted the code.If it like spoon feeding, I am sorry.

Regards
Sriram.
Re: DOS command output in pl/sql [message #426309 is a reply to message #426218] Wed, 14 October 2009 18:02 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
you can also do this kind of thing using dbms_scheduler, but you need to redirect outout to a temp file from what I recall, then read that file back in using external table, utl_file or whatever. Advantage of dbms_scheduler is no Java objects & permissions required, but beware of having single temp file because it won't work for multiple concurrent requests.

You should also look at UTL_INADDR and UTL_TCP.

select UTL_INADDR.get_host_address('www.google.com'),
       UTL_INADDR.get_host_name('72.14.213.147') 
from dual ;


74.125.127.106	pv-in-f147.google.com





Depends whether you're interested in the IP address, round-trip interval or whether the host is reachable...


Re: DOS command output in pl/sql [message #426321 is a reply to message #405748] Wed, 14 October 2009 23:41 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
you can also do this kind of thing using dbms_scheduler,...

That is what I did in my case.

I
created scheduler,
called one batch file in that,
redirect output of that batch file into text file,
read that text file for required output.

regards,
Delna
Re: DOS command output in pl/sql [message #426370 is a reply to message #426218] Thu, 15 October 2009 02:59 Go to previous messageGo to next message
JuicyFruit
Messages: 34
Registered: September 2009
Member

Thanks a lot.
One more question. Is it possible to execute command like
exec host('c:\WINDOWS\system32\cmd.exe /c fsutil volume diskfree C:');

and get output like we get from a cmd.exe executing it on host.
Total # of free bytes        : 12818509824
Total # of bytes             : 20974428160
Total # of avail free bytes  : 12818509824

As I understood this version of the script given above doesn't have such possibility.
Thank you.
Re: DOS command output in pl/sql [message #426703 is a reply to message #405748] Mon, 19 October 2009 01:35 Go to previous messageGo to next message
JuicyFruit
Messages: 34
Registered: September 2009
Member

Can anyone help me?
Re: DOS command output in pl/sql [message #426708 is a reply to message #426703] Mon, 19 October 2009 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In which way? Searching if it is possible and writing the code for you?
Post what you tried.

Regards
Michel
Re: DOS command output in pl/sql [message #426710 is a reply to message #426708] Mon, 19 October 2009 02:04 Go to previous messageGo to next message
JuicyFruit
Messages: 34
Registered: September 2009
Member

I don't know where to start. I need to know if it's even possible to output that info:
Total # of free bytes        : 12818509824
Total # of bytes             : 20974428160
Total # of avail free bytes  : 12818509824 

Re: DOS command output in pl/sql [message #426718 is a reply to message #426710] Mon, 19 October 2009 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The purpose of PL/SQL is not to output. Wink
What in message http://www.orafaq.com/forum/mv/msg/145816/426218/102589/#msg_426218 does not work for you?

Regards
Michel
Re: DOS command output in pl/sql [message #426721 is a reply to message #426718] Mon, 19 October 2009 02:39 Go to previous messageGo to next message
JuicyFruit
Messages: 34
Registered: September 2009
Member

I get nothing executing
exec host('c:\WINDOWS\system32\cmd.exe /c fsutil volume diskfree C:');
Re: DOS command output in pl/sql [message #426722 is a reply to message #426721] Mon, 19 October 2009 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So maybe the problem comes from fsutil itself, maybe it does not write into standard output.

Regards
Michel
Re: DOS command output in pl/sql [message #426728 is a reply to message #426722] Mon, 19 October 2009 03:19 Go to previous messageGo to next message
JuicyFruit
Messages: 34
Registered: September 2009
Member

I suggest that's the problem:) but if it doesn't write to standard output, I can't get that info? Or there is a way?
Re: DOS command output in pl/sql [message #426736 is a reply to message #426728] Mon, 19 October 2009 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First search where it writes.
Then search if there is a Java class to read this streams.

Regards
Michel
Re: DOS command output in pl/sql [message #426771 is a reply to message #426736] Mon, 19 October 2009 09:18 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Sure seems to be STDOUT to me. You need to manually read the output to get it back into pl/sql

C:\>fsutil volume diskfree C: > t.txt

C:\>type t.txt
Total # of free bytes        : 253552586752
Total # of bytes             : 320070287360
Total # of avail free bytes  : 253552586752

C:\>
Re: DOS command output in pl/sql [message #426779 is a reply to message #426771] Mon, 19 October 2009 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You need to manually read the output to get it back into pl/sql

Tis is what the procedure does.
It works for DIR not for FSUTIL.

Regards
Michel
Re: DOS command output in pl/sql [message #426798 is a reply to message #426779] Mon, 19 October 2009 13:25 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Yes, I think I understand correctly that the output from FSUTIL can't be accessed in the same way as DIR. I've encountered similar issues before (on Solaris). My suggestion would be to redirect output to temp file and see if that catches the output. You can then just type the contents of the temp file and they will go to STDOUT that can be caught in the same way as DIR.
Re: DOS command output in pl/sql [message #426888 is a reply to message #405748] Tue, 20 October 2009 03:54 Go to previous messageGo to next message
JuicyFruit
Messages: 34
Registered: September 2009
Member

That's a good idea with that.. but I tried to perform
type temp.txt

and it doesn't work too.. Output is empty.
Re: DOS command output in pl/sql [message #426951 is a reply to message #426888] Tue, 20 October 2009 06:54 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Can you post the complete SQL*Plus session where you did run this?

"Output is empty" Could be anything from a wrongly created "host" command to a forgotten to "SET SERVEROUTPUT ON" to a forgotten "CALL DBMS_JAVA.SET_OUTPUT(1000000);" to you running it in a client tool that doesn't support Java output.

[Updated on: Tue, 20 October 2009 06:58]

Report message to a moderator

Re: DOS command output in pl/sql [message #426954 is a reply to message #426888] Tue, 20 October 2009 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TYPE works for me but it is mandatory to specify the full path of the file.

Regards
Michel
Re: DOS command output in pl/sql [message #426957 is a reply to message #426771] Tue, 20 October 2009 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, "fsutil volume diskfree C: > t.txt" works but using the "host" procedure above with this command it does not fill the file (but it creates/clears it).

Regards
Michel
Re: DOS command output in pl/sql [message #426970 is a reply to message #426957] Tue, 20 October 2009 07:45 Go to previous messageGo to next message
JuicyFruit
Messages: 34
Registered: September 2009
Member

Okay, here is what I do in SQL plus:

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Release 10.2.0.4.0 - Production


SQL> connect sys/******@server1 as sysdba

Connected.

SQL> EXEC dbms_java.grant_permission('USER1', 'SYS:java.lang.RuntimePermission', 'writeFileDescripto
r', '');

PL/SQL procedure successfully completed.

SQL> EXEC dbms_java.grant_permission('USER1', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor
', '');

PL/SQL procedure successfully completed.

SQL> EXEC dbms_java.grant_permission('USER1', 'SYS:java.io.FilePermission', 'C:\windows\system32\cmd
.exe','execute');

PL/SQL procedure successfully completed.

SQL> EXEC dbms_java.grant_permission( 'USER1', 'SYS:java.io.FilePermission','C:\windows\system32\cmd
.exe /c dir C:\','execute');

PL/SQL procedure successfully completed.

SQL> EXEC dbms_java.grant_permission( 'USER1','SYS:java.io.FilePermission','C:\windows\system32\cmd.exe /c fsutil volume diskfree C: >temp.txt','execute');

SQL> EXEC dbms_java.grant_permission( 'USER1','SYS:java.io.FilePermission','C:\windows\system32\cmd.exe /c type temp.txt','execute');


SQL> connect USER1/******@server1
Connected.


SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
  2     import java.io.*;
  3     public class Host {
  4       public static void executeCommand(String command) {
  5         try {
  6           String[] finalCommand;
  7           if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1) {
  8      finalCommand = new String[1];
  9      finalCommand[0] = command;
 10  
 11           } else {                                             // Linux or Unix System
 12             finalCommand = new String[3];
 13             finalCommand[0] = "/bin/sh";
 14             finalCommand[1] = "-c";
 15             finalCommand[2] = command;
 16           }
 17   
 18           // Execute the command...
 19           final Process pr = Runtime.getRuntime().exec(finalCommand);
 20   
 21           // Capture output from STDOUT...
 22           BufferedReader br_in = null;
 23           try {
 24             br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
 25             String buff = null;
 26             while ((buff = br_in.readLine()) != null) {
 27               System.out.println("stdout: " + buff);
 28               try {Thread.sleep(100); } catch(Exception e) {}
 29             }
 30             br_in.close();
 31           } catch (IOException ioe) {
 32             System.out.println("Error printing process output.");
 33             ioe.printStackTrace();
 34           } finally {
 35             try {
 36               br_in.close();
 37             } catch (Exception ex) {}
 38           }
 39   
 40           // Capture output from STDERR...
 41           BufferedReader br_err = null;
 42           try {
 43             br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
 44             String buff = null;
 45             while ((buff = br_err.readLine()) != null) {
 46               System.out.println("stderr: " + buff);
 47               try {Thread.sleep(100); } catch(Exception e) {}
 48             }
 49             br_err.close();
 50           } catch (IOException ioe) {
 51             System.out.println("Error printing execution errors.");
 52             ioe.printStackTrace();
 53           } finally {
 54             try {
 55               br_err.close();
 56             } catch (Exception ex) {}
 57           }
 58         }
 59         catch (Exception ex) {
 60           System.out.println(ex.getLocalizedMessage());
 61         }
 62       }
 63   
 64     };
 65     /

Java created.


SQL> CREATE OR REPLACE PROCEDURE host (p_command IN VARCHAR2)
  2       AS LANGUAGE JAVA
  3       NAME 'Host.executeCommand (java.lang.String)';
  4    /

Procedure created.


SQL> CALL DBMS_JAVA.SET_OUTPUT(1000000);

Call completed.

SQL> SET SERVEROUTPUT ON SIZE 1000000

SQL> exec host('C:\windows\system32\cmd.exe /c fsutil volume diskfree C: >temp.txt');

PL/SQL procedure successfully completed.

SQL> exec host('C:\windows\system32\cmd.exe /c type temp.txt');

PL/SQL procedure successfully completed.


After all, temp.txt file is there. With all the information I need, but type temp.txt doesn't display file.

[Updated on: Tue, 20 October 2009 08:38]

Report message to a moderator

Re: DOS command output in pl/sql [message #427008 is a reply to message #426970] Tue, 20 October 2009 10:07 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Your command[0] seems to be the complete command string "C:\windows\system32\cmd.exe /c type temp.txt", but it would have to be an array of the single parts.

C:\windows\system32\cmd.exe
/c
type
temp.txt


Also, you cant't grant execute permission to "C:\windows\system32\cmd.exe /c fsutil volume diskfree C: >temp.txt", since that isn't a file. "C:\windows\system32\cmd.exe" is.

Fix the permission, and try changing

      finalCommand = new String[1];
      finalCommand[0] = command;


to

      finalCommand = new String[6];
      finalCommand[0] = 'C:\windows\system32\cmd.exe';
      finalCommand[1] = '/c';
      finalCommand[2] = 'fsutil';
      finalCommand[3] = 'volume'; 
      finalCommand[4] = 'diskfree';
      finalCommand[5] = 'C:';


or possibly

      finalCommand = new String[3];
      finalCommand[0] = 'C:\windows\system32\cmd.exe';
      finalCommand[1] = '/c';
      finalCommand[2] = 'fsutil volume diskfree C:';


Hard-coded for now, to see if that works.


[Updated on: Tue, 20 October 2009 10:08]

Report message to a moderator

Re: DOS command output in pl/sql [message #427021 is a reply to message #427008] Tue, 20 October 2009 11:15 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
This is all a bit like quantum mechanics - not everything behaves as you'd expect. First of all, metacharacters like <, >, >>, <<, ;, (, ), |, newline etc can all have their own special behavior. You want ">" to be seen as a metacharacter, not an extra parameter for sh. See the ">" eaxmple here http://www.ensta.fr/~diam/java/online/io/javazine.html
Windows may or may not behave the same.

1) make sure temp.txt is actually being created by your command
2) make sure that temp.txt hase some content. If not, stop here (you need solve the capture of the output first).

If you aren't getting output in temp.txt, try putting the whole command in a batch script and call the batch script from your host command.

If that still doesn't work, try something like calling master.bat:

master.bat
-----------
call slave.bat > temp.txt

slave.bat
----------
fsutil volume diskfree C:

[Updated on: Tue, 20 October 2009 11:17]

Report message to a moderator

Re: DOS command output in pl/sql [message #427093 is a reply to message #405748] Wed, 21 October 2009 01:55 Go to previous message
JuicyFruit
Messages: 34
Registered: September 2009
Member

temp.txt is created, but is empty..

[Updated on: Wed, 21 October 2009 02:37]

Report message to a moderator

Previous Topic: PROBLEM WRITING QUERY
Next Topic: procedure to change password
Goto Forum:
  


Current Time: Tue Sep 27 16:06:39 CDT 2016

Total time taken to generate the page: 0.17600 seconds