SQL> set serveroutput on SQL> run 1 declare 2 v_type varchar2(40):='object_type'; 3 v_name varchar2(40):='ojject_name'; 4 v_str varchar2(60):=ltrim(rtrim('ALTER '||ltrim(rtrim(v_type))|| 5 ' '||ltrim(rtrim(v_name))||' compile;')); 6 begin 7 dbms_output.put_line(v_str); 8* end; ALTER object_type ojject_name compile;PL/SQL 过程已成功完成。SQL> 打印出来的结果没错啊! 我用此值查询一下也没有错误啊!不知道是怎么回事!
我用这种语句查询是没问题的: SQL> select 'alter '||object_type||' '||object_name||' compile;' 2 from user_objects where object_type in ('VIEW','TRIGGER','PROCEDURE', 3 'FUNCTION','PACKAGE','PACKAGE BODY');'ALTER'||OBJECT_TYPE||''||OBJECT_NAME||'COMPILE;' -------------------------------------------------------------------------------- alter PROCEDURE AAA compile; alter FUNCTION F_GET_TTSBZ compile; alter FUNCTION F_ISDIGIT compile; alter FUNCTION F_PAR compile; alter PROCEDURE P_COMMPILE compile; alter PROCEDURE P_COMPILE compile; alter PROCEDURE P_RECOMPILE compile; alter PROCEDURE TEST1 compile; alter PROCEDURE TEST_NUM compile;已选择9行。SQL>
SQL> set serverout on 13 for c_re in mycursor loop 14 execute immediate c_re.v_str; 15 end loop; 先改为 13 for c_re in mycursor loop 14 dbms_output.put_line(c_re.v_str); 15 end loop;检查一下结果输出是什么
open mycursor for select 'alter '||object_type||' '||object_name||' compile;' from user_objects where object_type in ('VIEW','TRIGGER','PROCEDURE', 'FUNCTION','PACKAGE','PACKAGE BODY');
我把它改成如下形式是没有错误的: 1 create or replace procedure p_recompile 2 is 3 --v_type varchar2(40):='object_type'; 4 --v_name varchar2(40):='ojject_name'; 5 --v_str varchar2(60):=ltrim(rtrim('ALTER '||ltrim(rtrim(v_type))||' '||ltrim(rtrim --(v_name 6 v_success varchar2(60):='compiled no successful!'; 7 cursor mycursor is 8 select 'alter '||object_type||' '||object_name||' compile;' as v_str 9 from user_objects where object_type in ('VIEW','TRIGGER','PROCEDURE', 10 'FUNCTION','PACKAGE','PACKAGE BODY'); 11 begin 12 for c_re in mycursor loop 13 --execute immediate c_re.v_str; 14 dbms_output.put_line(c_re.v_str); 15 end loop; 16 v_success :='compiled successfully!'; 17 dbms_output.put_line(v_success); 18* end p_recompile; 19 /过程已创建。SQL> execute p_recompile; alter FUNCTION F_GET_TTSBZ compile; alter FUNCTION F_ISDIGIT compile; alter PROCEDURE P_COMPILE compile; alter PROCEDURE P_RECOMPILE compile; compiled successfully!PL/SQL 过程已成功完成。SQL> 但是如下形式就报错: 1 create or replace procedure p_recompile 2 is 3 --v_type varchar2(40):='object_type'; 4 --v_name varchar2(40):='ojject_name'; 5 --v_str varchar2(60):=ltrim(rtrim('ALTER '||ltrim(rtrim(v_type))||' '||ltrim(rtrim--(v_name)) 6 v_success varchar2(60):='compiled no successful!'; 7 cursor mycursor is 8 select 'alter '||object_type||' '||object_name||' compile;' as v_str 9 from user_objects where object_type in ('VIEW','TRIGGER','PROCEDURE', 10 'FUNCTION','PACKAGE','PACKAGE BODY'); 11 begin 12 for c_re in mycursor loop 13 execute immediate c_re.v_str; 14 --dbms_output.put_line(c_re.v_str); 15 end loop; 16 v_success :='compiled successfully!'; 17 dbms_output.put_line(v_success); 18* end p_recompile; 19 /过程已创建。SQL> execute p_recompile BEGIN p_recompile; END;* ERROR 位于第 1 行: ORA-00911: 无效字符 ORA-06512: 在"ZZY.P_RECOMPILE", line 13 ORA-06512: 在line 1 SQL> 不知道是什么原因!
create or replace procedure p_recompile is str varchar2(200); cursor mycursor is select object_type,object_name from user_objects where object_type in ('VIEW','TRIGGER','PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY'); begin for cur in mycursor loop str:='alter '||cur.object_type||' '||cur.object_name||' compile'; execute immediate str; end loop;
SQL> run
1 declare
2 v_type varchar2(40):='object_type';
3 v_name varchar2(40):='ojject_name';
4 v_str varchar2(60):=ltrim(rtrim('ALTER '||ltrim(rtrim(v_type))||
5 ' '||ltrim(rtrim(v_name))||' compile;'));
6 begin
7 dbms_output.put_line(v_str);
8* end;
ALTER object_type ojject_name compile;PL/SQL 过程已成功完成。SQL>
打印出来的结果没错啊!
我用此值查询一下也没有错误啊!不知道是怎么回事!
SQL> select 'alter '||object_type||' '||object_name||' compile;'
2 from user_objects where object_type in ('VIEW','TRIGGER','PROCEDURE',
3 'FUNCTION','PACKAGE','PACKAGE BODY');'ALTER'||OBJECT_TYPE||''||OBJECT_NAME||'COMPILE;'
--------------------------------------------------------------------------------
alter PROCEDURE AAA compile;
alter FUNCTION F_GET_TTSBZ compile;
alter FUNCTION F_ISDIGIT compile;
alter FUNCTION F_PAR compile;
alter PROCEDURE P_COMMPILE compile;
alter PROCEDURE P_COMPILE compile;
alter PROCEDURE P_RECOMPILE compile;
alter PROCEDURE TEST1 compile;
alter PROCEDURE TEST_NUM compile;已选择9行。SQL>
14 execute immediate c_re.v_str;
15 end loop;
先改为
13 for c_re in mycursor loop
14 dbms_output.put_line(c_re.v_str);
15 end loop;检查一下结果输出是什么
select 'alter '||object_type||' '||object_name||' compile;'
from user_objects where object_type in ('VIEW','TRIGGER','PROCEDURE',
'FUNCTION','PACKAGE','PACKAGE BODY');
2 is
3 --v_type varchar2(40):='object_type';
4 --v_name varchar2(40):='ojject_name';
5 --v_str varchar2(60):=ltrim(rtrim('ALTER '||ltrim(rtrim(v_type))||' '||ltrim(rtrim --(v_name
6 v_success varchar2(60):='compiled no successful!';
7 cursor mycursor is
8 select 'alter '||object_type||' '||object_name||' compile;' as v_str
9 from user_objects where object_type in ('VIEW','TRIGGER','PROCEDURE',
10 'FUNCTION','PACKAGE','PACKAGE BODY');
11 begin
12 for c_re in mycursor loop
13 --execute immediate c_re.v_str;
14 dbms_output.put_line(c_re.v_str);
15 end loop;
16 v_success :='compiled successfully!';
17 dbms_output.put_line(v_success);
18* end p_recompile;
19 /过程已创建。SQL> execute p_recompile;
alter FUNCTION F_GET_TTSBZ compile;
alter FUNCTION F_ISDIGIT compile;
alter PROCEDURE P_COMPILE compile;
alter PROCEDURE P_RECOMPILE compile;
compiled successfully!PL/SQL 过程已成功完成。SQL>
但是如下形式就报错: 1 create or replace procedure p_recompile
2 is
3 --v_type varchar2(40):='object_type';
4 --v_name varchar2(40):='ojject_name';
5 --v_str varchar2(60):=ltrim(rtrim('ALTER '||ltrim(rtrim(v_type))||' '||ltrim(rtrim--(v_name))
6 v_success varchar2(60):='compiled no successful!';
7 cursor mycursor is
8 select 'alter '||object_type||' '||object_name||' compile;' as v_str
9 from user_objects where object_type in ('VIEW','TRIGGER','PROCEDURE',
10 'FUNCTION','PACKAGE','PACKAGE BODY');
11 begin
12 for c_re in mycursor loop
13 execute immediate c_re.v_str;
14 --dbms_output.put_line(c_re.v_str);
15 end loop;
16 v_success :='compiled successfully!';
17 dbms_output.put_line(v_success);
18* end p_recompile;
19 /过程已创建。SQL> execute p_recompile
BEGIN p_recompile; END;*
ERROR 位于第 1 行:
ORA-00911: 无效字符
ORA-06512: 在"ZZY.P_RECOMPILE", line 13
ORA-06512: 在line 1
SQL>
不知道是什么原因!
8 select 'alter '||object_type||' '||object_name||' compile;' 试试改成
7 cursor mycursor is
8 select 'alter '||object_type||' '||object_name||' compile'
str varchar2(200);
cursor mycursor is select object_type,object_name from user_objects
where object_type in ('VIEW','TRIGGER','PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');
begin
for cur in mycursor loop
str:='alter '||cur.object_type||' '||cur.object_name||' compile';
execute immediate str;
end loop;
dbms_output.put_line('compiled successfully!');
end;