Newbie Procedure Problem

Hi there, I'm new to PL/SQL and have been given the following procedure:

create or replace procedure otime (hoursworked number) as
overtimehours number;
normalhours number;
message varchar2(30);
begin
normalhours := 35;
if hoursworked > normalhours then
overtimehours := hoursworked - normalhours;
message := 'Overtime Hours worked = ';
dbms_output.put_line(message);
dbms_output.put_line(overtimehours);
else
message := 'No Overtime!';
dbms_output.put_line(message);
end if;
end;

it compiles OK, but when I enter the command 'EXECUTE otime', I get he following error:

BEGIN otime; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'OTIME'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I am using SQL *Plus on Oracle 9i on Windows XP.

I haven't created tables to use with this procedure as I wasn't sure if it
was necessary.

Also, how would I get the data from a column called hours in a table called
EMP, when using a procedure?

Many thanks,
James//1
I think execute works when you call a procedure from another procedure.

You may try this, save your procedure in file say for eg
procedure_otime.sql and then run at the sql prompt as

>start procedure_otime.sql;

or

>@.procedure_otime.sql;

to display errors use show errors" at sql prompt

//2

To get the data from a column called hours in a table called
EMP, when using a procedure

v_hours EMP.hours%TYPE; /* assuming table is created */
BEGIN
SELECT hours INTO v_hours FROM EMP;
DBMS_OUTPUT.PUT_LINE(v_hours);
END;|||Try 'exec otime 42', you must pass one parameter to your proc.

Originally posted by donnie_darko
Hi there, I'm new to PL/SQL and have been given the following procedure:

create or replace procedure otime (hoursworked number) as
overtimehours number;
normalhours number;
message varchar2(30);
begin
normalhours := 35;
if hoursworked > normalhours then
overtimehours := hoursworked - normalhours;
message := 'Overtime Hours worked = ';
dbms_output.put_line(message);
dbms_output.put_line(overtimehours);
else
message := 'No Overtime!';
dbms_output.put_line(message);
end if;
end;

it compiles OK, but when I enter the command 'EXECUTE otime', I get he following error:

BEGIN otime; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'OTIME'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I am using SQL *Plus on Oracle 9i on Windows XP.

I haven't created tables to use with this procedure as I wasn't sure if it
was necessary.

Also, how would I get the data from a column called hours in a table called
EMP, when using a procedure?

Many thanks,
James|||Originally posted by ndu35
Try 'exec otime 42', you must pass one parameter to your proc.

I tried this and I received the following error:

BEGIN otime 42; END;

*
ERROR at line 1:
ORA-06550: line 1, column 13:
PLS-00103: Encountered the symbol "42" when expecting one of the following:
:= . ( @. % ;
The symbol ":=" was substituted for "42" to continue.

Then I entered 'execute otime(45) and received the following:

"PL/SQL procedure successfully completed."

Which isn't correct as it should produce a message.|||ok I seem to have sorted this problem now. I hadn't input the command 'SET SERVEROUTPUT ON' and it worked when I enter the command 'EXECUTE otime(45)'. Thanks for the help.