Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2005
    Posts
    6

    Unanswered: How to call this sproc and return OUTs

    Ok... be gentle... I'm an Oracle "newbie", have no clue how to do this, and haven't been able to find a decent example in the Documentation or on the Web (probably not looking for the right thing).

    I have a stored procedure in a package (package name is GenericNPAInfoLibPkg) that looks like this...

    Code:
    PROCEDURE GetRateCenterLataForNpaNxx
    /**********************************************************************************************************************
     Purpose:
     Given at least the first 6 digits of a telephone number and an indication of record source, find and return the
     primary rate center name, State, Country, and LATA.
    
    **********************************************************************************************************************/
    --===== Declare I/O parameters
            (
            piPhoneNumber    IN  VARCHAR2,     --May be a whole NANPA capable phone number, or just the NPA/NXX
            piRecordSource   IN  VARCHAR2      --May be any record source from SvcRateCenter including LIKE's with %
                             DEFAULT 'NNACL',  --When not provided, defaults to Telcordia's LERG rate center name
            poRateCenterName OUT VARCHAR2,     --The found primary rate center name for the given NPA/NXX/RecordSource
            poState          OUT VARCHAR2,     --The found State for the given NPA/NXX/RecordSource
            poCountry        OUT VARCHAR2,     --The found Sountry for the given NPA/NXX/RecordSource
            poLata           OUT VARCHAR2      --The found LATA for the given NPA/NXX/RecordSource
            )
         AS
    
    --===== Declare local variables
            vNpa             VARCHAR2(3); --Will contain the 1st 3 characters of the piPhoneNumber variable
            vNxx             VARCHAR2(3); --Will contain the 2nd 3 characters of the piPhoneNumber variable
    
    BEGIN
            --===== Split out the Npa and NXX from the supplied phone number
                    vNpa := SUBSTR(piPhoneNumber,1,3);
                    vNxx := SUBSTR(piPhoneNumber,4,3);
    
            --===== Populate the output variables using a lookup based on the input parameters.
                 -- Only "Active" rows not found in RateArea 7 "Information and Dial-it" are returned.
                 -- In English, that means no 900, 976, 500, toll free, or other "special" numbers will be found.
             SELECT RateCenterName  , State  , Country  , Lata
               INTO poRateCenterName, poState, poCountry, poLata
               FROM SvcRateCenter
              WHERE NPA = vNpa
                AND NXX = vNxx
                AND RecordSource LIKE piRecordSource
                AND IsActive = 1  --Is not a RateArea 7 "Information and Dial-it" number
                AND ROWNUM  <= 1; --Force a single return in case of duplication in the source table (should never happen)
    
            --======= Handle not found errors
            EXCEPTION
                 WHEN NO_DATA_FOUND
                 THEN BEGIN --Force output variables to NULL just to be sure, then EXIT
                            poRateCenterName := NULL;
                            poState          := NULL;
                            poCountry        := NULL;
                            poLata           := NULL;
                            RETURN;
                      END;
                 WHEN OTHERS
                 THEN RAISE; --Raise the unknown error as if no exception code existed.
    
    END GetRateCenterLataForNpaNxx;
    I need a good example of how to setup for and call this sproc from PL/SQL and use DBMS_OUTPUT to print the values in the OUT variables/parameters. Of course, I don't want to include DBMS_OUTPUT in the sproc above.

    Thanks for the help, folks.

    --Jeff Moden

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's an example based on Scott's schema: I'll display employee's salary.

    First, create a procedure with an OUT parameter (simulating your case; I will not create a package, but the principle is the same. Also, I'll omit exception handling to simplify the procedure):
    Code:
    CREATE OR REPLACE PROCEDURE prc_out_example
      (par_empno IN EMP.empno%TYPE,
       par_salary OUT EMP.sal%TYPE
      )
    IS
    BEGIN
      SELECT sal INTO par_salary
        FROM EMP
        WHERE empno = par_empno;
    END;
    /
    Now the answer to your question: to display an OUT parameter using a PL/SQL block, you'll have to declare a variable which will be used both to store and display OUT parameter's value.
    I'm displaying employees who work in department 10
    Code:
    DECLARE
      l_salary EMP.sal%TYPE;
    BEGIN
      FOR cur_r IN (SELECT empno, ename FROM EMP WHERE deptno = 10)
      LOOP
        prc_out_example (cur_r.empno, l_salary);
        dbms_output.put_line(cur_r.ename ||' gets $' || l_salary);
      END LOOP;
    END;
    /
    
    CLARK gets $2450
    KING gets $5000
    MILLER gets $1300
    
    PL/SQL procedure successfully completed.
    Don't forget to SET SERVEROUTPUT ON before using DBMS_OUTPUT package; otherwise, you won't see anything.

  3. #3
    Join Date
    Aug 2005
    Posts
    6
    Perfect... thanks.

Posting Permissions

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