Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Unimplemented feature error in stored procedure

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

like image 535
Werbel Avatar asked Oct 17 '25 18:10

Werbel


2 Answers

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;
/
like image 175
Jon Heller Avatar answered Oct 19 '25 10:10

Jon Heller


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.

like image 25
tilley31 Avatar answered Oct 19 '25 11:10

tilley31