PL/SQL Statement ignored 1

[AgentM]

I am getting the eror "PL/SQL Statement ignored"

on a stored procedure.I can't see any syntax errors. the error is pointed at the line after &quotIS"
Here's the code:
CREATE OR REPLACE PROCEDURE proc(
id IN NUMBER /*NUMBER(10)*/, total_amt_due_in IN NUMBER /*NUMBER(5,2)*/,
overnight_status IN CHAR /*CHAR(1)*/,
total_records_in IN NUMBER /*NUMBER(7)*/

)
IS
BEGIN

If (overnight_status = '1') then
overnight_status := 'Y';
else
overnight_status := 'N';
end if;

INSERT INTO tbl
(
id,
res_id,
total_amt_due,
overnight_status,
total_records
)
VALUES(
seq_id.nextval,
res_id_in,
total_amt_due_in,
overnight_status,
total_records_in
);
END;
/

Any help appreciated

 

[karluk]

Are you familiar with the "show errors" command in SQL*Plus. It will give you the exact error that's causing the problem.

When I try to create your proc (excluding the insert statement), and then show the errors, I get "PLS-00363: expression 'OVERNIGHT_STATUS' cannot be used as an
assignment target". You are trying to change the value of an input-only parameter, which is not allowed.

Probably you can correct this easily by declaring a variable to hold the desired 'Y/N', and using it in your insert statement.

CREATE OR REPLACE PROCEDURE proc(
id IN NUMBER /*NUMBER(10)*/, total_amt_due_in IN NUMBER /*NUMBER(5,2)*/,
overnight_status IN CHAR /*CHAR(1)*/,
total_records_in IN NUMBER /*NUMBER(7)*/

)
IS
insert_status char(1);
BEGIN

If (overnight_status = '1') then
insert_status := 'Y';
else
insert_status := 'N';
end if;

INSERT INTO tbl
(
id,
res_id,
total_amt_due,
overnight_status,
total_records
)
VALUES(
seq_id.nextval,
res_id_in,
total_amt_due_in,
insert_status,
total_records_in
);
END;
/

 

[AgentM]

Thank you
I appreciate it...

 

[AgentM]

Can you give me more details of how to use SHOW ERRORS in a stored procedure?

I am not able to locate much info on that issue at the moment

Thank you

 

[Guest_imported]

Are you running the stored proc on sql*plus?
If yes, then as soon as you receive this error:

Warning: Procedure created with compilation errors.
then type show errors;
and the error message with the accompanying line number will be displayed.
hope this helps.
sokeh