Host Command in Windows 2008

[Guru3i]

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.

[Tim...]

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...

[Guru3i]

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]

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]

Hi Tim,
Any light you can throw on this issue to overcome our problem. Thanks in anticipation

[Tim...]

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...

[Guru3i]

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 ?

[Tim...]

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...