ORA-06530 : reference to uninitialized composite'.

[sudhakar_shinde]

Hi All,

I am getting an error 'ORA-06530 : reference to uninitialized composite'.This occurs very randomly and there is no pattern as such for this error's occurance.

Following is the piece of code..

TYPE array_dec IS TABLE OF varchar2(10)
INDEX BY binary_integer;
proc_array array_dec; -- scanner and executor proc names.
jobid_array array_dec; -- jobid's for scanner and executor.
brokenstatus_array array_dec; -- broken status for scanner and executor.
chandle INTEGER;
scr VARCHAR2(500);
ret_val NUMBER;
n_jobno NUMBER;
v_job_nmbr VARCHAR2(14);
v_proc_name VARCHAR2(40);


BEGIN
proc_array(1) := 'SCAN';
proc_array(2) := 'EXEC';
jobid_array(1) := NULL;
jobid_array(2) := NULL;
brokenstatus_array(1) := NULL;
brokenstatus_array(2) := NULL;
IF Action = 'START' OR Action = 'STARTCHK' THEN
OPEN get_eod_flag;
FETCH get_eod_flag INTO v_eod_in_progress;
IF get_eod_flag%NOTFOUND THEN
RAISE error_fetching_eod_flag;
END IF;
IF v_eod_in_progress = 'Y' and Action <> 'BODSTART' THEN
IF Action = 'STARTCHK' THEN
v_do_stop := TRUE;
ELSE
RAISE eod_in_progress;
END IF;
END IF;
END IF;
/* Get Scanner and executor Job ID */
FOR i IN 1..2 LOOP
OPEN get_job(proc_array(i));
FETCH get_job INTO jobid_array(i);
dbms_output.put_line('jobid' ||jobid_array(i));
IF jobid_array(i) IS NOT NULL THEN
OPEN check_job(jobid_array(i));
FETCH check_job INTO brokenstatus_array(i);
dbms_output.put_line('Broken' || brokenstatus_array(i));
CLOSE check_job;
ELSE
dbms_output.put_line('jobid NOT FOund');
END IF;
CLOSE get_job;
END LOOP;
dbms_output.put_line('Out OF get data');

IF (ACTION = 'START' OR Action = 'STARTCHK' OR Action = 'BODSTART') AND v_do_stop = FALSE AND brokenstatus_array(1) = 'N' AND brokenstatus_array(2) = 'N' THEN
RAISE scheduler_already_running;
ELSIF (Action = 'STOP' OR Action = 'STOPCHK' OR v_do_stop = TRUE) AND brokenstatus_array(1) IS NULL AND brokenstatus_array(1) IS NULL THEN
RAISE scheduler_already_stopped;
END IF;

FOR i IN 1..2 LOOP
dbms_output.put_line('In For LOOP ');
IF jobid_array(i) IS NOT NULL THEN
IF brokenstatus_array(i) IS NOT NULL THEN
scr := 'BEGIN DBMS_JOB.REMOVE ('||jobid_array(i)||'); END;';
DBMS_OUTPUT.PUT_LINE (scr) ;
chandle := dbms_sql.Open_cursor;
dbms_sql.parse(chandle, scr, dbms_sql.v7);
ret_val := dbms_sql.execute(chandle);
dbms_sql.close_cursor(chandle);
END IF;
END IF;
END LOOP;


IF (Action = 'START' OR Action = 'STARTCHK' OR Action = 'BODSTART') AND v_do_stop = FALSE THEN


