oracle - Procedure calling gone bad - Statement Ignored -
i have following procedure insert users in table:
create or replace procedure elr_add_user (i_name in varchar2, i_morada in varchar2, i_birthdate in date, i_country in varchar2, o_id out number, o_error_msg out varchar2) error_null exception; begin if i_name null or i_morada null or i_birthdate null or i_country null raise error_null; end if; o_id := elr_seq_user_id.nextval; if o_id null raise error_null; end if; insert elr_users values (o_id, i_nome, i_morada, i_birthdate, i_country); exception when error_null o_error_msg := 'null fields'; when others o_error_msg := 'unexpected error: '|| sqlerrm; end; /
i think procedure , it's syntax correct. when i'm trying call with:
declare p_name varchar2(50); p_morada varchar2(50); p_birthdate date; p_country varchar2(20); p_id number(20); p_error_msg varchar2(4000); begin elr_add_user('ed warner','cenas street',sysdate, 'china', p_id, p_error_msg); if p_error_msg not null dbms_output.put_line('error: '||p_error_msg); end if; end; /
i following message:
is there wrong calling or procedure itself?
ora-06550
followed pls-00905
compilation error. procedure in invalid state.
recompile procedure, , use show errors complete error details.
show error procedure rms_mm.elr_add_user
or show errors
for example,
sql> create or replace procedure testproc 2 3 vnum number; 4 begin 5 vnum := vanothernum; 6 end; 7 / warning: procedure created compilation errors. sql> execute testproc(); begin testproc(); end; * error @ line 1: ora-06550: line 1, column 7: pls-00905: object example.testproc invalid ora-06550: line 1, column 7: pl/sql: statement ignored sql> show error procedure testproc; errors procedure testproc: line/col error -------- ----------------------------------------------------------------- 5/1 pl/sql: statement ignored 5/9 pls-00201: identifier 'vanothernum' must declared
Comments
Post a Comment