[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PLSQL package

Posted on 2011-05-12
4
Medium Priority
?
260 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:iamtechnical
  • 2
  • 2
4 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35747063
you can't

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

and only functions that do NOT have out parameters.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 35747085
You might think about creating a function that in turn executes the procedure.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35747151
you'd have to create multiple functions,  or create some sort of record type to return the 3 outputs.
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 35747800
true. It all depends on how the three outputs need to be returned.  Maybe even a pipelined function.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question