我想写一个重新编译无效触发器的存储过程,代码如下:CREATE OR REPLACE PROCEDURE COMPILE_INVALID_TRIGGERS
(p_owner varchar2)
asstr_sql varchar2(200); declare cursor bcur is select object_name from dba_objects where status = 'INVALID' and object_type = 'TRIGGER' and owner=upper(p_owner);
brec bcur%ROWTYPE;begin
open bcur;
loop
fetch bcur into brec;
exit when bcur%NOTFOUND;
str_sql := 'alter trigger ' || p_owner || '.' || bcur.object_name || ' compile';
begin
execute immediate str_sql;
exception
--When Others Then Null;
when OTHERS Then
dbms_output.put_line(sqlerrm);
end;
end loop;
close bcur;
end;编译时出现如下错误:
行号= 6 列号= 1 错误文本= PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor The symbol "begin" was substituted for "DECLARE" to continue.
行号= 24 列号= 4 错误文本= PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe 我对pl/sql编程不熟,请帮我看看错在哪里了,谢谢!
(p_owner varchar2)
asstr_sql varchar2(200); declare cursor bcur is select object_name from dba_objects where status = 'INVALID' and object_type = 'TRIGGER' and owner=upper(p_owner);
brec bcur%ROWTYPE;begin
open bcur;
loop
fetch bcur into brec;
exit when bcur%NOTFOUND;
str_sql := 'alter trigger ' || p_owner || '.' || bcur.object_name || ' compile';
begin
execute immediate str_sql;
exception
--When Others Then Null;
when OTHERS Then
dbms_output.put_line(sqlerrm);
end;
end loop;
close bcur;
end;编译时出现如下错误:
行号= 6 列号= 1 错误文本= PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor The symbol "begin" was substituted for "DECLARE" to continue.
行号= 24 列号= 4 错误文本= PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe 我对pl/sql编程不熟,请帮我看看错在哪里了,谢谢!
行号= 4 列号= 64 错误文本= PL/SQL: ORA-00942: table or view does not exist
行号= 4 列号= 40 错误文本= PL/SQL: SQL Statement ignored
行号= 4 列号= 32 错误文本= PLS-00341: declaration of cursor 'BCUR' is incomplete or malformed
行号= 5 列号= 6 错误文本= PL/SQL: Item ignored
行号= 10 列号= 25 错误文本= PLS-00320: the declaration of the type of this expression is incomplete or malformed
行号= 10 列号= 9 错误文本= PL/SQL: SQL Statement ignored
行号= 12 列号= 63 错误文本= PLS-00225: subprogram or cursor 'BCUR' reference is out of scope
行号= 12 列号= 9 错误文本= PL/SQL: Statement ignored
这里不能这么定义,不能用游标的%ROWTYPE。
这里因为只搜出来一个 object_name,所以你可以定义个dba_objects.object_name%TYPE的变量
(p_owner varchar2)
asstr_sql varchar2(200);cursor bcur is select object_name from dba_objects where status = 'INVALID' and object_type = 'TRIGGER' and owner=upper(p_owner);
brec dba_objects.object_name%TYPE;begin
open bcur;
loop
fetch bcur into brec;
exit when bcur%NOTFOUND;
str_sql := 'alter trigger ' || p_owner || '.' || brec || ' compile';
begin
execute immediate str_sql;
exception when OTHERS Then
dbms_output.put_line(sqlerrm);
end;
end loop;
close bcur;
end;
/
(p_owner varchar2)
as str_sql varchar2(200); cursor bcur is select object_name from dba_objects where status = 'INVALID' and object_type = 'TRIGGER' and owner=upper(p_owner);
brec dba_objects.object_name%TYPE; begin
open bcur;
loop
fetch bcur into brec;
exit when bcur%NOTFOUND;
str_sql := 'alter trigger ' || p_owner || '.' || brec || ' compile';
begin
execute immediate str_sql;
exception when OTHERS Then
dbms_output.put_line(sqlerrm);
end;
end loop;
close bcur;
end;错误如下:
行号= 6 列号= 40 错误文本= PL/SQL: ORA-00942: table or view does not exist
行号= 6 列号= 16 错误文本= PL/SQL: SQL Statement ignored
行号= 7 列号= 6 错误文本= PLS-00201: identifier 'DBA_OBJECTS' must be declared
行号= 7 列号= 6 错误文本= PL/SQL: Item ignored
行号= 12 列号= 25 错误文本= PLS-00320: the declaration of the type of this expression is incomplete or malformed
行号= 12 列号= 9 错误文本= PL/SQL: SQL Statement ignored
行号= 14 列号= 58 错误文本= PLS-00320: the declaration of the type of this expression is incomplete or malformed
行号= 14 列号= 9 错误文本= PL/SQL: Statement ignored
2、其次,你是用mysql数据库的吧,创建过程的语句写的也有些不对,
CREATE OR REPLACE PROCEDURE COMPILE_INVALID_TRIGGERS (p_owner varchar2)
AS
str_sql varchar2 (200); CURSOR bcur
IS
SELECT object_name
FROM sys.dba_objects
WHERE status = 'INVALID'
AND object_type = 'TRIGGER'
AND owner = UPPER (p_owner);
BEGIN FOR e IN bcur
LOOP
str_sql :=
'alter trigger ' || p_owner || '.' || e.object_name || ' compile';
BEGIN
EXECUTE IMMEDIATE str_sql;
EXCEPTION
--When Others Then Null;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
END LOOP;
END;
这个是我改的,我执行成功了,你看看吧!
(1)当前用户是dba权限,在命令行下执行 select object_name from dba_objects没问题。
(2)不是mysql,是oracle。
GRANT SELECT ON SYS.DBA_OBJECTS TO user_a;
然后在user_a下执行;
我先说错了,我意思是你是不是经常用的mysql数据库啊,因为我以前就遇到mysql和oracle在declare方面存在不同的情况。