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?
joehodgeAsked:
Who is Participating?
 
anand_2000vConnect With a Mentor Commented:
oh right it's an in out parameter...my mistake in not noting that...
you'll just have to add
tab_output:=CMD_OUTPUT('A','B','C','X','Y','Z');
before
lv_success := fn_exec_cmd(lv_shell,lv_cmd,tab_output);
it should work
0
 
anand_2000vCommented:
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;
0
 
anand_2000vCommented:
with your type use
A:=fn_exec_cmd('ABC','XYZ',CMD_OUTPUT('A','B','C','X','Y','Z'))
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
joehodgeAuthor Commented:
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?
0
 
joehodgeAuthor Commented:

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
0
 
joehodgeAuthor Commented:
its nearing hometime so I've just doubled the points!
0
 
joehodgeAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.