Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-24344:success with compilation error

501573Mar 27 2006 — edited Apr 1 2006
I 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 29 2006
Added on Mar 27 2006
5 comments
1,187 views