FOR i IN 1..2 LOOP
OPEN get_proc_parameters(proc_array(i));
FETCH get_proc_parameters INTO v_proc_name,n_frequency;
IF get_proc_parameters%NOTFOUND THEN
RAISE error_fetching_process_info;
END IF;
CLOSE get_proc_parameters;
scr := 'DECLARE n_jobno NUMBER; ret_val INTEGER; BEGIN DBMS_JOB.SUBMIT (n_jobno,'''||v_proc_name||';'',SYSDATE,''SYSDATE + 1/(24*60*60)*'||to_char(n_frequency) ||'''); dbms_pipe.pack_message(n_jobno); ret_val := dbms_pipe.send_message(''mypipe''); END;';
DBMS_OUTPUT.PUT_LINE (scr) ;
chandle := dbms_sql.Open_cursor;
dbms_sql.parse(chandle, scr, dbms_sql.v7);
ret_val := dbms_sql.execute(chandle);
dbms_sql.close_cursor(chandle);
ret_val :=dbms_pipe.receive_message('mypipe');
dbms_pipe.unpack_message(n_jobno);
DBMS_OUTPUT.PUT_LINE ('After execution');
generic.generate_sequence_id('JB',v_job_nmbr,v_retval,v_errmsg);
IF v_retval IS NOT NULL THEN
RAISE sequence_generation_error;
END IF;
END LOOP;

END IF;
COMMIT;
IF v_do_stop = TRUE THEN
RAISE schedule_forcefully_stopped;
END IF;
EXCEPTION
WHEN error_fetching_eod_flag THEN
ROLLBACK;
IF Action <> 'START' THEN
retval := 'P30501';
END IF;
WHEN eod_in_progress THEN
retval := 'P30502';

WHEN scheduler_already_running THEN
IF Action <> 'STARTCHK' and Action <> 'BODSTART' THEN
retval := 'P30503';
END IF;
WHEN scheduler_already_stopped THEN

NULL;
WHEN error_fetching_process_info THEN
retval := 'P30505';
WHEN sequence_generation_error THEN
retval := v_retval;
errmsg := v_errmsg;
WHEN schedule_forcefully_stopped THEN
retval := 'P30506';
WHEN others THEN
retval := 'P30599';
errmsg := generic.get_error_txt;
END;

It goes in others exception (P30599) and halts the program.Please suggest what could be the reason.I suspect this is occuring because n_jobs is not initialised anywhere but I am not sure.Please suggest.

Regards

[Kumar_RP]

Hi,

Usually, you may receive these errors when some of the objects
are not initialized.

For example:
------------

drop table emp;
drop type emp_type;

create type emp_type as object(
name VARCHAR2(30),
address VARCHAR2(30));
/

create table emp of emp_type;

insert into emp
values (emp_type('Kumar', 'Chennai'));

create or replace procedure test as

emp1 emp_type;

begin

select emp.name, emp.address into emp1.name, emp1.address
from emp
where emp.name = 'Kumar';

dbms_output.put_line('emp1.name : '||emp1.name);
dbms_output.put_line('emp1.address : '||emp1.address);

end;

/


SQL> execute test;
begin test; end;

*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at "TEST", line 15
ORA-06512: at line 1


Solution Description
--------------------
You have to initialize the object using a constructor.

After initializing the object 'emp1' as below in the example it worked.

emp1 := emp_type(NULL, NULL);


Changed example:
----------------

set serveroutput on;

drop table emp;
drop type emp_type;

create type emp_type as object(
name VARCHAR2(40),
address VARCHAR2(40));
/

create table emp of emp_type;

insert into emp
values (emp_type('Kumar', 'Chennai'));

create or replace procedure test as

emp1 emp_type;

begin

emp1 := emp_type(NULL, NULL);
select emp.name, emp.address into emp1.name, emp1.address
from emp
where emp.name = 'Kumar';

dbms_output.put_line('emp1.name : '||emp1.name);
dbms_output.put_line('emp1.address : '||emp1.address);

end;
/
show errors;

SQL> execute test;
emp1.name : Kumar
emp1.address : Chennai

PL/SQL procedure successfully completed.


Hope this will help you.

Regards,

[DaPi]

Originally posted by sudhakar_shinde
I am getting an error 'ORA-06530 : reference to uninitialized composite'.This occurs very randomly and there is no pattern as such for this error's occurance.

You don't say what version you are running.
"Randomly" is the clue - it gets you pulling out you hair! I had this under 8.1.6 - that was a bug! It's in Metalink:

Bug No. 1606456
Filed 22-JAN-2001 Updated 23-MAY-2002
Product PL/SQL Product Version 8.1.6
Platform Intel Windows NT Platform Version 4.0 SP6
Database Version 8.1.6.0.0 Affects Platforms Generic
Priority Severe Loss of Service Status Q/A To Development
Base Bug N/A Fixed in Product Version 8.1.7.3

Problem statement:

ORA-6530 WHEN FETCHING ROWS INTO PL/SQL TABLES
---------------------------------------------------------------------

etc etc etc