oracle 存储过程中未实现的功能错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17686705/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unimplemented feature error in stored procedure
提问by Werbel
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.
问题似乎来自下一行，而不是对 alter_constraints_disable 的调用。
execute immediate 'select person_id bulk collect into dup_ssan from '||hosm_list.hosm||'.dod_per order by 1 asc';
立即执行“选择 person_id 从 '||hosm_list.hosm||'.dod_per order by 1 asc' 批量收集到 dup_ssan 中；
Is there something I am missing about bulk collect
我对批量收集有什么遗漏吗
回答by Jon Heller
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 intoshould not be inside the dynamic SQL string. Here's an example:
本bulk collect into不应该是动态的SQL字符串内。下面是一个例子：
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;
/
回答by tilley31
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.
DBA_CONSTRAINTS 仅对具有 DBA 角色的用户可用。尝试使用 ALL_CONSTRAINTS 或 USER_CONSTRAINTS。还要确保执行该过程的用户具有 ALTER CONSTRAINT 权限。
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.
您使用的是哪个版本的 Oracle？如果是速成版，则某些功能将不会启用，因为您需要许可证，但据我所知，禁用/启用约束不是其中之一。
回答by DVK
Interestingly enough, I also received this error via the ODP.NET provider when my query had an identifier with an extra trailing double-quote (something like this)
有趣的是，当我的查询有一个带有额外尾随双引号的标识符时，我也通过 ODP.NET 提供程序收到了这个错误（类似这样）
SELECT "Field1" "Alias1", "Field2" "Alias2"
FROM "SomeSchema"."SomeTable"" -- <- extra quote
WHERE "SomeField" = 'SomeCriteria'
Strange error for a simple typo.
一个简单的错字的奇怪错误。
