2 Replies Latest reply: Jan 14, 2013 10:04 AM by 977296 RSS

    error messages help need asap!!!!

    974851
      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
          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
            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