This discussion is archived
2 Replies Latest reply: Jan 14, 2013 8:04 AM by 977296 RSS

error messages help need asap!!!!

974851 Newbie
Currently Being Moderated
hi this is my code and I need help with what i need to do and how to do it to get rid of the error messages

CREATE OR REPLACE FUNCTION
no_of_task_types (x NUMBER)
RETURN NUMBER IS

my_val NUMBER;

BEGIN
SELECT COUNT(distinct t.task_type_no)
INTO my_val
FROM employee e, assignment a, task t
WHERE e.employee_id = x
AND e.employee_no = a.employee_no
AND t.task_id = a.task_id
RETURN tasktotal;

END;
/

SHOW ERRORS



these are the error messages:

Warning: Function created with compilation errors.
Errors for FUNCTION NO_OF_TASK_TYPES:

8/1     PL/SQL: SQL Statement ignored
13/27     PL/SQL: ORA-00933: SQL command not properly ended
  • 1. Re: error messages help need asap!!!!
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!

    Don't say things like "urgent" or "asap" (as in your title, "error messages help need asap!!!!"). It's rude.
    971848 wrote:
    hi this is my code and I need help with what i need to do and how to do it to get rid of the error messages

    CREATE OR REPLACE FUNCTION
    no_of_task_types (x NUMBER)
    RETURN NUMBER IS

    my_val NUMBER;

    BEGIN
    SELECT COUNT(distinct t.task_type_no)
    INTO my_val
    FROM employee e, assignment a, task t
    WHERE e.employee_id = x
    AND e.employee_no = a.employee_no
    AND t.task_id = a.task_id
    RETURN tasktotal;

    END;
    /

    SHOW ERRORS



    these are the error messages:

    Warning: Function created with compilation errors.
    Errors for FUNCTION NO_OF_TASK_TYPES:

    8/1     PL/SQL: SQL Statement ignored
    13/27     PL/SQL: ORA-00933: SQL command not properly ended
    It looks like you're missing a semicolon at the end of line 13; that's why the statement beginning at line 8 can't be understood.

    Also, you store a number in my_val, but never use that number, and you have a variable called tasktotal that's never defined. Should my_val and tasktotal be the same variable?
    Perhaps this is what you want:
    CREATE OR REPLACE FUNCTION
           no_of_task_types (x NUMBER)
    RETURN NUMBER IS
     
        tasktotal   NUMBER;
    
    BEGIN
        SELECT  COUNT(distinct t.task_type_no)
        INTO    tasktotal
        FROM    employee e, assignment a, task t
        WHERE   e.employee_id = x
        AND     e.employee_no = a.employee_no
        AND     t.task_id        = a.task_id;          -- ; at end is important
    
        RETURN tasktotal;
     
    END;
    /
    This question doesn't have anything to do with SQL*Plus, so maybe the SQL*Plus forum isn't the best place for it. This is strictly a PL/SQL problem; in the future, post questions like this in the SQL and PL/SQL. The FAQ page for that forum, {message:id=9360002} , can really help you.
  • 2. Re: error messages help need asap!!!!
    977296 Newbie
    Currently Being Moderated
    CREATE OR REPLACE FUNCTION no_of_task_types (x NUMBER)

    RETURN NUMBER IS

    my_val NUMBER;

    BEGIN
         SELECT COUNT(distinct t.task_type_no) INTO my_val
         FROM employee e, assignment a, task t
         WHERE      e.employee_id = x AND
              e.employee_no = a.employee_no AND
              t.task_id = a.task_id; -- here you need to end the sql command

    RETURN my_val; -- here you need to pass my_val but not tasktotal which is not defined and assigned the Function
    END;
    /


    Try this, at least you will suppress one error.

    Edited by: ASPERANT on Jan 14, 2013 8:02 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points