Host Command in Windows 2008

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Host Command in Windows 2008

Postby Guru3i » Thu Dec 13, 2012 5:40 pm

Hi Tim,

http://www.oracle-base.com/articles/8i/shell-commands-from-plsql.php

With the help from your link i was able to deploy this successfully in a linux box.

But the below code is not working as expected in a Windows box

Code: Select all
DECLARE
      L_OUTPUT DBMS_OUTPUT.CHARARR;
      L_LINES  INTEGER := 1000;
    BEGIN
      DBMS_OUTPUT.ENABLE(1000000);
      DBMS_JAVA.SET_OUTPUT(1000000);
    /* 
       HOST_COMMAND('/bin/sh/ls -alrtd  /data/source/');
      --HOST_COMMAND('/bin/ls -alrtd /data/source/*');   -- Working from Linux Box
    */
     HOST_COMMAND('dir /b /ad  d:\rnd\*'); -- Not Working from Windows Box 2008 Server R2
      DBMS_OUTPUT.GET_LINES(L_OUTPUT, L_LINES);
      FOR I IN 1 .. L_LINES LOOP
         DBMS_OUTPUT.put_line(l_output(i));
      END LOOP;
    END;
/


Code: Select all
 OS Command is  - dir /b /ad  d:\rnd\* 


The above command should lists the directory names under the directory rnd in D Drive, i am trying to spool it into the character array and use it in my application.

Code above works very well when number of directories it reads is below 40 or maximum 50 but when number of directories increases to more than 50 (i.e. in d:\rnd folder if there are more than 50 folders ) the respective ORACLE session just hangs the & i am forced to kill it from the task manager of the oracle database server. Even killing it through Alter system doesnt kills the session.

Any known issues/limitations when this code is put on a windows box. Your inputs would be great help as always.

Thanks in advance.
Guru3i
Senior Member
 
Posts: 95
Joined: Fri Aug 24, 2007 7:17 am

Re: Host Command in Windows 2008

Postby Tim... » Thu Dec 13, 2012 8:41 pm

Hi.

I just did a run through the article on an 11gR2 RAC installation on Windows 2008 R2.

I followed the article and it worked fine. If I was creating the Java from sqlplus in a CMD windows, I had to change "\" to "\\" in this line.

Code: Select all
finalCommand[0] = "C:\\windows\\system32\\cmd.exe";  // Windows XP/2003


This also worked for me.

Code: Select all
finalCommand[0] = "C:\\windows\\SysWoW64\\cmd.exe";  // Windows XP/2003


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: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Host Command in Windows 2008

Postby Guru3i » Fri Dec 14, 2012 3:39 am

Hi Tim,
Thanks. I will check the code and try once with the changes you have said. Did you happen to test it for a folder which had more than 50 folders in it. Because i am having issue only if the number of folders is greater than 40 or 50.
Thanks in advance
Guru3i
Senior Member
 
Posts: 95
Joined: Fri Aug 24, 2007 7:17 am

Re: Host Command in Windows 2008

Postby Guru3i » Fri Dec 14, 2012 8:03 am

Hi Tim,
Tried both the options and still the same issue. The session hangs when its reads the directory with greater than 50 directories. Any clues or help please
Thanks
Guru3i
Senior Member
 
Posts: 95
Joined: Fri Aug 24, 2007 7:17 am

Re: Host Command in Windows 2008

Postby Guru3i » Mon Dec 17, 2012 5:47 am

Hi Tim,
Any light you can throw on this issue to overcome our problem. Thanks in anticipation
Guru3i
Senior Member
 
Posts: 95
Joined: Fri Aug 24, 2007 7:17 am

Re: Host Command in Windows 2008

Postby Tim... » Mon Dec 17, 2012 5:15 pm

Hi.

I think it is more likely to be a product of the amount of text you are returning, rather than the number of directories.

The routine returns a VARCHAR2, so it is limited to 32K out output returned. You possibly need to alter it to return a CLOB?

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: 17950
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Host Command in Windows 2008

Postby Guru3i » Tue Dec 18, 2012 5:29 pm

Hi Tim,
The same code works fine in a linux box. This might be a restriction/security issue in Windows 2008 box.
By the way what do you mean by returning as CLOB the java function. Is it doable ?
Guru3i
Senior Member
 
Posts: 95
Joined: Fri Aug 24, 2007 7:17 am

Re: Host Command in Windows 2008

Postby Tim... » Tue Dec 18, 2012 8:26 pm

Hi.

My bad. It's not returning a VARCHAR2. It's returning a collection of data using the DBMS_OUTPUT pipe. I've just tried on Linux and 150 directories works fine. Not able to try on WIndows at the moment, as I don't have Windows installations here.

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: 17950
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 1 guest