• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

exception handlers

CREATE OR REPLACE FUNCTION sourcingpercentage (
   project_number   IN   VARCHAR2,
   employee         IN   VARCHAR2
)
   RETURN VARCHAR2
IS
   v_project_number       VARCHAR2 (15);
   v_employee             VARCHAR2 (15);
   v_sourcingpercentage   VARCHAR2 (10);
BEGIN
   v_project_number := project_number;
   v_employee := employee;

   SELECT spc
     INTO v_sourcingpercentage
     FROM projectsourcings
    WHERE project = v_project_number AND employee = v_employee;

   RETURN v_sourcingpercentage;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      SELECT es.spc * s.spc
        INTO v_sourcingpercentage
        FROM employeescourcings es, sourcing s
       WHERE s.employee = es.employee
       and s.project = v_project_number;

      RETURN v_sourcingpercentage;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      v_sourcingpercentage := NULL;
      RETURN v_sourcingpercentage;
END sourcingpercentage;
/


what is wrong with the syntax, when i tried to compile it toad is throwing error saying
EXCEPTION is not a valid identifer
0
iamtechnical
Asked:
iamtechnical
  • 5
  • 3
1 Solution
 
sdstuberCommented:
you should only have  one exception section per block
0
 
sdstuberCommented:
if you were trying to nest the second exception within the first one
then you need to create a nested block
CREATE OR REPLACE FUNCTION sourcingpercentage(project_number IN VARCHAR2, employee IN VARCHAR2)
    RETURN VARCHAR2
IS
    v_project_number       VARCHAR2(15);
    v_employee             VARCHAR2(15);
    v_sourcingpercentage   VARCHAR2(10);
BEGIN
    v_project_number  := project_number;
    v_employee        := employee;

    SELECT spc
      INTO v_sourcingpercentage
      FROM projectsourcings
     WHERE project = v_project_number AND employee = v_employee;

    RETURN v_sourcingpercentage;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        BEGIN
            SELECT es.spc * s.spc
              INTO v_sourcingpercentage
              FROM employeescourcings es, sourcing s
             WHERE s.employee = es.employee AND s.project = v_project_number;

            RETURN v_sourcingpercentage;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                v_sourcingpercentage  := NULL;
                RETURN v_sourcingpercentage;
        END;
END sourcingpercentage;
/

Open in new window

0
 
iamtechnicalAuthor Commented:
/* Formatted on 2011/04/19 12:26 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FUNCTION sourcingpercentage (
   project_number   IN   VARCHAR2,
   employee         IN   VARCHAR2
)
   RETURN VARCHAR2
IS
   v_project_number       VARCHAR2 (15);
   v_employee             VARCHAR2 (15);
   v_sourcingpercentage   VARCHAR2 (10);
BEGIN
   v_project_number := project_number;
   v_employee := employee;

   SELECT spc
     INTO v_sourcingpercentage
     FROM projectsourcings
    WHERE project = v_project_number AND employee = v_employee;

   RETURN v_sourcingpercentage;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      SELECT es.spc * s.spc
        INTO v_sourcingpercentage
        FROM employeescourcings es, sourcing s
       WHERE s.employee = es.employee
       and s.project = v_project_number;

      RETURN v_sourcingpercentage;
   WHEN OTHERS
   THEN
      v_sourcingpercentage := NULL;
      RETURN v_sourcingpercentage;
END sourcingpercentage;
/

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
You have two exception handlers in the same PL/SQL block.

You need to nest pl/sql blocks.
CREATE OR REPLACE FUNCTION sourcingpercentage (
   project_number   IN   VARCHAR2,
   employee         IN   VARCHAR2
)
   RETURN VARCHAR2
IS
   v_project_number       VARCHAR2 (15);
   v_employee             VARCHAR2 (15);
   v_sourcingpercentage   VARCHAR2 (10);
BEGIN
   v_project_number := project_number;
   v_employee := employee;
   SELECT spc
     INTO v_sourcingpercentage
     FROM projectsourcings
    WHERE project = v_project_number AND employee = v_employee;

   RETURN v_sourcingpercentage;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
     begin
      SELECT es.spc * s.spc
        INTO v_sourcingpercentage
        FROM employeescourcings es, sourcing s
       WHERE s.employee = es.employee
       and s.project = v_project_number;

      RETURN v_sourcingpercentage;
   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      v_sourcingpercentage := NULL;
      RETURN v_sourcingpercentage;
    end;
END sourcingpercentage;
/

Open in new window

0
 
iamtechnicalAuthor Commented:
i got the solution by myself
0
 
sdstuberCommented:
are you sure your solution is correct?

your second post does NOT capture errors from the select found in the WHEN NO_DATA_FOUND exception.
0
 
sdstuberCommented:
if you were not trying to capture errors in a nested fashion
then your second post simply implements my original suggestion by consolidating to a single exception section
0
 
sdstuberCommented:
iamtechnical,

given that the accepted answer is essentially the same as what was posted earlier, you should have accepted the first post.

I have asked for moderator review
0
 
iamtechnicalAuthor Commented:
@alias99

yeah, will sure do....thanks for the information

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now