Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    England
    Posts
    15

    Unanswered: 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

  2. #2
    Join Date
    Sep 2003
    Posts
    71
    //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;

  3. #3
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: Newbie Procedure Problem

    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

  4. #4
    Join Date
    Oct 2003
    Location
    England
    Posts
    15

    Re: Newbie Procedure Problem

    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.

  5. #5
    Join Date
    Oct 2003
    Location
    England
    Posts
    15
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •