Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

HOST Command procedure not working correctly for scripting commands..

512818Mar 25 2008 — edited Mar 25 2008
Hello guys. Im using a pl/sql encased java procedure to run commands on the host server. Documentation on the source code for this can be found at: http://www.oracle-base.com/articles/8i/ShellCommandsFromPLSQL.php I can get the host procedure to run basic commands like delete file, move file, rename, etc - but im trying to get the procedure to run a WINSCP (SFTP client/server software) script, and im coming up empty handed. This is the procedure im running :

BEGIN
RUN_CMD('winscp.exe /console /script=D:\Empower\Dev\ACH\ACH_CTRL\CA308218.CTRL');
END;
/

PL/SQL procedure successfully completed.

After running this procedure, I went back to the FTP server to check if the file (the file the CTRL file is supposed to transfer) was there but the transfer was never completed. I also ran the same command ("winscp.exe /console /script=D:\Empower\Dev\ACH\ACH_CTRL\CA308218.CTRL") in a dos command prompt and the command worked perfectly. The problem isnt in the command syntax, or the structure of the CTRL file.

The I tried to execute the same command via the SQL prompt again, but tried to fetch any kind of DBMS_OUTPUT with the statement below. The block returned no output...

DECLARE
l_output DBMS_OUTPUT.chararr;
l_lines INTEGER := 1000;
BEGIN
DBMS_OUTPUT.enable(1000000);
DBMS_JAVA.set_output(1000000);
RUN_CMD('winscp /console /script=D:\Empower\Dev\ACH\ACH_CTRL\CA308218.CTRL');
DBMS_OUTPUT.get_lines(l_output, l_lines);
IF l_output IS NULL THEN
DBMS_OUTPUT.PUT_LINE('NULL'||l_lines);
ELSE
DBMS_OUTPUT.PUT_LINE('NOT NULL'||l_lines);
END IF;
FOR i IN 1 .. l_lines LOOP
DBMS_OUTPUT.put_line(l_output(i));
END LOOP;
END;

So after a few hours searching the forums and google, im stumped. Are host procedures supposed to be secluded to only issuing host commands or can we use them to execute FTP and SFTP procedures? I need a way to execute the command line:

"winscp /console /script=D:\Empower\Dev\ACH\ACH_CTRL\CA308218.CTRL"

Any clue to how to do this successfully?

I've already tried using the HOST built-in for forms (same result).. thanks in advance for your help.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 22 2008
Added on Mar 25 2008
1 comment
679 views