asked on

ORA-06531: Reference to uninitialized collection

Hi,

I have a procedure that executes os commands
1) pr_runOsCmd uses java in the internal oracle jvm to host out to the os.
It appends the windows start cmd into the cmd line.

I had it working, my db crashed and now I get an ORA-06531: Reference to uninitialized collection when I try to run it.

FUNCTION fn_exec_cmd(p_shell VARCHAR2,p_cmd VARCHAR2,p_output IN OUT cmd_output) RETURN VARCHAR2 as
LANGUAGE JAVA NAME 'com.utils.OSExecutor.executeCommand(java.lang.String,java.lang.String,oracle.sql.ARRAY[]) return java.lang.String';

-----------

cmd_output is declared in the same schema

CREATE OR REPLACE
type CMD_OUTPUT as table of varchar2(255)
/
I have granted synonyms, privileges to all the schemas but still cant get it working.

How do I initialize this collection?

anand_2000v

Hello

Make sure the collection variable is initialized with a constructor which initializes the number of elements which are needed by the procedure.

Example:

create type event_times
as varray(6) of date;

create or replace procedure test as
et event_times;
begin
et := event_times(SYSDATE, NULL, NULL);
et(2) := to_date('13-JAN-95');
dbms_output.put_line('Count : '||et.COUNT);
dbms_output.put_line('Limit : '||et.LIMIT);
et.extend(1,1);
dbms_output.put_line('et(1) : '||et(1));
dbms_output.put_line('et(2) : '||et(2));
dbms_output.put_line('et(3) : '||et(3));
dbms_output.put_line('et(4) : '||et(4));
end;

anand_2000v

with your type use
A:=fn_exec_cmd('ABC','XYZ',CMD_OUTPUT('A','B','C','X','Y','Z'))

joehodge

ASKER

Hi,

------------------------------
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_cmd)-3);
ELSE
       lv_shell := 'cmd /c';
       lv_cmd := p_cmd;
END IF;
-- Execute command
lv_success := fn_exec_cmd(lv_shell,lv_cmd,tab_output);

------------------------------------------
This is how the function works so I'm assuming that lv_success is the constructor?

joehodge

ASKER

lv_success :=fn_exec_cmd(lv_shell,lv_cmd,CMD_OUTPUT('A','B','C','X','Y','Z'))
results in
PLS-00363: expression 'CMD_OUTPUT' cannot be used as an assignment target

and

lv_success :=fn_exec_cmd(lv_shell,lv_cmd,tab_output('A','B','C','X','Y','Z'))
results in
PLS-00316: PL/SQL TABLEs must use a single index

joehodge

ASKER

its nearing hometime so I've just doubled the points!

ASKER CERTIFIED SOLUTION

anand_2000v

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

joehodge

ASKER

job done!

I've awarded you the points.

I didn't need that line of code before so can I initialize the collection outside of the function or do I have to leave it there?

thanks again