using host_command

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

using host_command

Postby Lech » Thu May 23, 2013 11:14 am

Hi

I start to use package "host_command", which I found on site http://www.oracle-base.com/articles/8i/ ... -plsql.php .

When I execute code:

Code: Select all
begin
  DBMS_JAVA.set_output(1000000);
  host_command (p_command => '(net stop "Usługa indeksowania") 2>>c:\a.txt 1>'||CHR(38)||'2');
end;

or

begin
  DBMS_JAVA.set_output(1000000);
  host_command (p_command => '(net stop "Usługa indeksowania") 2>>c:\a.txt');
end;


I do not get the file c:\a.txt, but if I execute code:

Code: Select all
begin
  DBMS_JAVA.set_output(1000000);
  host_command (p_command => 'echo test 2>>c:\a.txt 1>'||CHR(38)||'2');
end;


second handle STDERR is written to the file.

Why in first example there isn't writen output to file ?

Best regards,
Lech
Lech
Member
 
Posts: 3
Joined: Thu May 23, 2013 10:35 am

Re: using host_command

Postby Tim... » Thu May 23, 2013 1:25 pm

Hi.

I guess it depends whether utilities write to stdout ot stderr. You see this in UNIX also. Command line utilities are often written for interactive use. The fact both stderr and stdout both show in a shell means it doesn't matter. It only comes to light when you run then in scripts.

I would suggest you always redirect both.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17966
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: using host_command

Postby Lech » Thu May 23, 2013 7:06 pm

Hi.

Thank you for your answer !

I'm not sure if I understood you correctly. I think there is some restriction on the procedure host_command, because when code is executed simply in cmd, it works:

Code: Select all
C:\>(net stop Fax)
Usługa Fax nie została uruchomiona.

Dostępne są dalsze informacje Pomocy; aby je uzyskać, wpisz NET HELPMSG 3521.


and two of these commands produce a file:

Code: Select all
C:\>(net stop Fax) 2>>c:\a.txt 1>&2

C:\>(net stop Fax) 1>>c:\a.txt 2>&1

C:\>


but any of the following commands in PL/SQL do not write to file:

Code: Select all
begin
  DBMS_JAVA.set_output(1000000);
  host_command (p_command => '(net stop Fax) 2>>c:\a.txt 1>'||CHR(38)||'2');
end;

begin
  DBMS_JAVA.set_output(1000000);
  host_command (p_command => '(net stop Fax) 1>>c:\a.txt 2>'||CHR(38)||'1');
end;


and this code do not produce dbms_output, too:

Code: Select all
begin
  DBMS_JAVA.set_output(1000000);
  host_command (p_command => 'net stop Fax');
end;


I quess this depends on os command. For example command "dir" produce dbms_output:

Code: Select all
begin
  DBMS_JAVA.set_output(1000000);
  host_command (p_command => 'dir');
end;


Is there any way to return this output to PL/SQL ?
I need to get a PID number of process, maybe from taskist.exe, and pass it to the PL/SQL, but with tasklist.exe is the same problem as with "net stop" - host_command does not capture output.

Best regards
Lech
Lech
Member
 
Posts: 3
Joined: Thu May 23, 2013 10:35 am

Re: using host_command

Postby Tim... » Fri May 24, 2013 12:59 pm

Hi.

I've just run this and it works fine for me.

Code: Select all
BEGIN
  DBMS_JAVA.set_output(1000000);
  host_command (p_command => 'echo test 2>>c:\a.txt 1>'||CHR(38)||'2');
END;
/


If I put in a junk command, the file contains the error messages.

Code: Select all
BEGIN
  DBMS_JAVA.set_output(1000000);
  host_command (p_command => 'aaa 2>>c:\a.txt 1>'||CHR(38)||'2');
END;
/

'aaa' is not recognized as an internal or external command,
operable program or batch file.


Code: Select all
BEGIN
  DBMS_JAVA.set_output(1000000);
  host_command (p_command => '(net start OracleJobSchedulerDB11G) 2>>c:\a.txt 1>'||CHR(38)||'2');
END;
/

'aaa' is not recognized as an internal or external command,
operable program or batch file.


The JVM does have a number of restrictions. For example, anything that could result in a visual dialog is restricted, so if a piece of code or a service could result in a message dialog appearing on the screen, this can not be called from the JVM.

The alternative to this is to schedule an executable job using the job scheduler.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17966
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: using host_command

Postby Lech » Fri May 24, 2013 1:58 pm

Hello Tim

Thank you very much for your answer !

Regards
Lech
Lech
Member
 
Posts: 3
Joined: Thu May 23, 2013 10:35 am

Re: using host_command

Postby Tim... » Fri May 24, 2013 4:12 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17966
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 3 guests

cron