PLSQL package

Hi
can anyone help me out with the syntax of how to call this package in a select statment

my package has 3 out parameters , which i need in the select query for a report
but i dont know how to call them in the select query
CREATE OR REPLACE PACKAGE XX_PR_PKG AUTHID CURRENT_USER AS

   PROCEDURE XX_PR_PROC (
      PMTS                OUT      VARCHAR2,
      NCCR               OUT     NUMBER,
      ADJUSTMENT    OUT      NUMBER,
      PROJECT_NUMBER  IN       VARCHAR2,
      TASK_NUMBER          IN       VARCHAR2,
      EMPLOYEE_NUMBER IN VARCHAR2,
      revenue_type in int,
      END_DATE       IN DATE
   );

   END XX_PR_PKG;
/

CREATE OR REPLACE PACKAGE BODY XX_PR_PKG
IS
   
   PROCEDURE XX_PR_PROC (
      PMTS                OUT      VARCHAR2,
      NCCR               OUT     NUMBER,
      ADJUSTMENT    OUT       NUMBER,
      PROJECT_NUMBER  IN       VARCHAR2,
      TASK_NUMBER          IN       VARCHAR2,
      EMPLOYEE_NUMBER IN VARCHAR2,
      REVENUE_TYPE    IN INT,
      END_DATE       IN DATE
      ) 
   IS
   
   V_PMTS        VARCHAR2(5);
   V_NCCR        NUMBER(10);
   V_ADJUSTMENT  NUMBER(10);
   V_PROJECT_NUMBER VARCHAR2(10);
   V_TASK_NUMBER    VARCHAR2(10);
   V_EMPLOYEE_NUMBER VARCHAR2(10);
   V_END_DATE       DATE;
   V_PROJECT_TYPE  VARCHAR2(10);
   V_REVENUE_TYPE  INT;
   
   BEGIN
   V_PROJECT_NUMBER := PROJECT_NUMBER;
   V_TASK_NUMBER := TASK_NUMBER;
   V_EMPLOYEE_NUMBER := EMPLOYEE_NUMBER;
   V_END_DATE   := END_DATE;
   V_REVENUE_TYPE := REVENUE_TYPE;
   
   SELECT PROJECT_TYPE 
   INTO V_PROJECT_TYPE 
   FROM PA_PROJECTS_ALL
   WHERE SEGMENT1 = V_PROJECT_NUMBER;
   
   V_PMTS := sourcingpercentage (V_PROJECT_NUMBER || '-'
                                    || V_TASK_NUMBER,
                                    V_EMPLOYEE_NUMBER
                                   );
                                   
   V_ADJUSTMENT := ADJUSTMENT (V_PROJECT_NUMBER || '-'
                                    || V_TASK_NUMBER,
                                    V_EMPLOYEE_NUMBER, V_END_DATE);
                                    
    IF V_PROJECT_TYPE LIKE 'T_M%' THEN
            IF V_REVENUE_TYPE = 1 THEN
                   V_NCCR := NCCR1(V_PROJECT_NUMBER ,TO_NUMBER (REGEXP_SUBSTR (V_TASK_NUMBER,
                                                          '[0-9]+$'
                                                         )), V_END_DATE);
                    ELSIF V_REVENUE_TYPE = 2 THEN
                            V_NCCR := NCCR2(V_PROJECT_NUMBER ,TO_NUMBER (REGEXP_SUBSTR (V_TASK_NUMBER,
                                                          '[0-9]+$'
                                                         )), V_END_DATE);
                                   ELSE 
                                        V_NCCR := NCCR3(V_PROJECT_NUMBER ,TO_NUMBER (REGEXP_SUBSTR (V_TASK_NUMBER,
                                                          '[0-9]+$'
                                                         )), V_END_DATE);
                                                         
                               END IF;
    ELSIF V_PROJECT_TYPE = 'Fixed Price' THEN
            IF V_REVENUE_TYPE = 1 THEN
                   V_NCCR := NCCR5(V_PROJECT_NUMBER ,TO_NUMBER (REGEXP_SUBSTR (V_TASK_NUMBER,
                                                          '[0-9]+$'
                                                         )), V_END_DATE);
                    ELSIF V_REVENUE_TYPE = 2 THEN
                            V_NCCR := NCCR6(V_PROJECT_NUMBER ,TO_NUMBER (REGEXP_SUBSTR (V_TASK_NUMBER,
                                                          '[0-9]+$'
                                                         )), V_END_DATE);
                                   ELSE 
                                        V_NCCR := NCCR7(V_PROJECT_NUMBER ,TO_NUMBER (REGEXP_SUBSTR (V_TASK_NUMBER,
                                                          '[0-9]+$'
                                                         )), V_END_DATE);
                                                         
                               END IF;
                               
     END IF;
     
     EXCEPTION WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('ERROR IS :'||SQLERRM);
     END XX_PR_PROC;
     
     END XX_PR_PKG;
     /

Open in new window

iamtechnicalAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
You might think about creating a function that in turn executes the procedure.
0
 
sdstuberCommented:
you can't

you can only call FUNCTIONS from a SQL statement,  not procedures

and only functions that do NOT have out parameters.
0
 
sdstuberCommented:
you'd have to create multiple functions,  or create some sort of record type to return the 3 outputs.
0
 
slightwv (䄆 Netminder) Commented:
true. It all depends on how the three outputs need to be returned.  Maybe even a pipelined function.
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.

All Courses

From novice to tech pro — start learning today.