joehodge
asked on
calling an external java program from an oracle plsql procedure
Hi,
I am calling a java program from an Oracle (9.1) stored procedure in a web browser
the oracle procedure is in schema OTIF_Executor that has
connect, resource, javasyspriv privileges.
the java program takes in a cmd and exeutes it on the operating system (windows server 2000).
I've had it working before my db crashed, I then rebuilt the database and now cannot get the procedure working.
-------------------------- ---------- ---------- ---------- ------
begin..
lv_Cmd:= 'cscript //nologo D:\KPI\SCRIPTS\'||lv_file_ name||'';
lv_Res:= pty_osutil_pkg.fn_runoscmd (lv_Cmd, my_tab);
IF lv_Res = 1 THEN
NULL;
ELSE
RAISE java_broken;
END IF;
--PL\SQL wrapper for the java class ExecCmd which is stored in the database
FUNCTION fn_exec_cmd(p_shell VARCHAR2,p_cmd VARCHAR2,p_output IN OUT cmd_output) RETURN VARCHAR2
as
LANGUAGE JAVA NAME 'com.bat.utils.OSExecutor. executeCom mand(java. lang.Strin g,java.lan g.String,o racle.sql. ARRAY[]) return java.lang.String';
FUNCTION fn_RunOsCmd(p_cmd IN VARCHAR2, tab_cmdoutput IN OUT osutil_cmd_output) RETURN VARCHAR2 IS
tab_output cmd_output;
tab_errors cmd_output;
lv_shell VARCHAR2(10);
lv_cmd VARCHAR2(255);
lv_success VARCHAR2(32767);
lv_output_count INTEGER :=0;
BEGIN
IF UPPER(substr(p_cmd,1,3)) ='RSH' THEN
lv_shell := 'rsh';
lv_cmd := substr(p_cmd,4,length(p_cm d)-3);
ELSE
lv_shell := 'cmd /c';
lv_cmd := p_cmd;
END IF;
-- Execute command
lv_success := fn_exec_cmd(lv_shell,lv_cm d, tab_output);
IF tab_output.COUNT >0 THEN
FOR x IN tab_output.first..tab_outp ut.last LOOP
tab_cmdoutput(lv_output_co unt+x) := tab_output(x);
END LOOP;
lv_output_count := tab_cmdoutput.COUNT;
END IF;
RETURN lv_success;
I did not have this issue before. I created a collection as:
CREATE OR REPLACE
type CMD_OUTPUT as table of varchar2(255)
/
again, originally this worked. I then started getting
ORA-06531: Reference to uninitialized collection
the only way I could get round this was to initialse the collection
e.g.
IF UPPER(substr(p_cmd,1,3)) ='RSH' THEN
lv_shell := 'rsh';
lv_cmd := substr(p_cmd,4,length(p_cm d)-3);
ELSE
lv_shell := 'cmd /c';
lv_cmd := p_cmd;
END IF;
tab_output:= CMD_OUTPUT('A','B','C','X' ,'Y','Z');
-- Execute command
lv_success := fn_exec_cmd(lv_shell,lv_cm d, tab_output);
the procedure then has no error message but will not execute the command.
I know there are no issues with the java program so it has to be due to me putting
a) tab_output:= CMD_OUTPUT('A','B','C','X' ,'Y','Z');
or
b) not setting up the correct privileges in the database
any ideas?
I am calling a java program from an Oracle (9.1) stored procedure in a web browser
the oracle procedure is in schema OTIF_Executor that has
connect, resource, javasyspriv privileges.
the java program takes in a cmd and exeutes it on the operating system (windows server 2000).
I've had it working before my db crashed, I then rebuilt the database and now cannot get the procedure working.
--------------------------
begin..
lv_Cmd:= 'cscript //nologo D:\KPI\SCRIPTS\'||lv_file_
lv_Res:= pty_osutil_pkg.fn_runoscmd
IF lv_Res = 1 THEN
NULL;
ELSE
RAISE java_broken;
END IF;
--PL\SQL wrapper for the java class ExecCmd which is stored in the database
FUNCTION fn_exec_cmd(p_shell VARCHAR2,p_cmd VARCHAR2,p_output IN OUT cmd_output) RETURN VARCHAR2
as
LANGUAGE JAVA NAME 'com.bat.utils.OSExecutor.
FUNCTION fn_RunOsCmd(p_cmd IN VARCHAR2, tab_cmdoutput IN OUT osutil_cmd_output) RETURN VARCHAR2 IS
tab_output cmd_output;
tab_errors cmd_output;
lv_shell VARCHAR2(10);
lv_cmd VARCHAR2(255);
lv_success VARCHAR2(32767);
lv_output_count INTEGER :=0;
BEGIN
IF UPPER(substr(p_cmd,1,3)) ='RSH' THEN
lv_shell := 'rsh';
lv_cmd := substr(p_cmd,4,length(p_cm
ELSE
lv_shell := 'cmd /c';
lv_cmd := p_cmd;
END IF;
-- Execute command
lv_success := fn_exec_cmd(lv_shell,lv_cm
IF tab_output.COUNT >0 THEN
FOR x IN tab_output.first..tab_outp
tab_cmdoutput(lv_output_co
END LOOP;
lv_output_count := tab_cmdoutput.COUNT;
END IF;
RETURN lv_success;
I did not have this issue before. I created a collection as:
CREATE OR REPLACE
type CMD_OUTPUT as table of varchar2(255)
/
again, originally this worked. I then started getting
ORA-06531: Reference to uninitialized collection
the only way I could get round this was to initialse the collection
e.g.
IF UPPER(substr(p_cmd,1,3)) ='RSH' THEN
lv_shell := 'rsh';
lv_cmd := substr(p_cmd,4,length(p_cm
ELSE
lv_shell := 'cmd /c';
lv_cmd := p_cmd;
END IF;
tab_output:= CMD_OUTPUT('A','B','C','X'
-- Execute command
lv_success := fn_exec_cmd(lv_shell,lv_cm
the procedure then has no error message but will not execute the command.
I know there are no issues with the java program so it has to be due to me putting
a) tab_output:= CMD_OUTPUT('A','B','C','X'
or
b) not setting up the correct privileges in the database
any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.