DOS command output in pl/sql [message #405748] |
Fri, 29 May 2009 05:25 |
|
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
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 #426218 is a reply to message #426209] |
Wed, 14 October 2009 07:33 |
|
ramoradba
Messages: 2457 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 |
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 #426771 is a reply to message #426736] |
Mon, 19 October 2009 09:18 |
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 #426798 is a reply to message #426779] |
Mon, 19 October 2009 13:25 |
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 #426951 is a reply to message #426888] |
Tue, 20 October 2009 06:54 |
ThomasG
Messages: 3212 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 #426970 is a reply to message #426957] |
Tue, 20 October 2009 07:45 |
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 |
ThomasG
Messages: 3212 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 |
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
|
|
|
|