Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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