I sm getting error:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.CHANGE_SSAN", line 127
ORA-06512: at line 2
Process exited.
on this line :
alter_constraints_disable(hosm_list.hosm);
This is the procedure:
PROCEDURE alter_constraints_disable (hosm_cd IN VARCHAR2)
IS
BEGIN
for constraint_list IN (select table_name, constraint_name
from dba_constraints
where owner=hosm_cd
and constraint_name in ('List of constraints removed for space.'))
LOOP
execute immediate 'alter table '||hosm_cd||'.'||constraint_list.table_name||' DISABLE constraint '||constraint_list.constraint_name;
END LOOP;
END;
The issue appears to be coming from the next line instead of the call to alter_constraints_disable.
execute immediate 'select person_id bulk collect into dup_ssan from '||hosm_list.hosm||'.dod_per order by 1 asc';
Is there something I am missing about bulk collect
Better exception handling will uncover exactly what is causing the error. Run this modified version and then post the output.
CREATE OR REPLACE PROCEDURE alter_constraints_disable (hosm_cd IN VARCHAR2)
IS
BEGIN
for constraint_list IN (select table_name, constraint_name
from dba_constraints
where owner=hosm_cd
and constraint_name in ('List of constraints removed for space.'))
LOOP
DECLARE
v_sql varchar2(32767);
BEGIN
execute immediate 'alter table '||hosm_cd||'.'||constraint_list.table_name||
' DISABLE constraint '||constraint_list.constraint_name;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error with '||constraint_list.table_name||'.'||
constraint_list.constraint_name);
v_sql := dbms_metadata.get_ddl('TABLE', constraint_list.table_name, hosm_cd);
dbms_output.put_line('Table that caused the error:'||chr(10)||v_sql);
raise;
END;
END LOOP;
END;
/
UPDATE
The bulk collect into
should not be inside the dynamic SQL string. Here's an example:
create table dod_per as select level person_id from dual connect by level <= 100;
declare
type dup_ssan_type is table of number;
dup_ssan dup_ssan_type;
v_owner varchar2(30) := user;
begin
execute immediate 'select person_id from '||v_owner||'.dod_per order by 1 asc'
bulk collect into dup_ssan;
end;
/
DBA_CONSTRAINTS is only available to users with DBA role. Try with ALL_CONSTRAINTS or USER_CONSTRAINTS. Also make sure that the user executing the procedure has the ALTER CONSTRAINT privilege.
What version of Oracle are you using? If it's express edition, then some features will not be enabled because you need a license, but disabling/enabling constraints is not one of them as far as I remember.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With