Re: PL/SQL & ora-6502 bug?

From: Robert T. Bunch <robert.t.bunch_at_cpmx.saic.com>
Date: Thu, 28 Jan 1999 19:23:22 GMT
Message-ID: <36b6b0e9.1375120949@news>

I definitely appreciate everybody's help on this... Here's more info and some of the procedures:

The two tables, TEMP_PARENT and TEMP_CHILD *must* exist prior to compiling or running. These tables are generated via "create table temp_x as select pk from main_table"

In the procedures below, p_Key is the primary key from the parent table, p_Table is the name of the parent table.

There are more procedures that account for tables with composite primary keys, so they will have different numbers in the "having count(*) = 1" clause.

Running it: exec the ONE_CHAR_KEY_PARENT, which generates DDL (done in DO_SQL), then calls ONE_CHAR_KEY_CHILD, which again generates DDL (DO_SQL), then finally calls FIND_ORPHANS to actually do the comparisons.

The procedure dies intermittently within the FIND_ORPHANS procedure before the "dbms_output.putline ('F:2');" troubleshooting line.

Again, thanks in advance for any help on this problem.

---

create or replace package ORPHAN_HUNT is

        procedure FIND_ORPHANS;
        procedure DO_SQL (p_DDLString in varchar2);
        procedure ONE_CHAR_KEY_PARENT (
                p_Key in varchar2,
                p_Table in varchar2);
        procedure ONE_CHAR_KEY_CHILD (
                p_Key in varchar2,
                p_Table in varchar2);

end ORPHAN_HUNT;
/

---

 

create or replace package body ORPHAN_HUNT is  

procedure FIND_ORPHANS is

	cursor v_ChildSelectCursor is
		select CHILD_KEY 
		from TEMP_PARENT, TEMP_CHILD
		where
			PARENT_KEY (+) = CHILD_KEY and
			PARENT_KEY is null;
 
	v_ChildCursor v_ChildSelectCursor%rowtype;
begin
		dbms_output.enable(1000000);
		open v_ChildSelectCursor;
		loop
-- dbms_output.put_line ('F:1');
			fetch v_ChildSelectCursor into v_ChildCursor;
-- dbms_output.put_line ('F:2');  <-- May not get here, ora-06502
			exit when v_ChildSelectCursor%notfound;
			dbms_output.put_line ('F *** Unmatched value found: ' ||
				v_ChildCursor.CHILD_KEY);
		end loop;
		close v_ChildSelectCursor;

end FIND_ORPHANS;  

---

procedure DO_SQL (p_DDLString in varchar2) is

        v_DDLCursor number;
begin

	dbms_output.enable;
	v_DDLCursor := dbms_sql.open_cursor;
	begin
		dbms_sql.parse (v_DDLCursor, 
		p_DDLString, dbms_sql.v7);
	exception
		when others then
			if sqlcode != -942 then
				raise;
			end if;
	end;
	dbms_sql.close_cursor (v_DDLCursor);

end DO_SQL;

---

procedure ONE_CHAR_KEY_PARENT (

	p_Key in varchar2,
	p_Table in varchar2) is

	cursor v_ParentSelectCursor (
		p_Key varchar2,
		p_Table varchar2) is
			select a.table_name 
			from all_tab_columns a, all_tables b
			where
				a.column_name = p_Key and
				a.owner = 'TABLE_MASTER' and
				a.table_name != p_Table and
				a.table_name = b.table_name
			group by a.table_name
			having count(*) = 1
			order by a.table_name;
 
	v_ParentCursor v_ParentSelectCursor%rowtype;
begin
	ORPHAN_HUNT.DO_SQL ('drop table TEMP_PARENT');
	ORPHAN_HUNT.DO_SQL ('create table TEMP_PARENT as select distinct
nvl ('
		|| p_Key
		|| ', ''*'') PARENT_KEY from TABLE_MASTER.'
		|| p_Table);
	ORPHAN_HUNT.DO_SQL ('alter table TEMP_PARENT add constraint

PK_TEMP_PARENT primary key (PARENT_KEY)');  

	open v_ParentSelectCursor (p_Key, p_Table);
	loop
		fetch v_ParentSelectCursor into v_ParentCursor;
		exit when v_ParentSelectCursor%notfound;
		dbms_output.put_line ('Checking: ' ||
v_ParentCursor.TABLE_NAME);
		ONE_CHAR_KEY_CHILD (p_Key, v_ParentCursor.TABLE_NAME);
	end loop;
	close v_ParentSelectCursor;

end ONE_CHAR_KEY_PARENT;

---

procedure ONE_CHAR_KEY_CHILD (

	p_Key in varchar2,
	p_Table in varchar2) is
begin
	ORPHAN_HUNT.DO_SQL ('drop table TEMP_CHILD');
	ORPHAN_HUNT.DO_SQL ('create table TEMP_CHILD as select distinct
nvl ('
		|| p_Key
		|| ', ''*'') CHILD_KEY from TABLE_MASTER.'
		|| p_Table);
	ORPHAN_HUNT.DO_SQL ('alter table TEMP_CHILD add constraint

PK_TEMP_CHILD primary key (CHILD_KEY)');  

        ORPHAN_HUNT.FIND_ORPHANS;
end ONE_CHAR_KEY_CHILD;

---

• Running the procedure, getting an error,
• immediately trying again and it works

SQL> exec ORPHAN_HUNT.ONE_CHAR_KEY_PARENT ('UNT_CD','UNIT_TYPE') Checking: TABLE_A
Checking: TABLE_B
begin orphan_hunt.one_char_key_parent ('UNT_CD','UNIT_TYPE'); end;

*
ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "ORPHAN.ORPHAN_HUNT", line 10
ORA-06512: at "ORPHAN.ORPHAN_HUNT", line 249
ORA-06512: at "ORPHAN.ORPHAN_HUNT", line 59
ORA-06512: at line 1

SQL> exec ORPHAN_HUNT.ONE_CHAR_KEY_PARENT ('UNT_CD','UNIT_TYPE') Checking: TABLE_A
Checking: TABLE_B
F *** Unmatched value found: XXXXX
Checking: TABLE_C

PL/SQL procedure successfully completed.  

"Daniel Clamage" <dclamageNOSPAM_at_telerama.com> wrote:

>Sounds liek you really do have a VALUE_ERROR that you're not locating. One
>technique might be to write to a package variable the line # of the last
>statement executed (sprinkle these assignments liberally). Then in the
>exception handler to trap VALUE_ERROR, write out the last known line. This
>will help you pinpoint the statement suffering the exception. Or run a debug
>on the code from a tool like Platinum or PB. The problem is sporadic because
>it only happens for certain data values, so it seems random.

Received on Thu Jan 28 1999 - 13:23:22 CST