ORA-24344:success with compilation error
501573Mar 27 2006 — edited Apr 1 2006I created the following procedure in sql*plus:
create or replace procedure p_test(tname in varchar2,user_name in varchar2)
AUTHID CURRENT_USER
is
pragma autonomous_transaction;
v_ddlstr varchar2(1000);
begin
v_ddlstr :=
'create or replace trigger trg_valid_' || tname || ' before insert on ' || tname ||'
declare
user_name varchar2(50);
object_name varchar2(50);
user_label number;
object_label number;
BEGIN
select userlabel into user_label from test.ULabel where username=' || user_name ||';
select objectlabel into object_label from test.OLabel where objectname=' || tname ||';
insert into temp values(object_label);
if user_label>object_label then
null;
else
RAISE_APPLICATION_ERROR(-20001,''you can not do that!'');
end if;
end;';
execute immediate v_ddlstr;
end;
/
and it succeed,
and in the test database ,i have create the following structure:
SQL> select * from table1;
A
----------
1
2
SQL> select * from ulabel;
USERNAME USERLABEL
-------------------- ----------
WXY 6
FEELING 3
SQL> select * from olabel;
OBJECTNAME OBJECTLABEL
-------------------- -----------
TABLE1 4
TABLE2 5
but when I execute the following statement:
sql>execute p_test('TABLE1','WXY')
some errors happen:
ORA-24344:success with compilation error
ORA-06512: in "TEST.P_TEST", line 31
ORA-06512: at line 1
then where is the mistake?thank in advance.