9 Replies Latest reply: Feb 26, 2014 1:50 PM by K.S.I. RSS

    pipeline

    1043556

      HI ALL WHILE EXECUTING THE BELOW PKG IM GETTING THE ERROR PLS-00653: aggregate/table functions are not allowed
      HERE I USED THE PIPELINED FUNCTION HOW TO I RESOLVE THIS OR CAN I REMOVE PIPELINE FUNCTION

      CREATE OR REPLACE PACKAGE PKG1 IS

       
        TYPE TY_CUSTGL IS RECORD(
          W_PRODUCT_CD  NUMBER(4),
          W_GL_ACCES_CD VARCHAR2(15),
          W_GL_DESC     VARCHAR2(50),
          W_GL_CUR_CD   VARCHAR2(3),
          W_AC_DB_BAL   NUMBER(18, 3),
          W_AC_CR_BAL   NUMBER(18, 3),
          W_AC_NET_BAL  NUMBER(18, 3),
          W_BC_DB_BAL   NUMBER(18, 3),
          W_BC_CR_BAL   NUMBER(18, 3),
          W_BC_NET_BAL  NUMBER(18, 3),
          W_DB_ACS      NUMBER(6),
          W_CR_ACS      NUMBER(6),
          W_GL_AC_BAL   NUMBER(18, 3),
          W_GL_BC_BAL   NUMBER(18, 3));
        TYPE CUST_GL IS TABLE OF TY_CUSTGL;

        FUNCTION FN_CUSTGL_BAL(V_ENTITY_NUM IN NUMBER, BRANCH_CODE IN NUMBER,
                               V_CURR_CODE IN VARCHAR2, ASON_DATE IN DATE,
                               CUSTOMER_GL IN NUMBER, GL_ACC IN VARCHAR2,W_SUP_FLAG IN NUMBER DEFAULT 0)--S.PREM 03/01/2012
          RETURN CUST_GL
          PIPELINED;
      END PKG1;
      /

      CREATE OR REPLACE PACKAGE BODY PKG1 IS
        few variabls DECLARED
       
        P_RESULT      PKG_CUSTGL.TY_CUSTGL;
       
        FUNCTION FN_CUSTGL_BAL(V_ENTITY_NUM IN NUMBER, BRANCH_CODE IN NUMBER,
                               V_CURR_CODE IN VARCHAR2, ASON_DATE IN DATE,
                               CUSTOMER_GL IN NUMBER, GL_ACC IN VARCHAR2,W_SUP_FLAG IN NUMBER DEFAULT 0)
          RETURN CUST_GL
          PIPELINED IS
          W_BRN NUMBER;
          PROCEDURE VALIDATE_INPUT IS
            V_BCOUNT NUMBER(1);
            V_CCOUNT NUMBER(1);
            V_GCOUNT NUMBER(1);
            W_SQL    VARCHAR2(1000);
          BEGIN    

            IF BRANCH_CODE <> '0'
            THEN
              W_SQL := 'SELECT COUNT(1) FROM MBRN WHERE MBRN.MBRN_ENTITY_NUM =:1
            AND MBRN.MBRN_CODE =:2';
              EXECUTE IMMEDIATE W_SQL
                INTO V_BCOUNT
                USING V_ENTITY_NUM, BRANCH_CODE;
            ELSE
              V_BCOUNT := 1;
            END IF;
            IF GL_ACC <> '0'
            THEN
              W_SQL := 'SELECT COUNT(1) FROM EXTGL WHERE EXTGL.EXTGL_ACCESS_CODE =:1';
              EXECUTE IMMEDIATE W_SQL
                INTO V_GCOUNT
                USING GL_ACC;
            ELSE
              V_GCOUNT := 1;
            END IF;
            IF V_CURR_CODE <> '0'
            THEN
              W_SQL := 'SELECT COUNT(1) FROM CURRENCY WHERE CURRENCY.CURR_CODE =:1';
              EXECUTE IMMEDIATE W_SQL
                INTO V_GCOUNT
                USING V_CURR_CODE;
            ELSE
              V_CCOUNT := 1;
            END IF;
            W_OK := 0;

            IF V_BCOUNT = 0 OR V_CCOUNT = 0 OR V_GCOUNT = 0
            THEN
              W_OK := 0;
            ELSE
              W_OK := 1;
            END IF;

          END VALIDATE_INPUT;
        BEGIN
          W_ACY_BAL     := 0;
          W_LCY_BAL     := 0;
          W_ACY_DB_BAL  := 0;
          W_ACY_CR_BAL  := 0;
          W_LCY_DB_BAL  := 0;
          W_LCY_CR_BAL  := 0;
          W_ACY_NET_BAL := 0;
          W_LCY_NET_BAL := 0;
          W_DB_CNT      := 0;
          W_CR_CNT      := 0;
          V_GL_AC_BAL   := 0;
          V_GL_BC_BAL   := 0;
             VALIDATE_INPUT;
          IF W_OK = 0
          THEN
            RAISE E_USEREXCEP;
          END IF;

          IF GL_ACC = '0'
          THEN
            -- Customer GL Parameter Pass 0 for Non customer GL , 1 for Customer GL , 2 for All GLs
            IF CUSTOMER_GL = '1'
            THEN
              -- Getting Records of All Customer GLs
              FOR PRD IN (SELECT PRODUCT_CODE, PRODUCT_GLACC_CODE,
                                 EXTGL.EXTGL_EXT_HEAD_DESCN
                          FROM PRODUCTS, EXTGL
                          WHERE PRODUCTS.PRODUCT_GLACC_CODE =
                                EXTGL.EXTGL_ACCESS_CODE
                          AND EXTGL.EXTGL_GL_HEAD IN
                                (SELECT GL_NUMBER
                                 FROM GLMAST G
                                 WHERE G.GL_CUST_AC_ALLOWED = '1')
                          ORDER BY PRODUCT_CODE)
              LOOP
                -- Getting All Accounts of the Product
                -- For Getting Distinct Currency Code of for the Products
                FOR CUR IN (SELECT DISTINCT ACNTS_CURR_CODE
                            FROM ACNTS
                            WHERE ACNTS_ENTITY_NUM = V_ENTITY_NUM
                            AND ACNTS_PROD_CODE = PRD.PRODUCT_CODE
                                  AND ACNTS_BRN_CODE =
                                  DECODE(BRANCH_CODE, 0, ACNTS_BRN_CODE, BRANCH_CODE)
                            AND ACNTS_CURR_CODE =
                                  DECODE(V_CURR_CODE, '0', ACNTS_CURR_CODE,
                                      V_CURR_CODE)
                            ORDER BY ACNTS_CURR_CODE)
                LOOP
                  V_GL_AC_BAL := 0;
                  V_GL_BC_BAL := 0;
                   --MANI CHN 22-12-2012 BEG
                  --GET_ASON_GLBAL(1, P_BRN_CODE => BRANCH_CODE,
                  GET_ASON_GLBAL(V_ENTITY_NUM, P_BRN_CODE => BRANCH_CODE,
                   --MANI CHN 22-12-2012 END
                                 P_GL_ACC_CODE => PRD.PRODUCT_GLACC_CODE,
                                 P_CURR_CODE => CUR.ACNTS_CURR_CODE,
                                 P_ASON_DATE => ASON_DATE, P_CURR_DATE => W_CBD,
                                 P_GL_BAL_AC => V_GL_AC_BAL,
                                 P_GL_BAL_BC => V_GL_BC_BAL,
                                 P_ERR_MSG => W_ERROR_MSG,P_SUP_IGNORE_FLG => W_SUP_FLAG);

                  W_ACY_BAL     := 0;
                  W_LCY_BAL     := 0;
                  W_ACY_DB_BAL  := 0;
                  W_ACY_CR_BAL  := 0;
                  W_LCY_DB_BAL  := 0;
                  W_LCY_CR_BAL  := 0;
                  W_ACY_NET_BAL := 0;
                  W_LCY_NET_BAL := 0;
                  W_DB_CNT      := 0;
                  W_CR_CNT      := 0;

                  FOR ACNT IN (SELECT ACNTS_INTERNAL_ACNUM, ACNTS_CURR_CODE
                               FROM ACNTS
                               WHERE ACNTS_ENTITY_NUM = V_ENTITY_NUM
                               AND ACNTS_CURR_CODE = CUR.ACNTS_CURR_CODE
                               AND ACNTS_PROD_CODE = PRD.PRODUCT_CODE
                                      AND ACNTS_BRN_CODE =
                                     DECODE(BRANCH_CODE, 0, ACNTS_BRN_CODE,
                                         BRANCH_CODE))
                  LOOP
                    GET_ASON_ACBAL(V_ENTITY_NUM, ACNT.ACNTS_INTERNAL_ACNUM,
                                   ACNT.ACNTS_CURR_CODE, ASON_DATE, W_CBD,
                                   W_ACY_BAL, W_LCY_BAL, W_ERROR_MSG);

                    IF W_LCY_BAL <= 0
                    THEN
                      W_LCY_DB_BAL := W_LCY_DB_BAL + W_LCY_BAL;
                      W_ACY_DB_BAL := W_ACY_DB_BAL + W_ACY_BAL;
                      W_DB_CNT     := W_DB_CNT + 1;
                    ELSE
                      W_LCY_CR_BAL := W_LCY_CR_BAL + W_LCY_BAL;
                      W_ACY_CR_BAL := W_ACY_CR_BAL + W_ACY_BAL;
                      W_CR_CNT     := W_CR_CNT + 1;
                    END IF;
                  END LOOP;
                  P_RESULT.W_PRODUCT_CD  := PRD.PRODUCT_CODE;
                  P_RESULT.W_GL_ACCES_CD := PRD.PRODUCT_GLACC_CODE;
                  P_RESULT.W_GL_DESC     := PRD.EXTGL_EXT_HEAD_DESCN;
                  P_RESULT.W_GL_CUR_CD   := CUR.ACNTS_CURR_CODE;
                  P_RESULT.W_AC_DB_BAL   := W_ACY_DB_BAL;
                  P_RESULT.W_AC_CR_BAL   := W_ACY_CR_BAL;
                  P_RESULT.W_AC_NET_BAL  := W_ACY_DB_BAL + W_ACY_CR_BAL;
                  P_RESULT.W_BC_DB_BAL   := W_LCY_DB_BAL;
                  P_RESULT.W_BC_CR_BAL   := W_LCY_CR_BAL;
                  P_RESULT.W_BC_NET_BAL  := W_LCY_DB_BAL + W_LCY_CR_BAL;
                  P_RESULT.W_DB_ACS      := W_DB_CNT;
                  P_RESULT.W_CR_ACS      := W_CR_CNT;
                  P_RESULT.W_GL_AC_BAL   := V_GL_AC_BAL;
                  P_RESULT.W_GL_BC_BAL   := V_GL_BC_BAL;
                  PIPE ROW(P_RESULT);
                END LOOP;
              END LOOP;
           END IF;
      IF DATA_FOUND = 0
          THEN
            P_RESULT.W_PRODUCT_CD  := 0;
            P_RESULT.W_GL_ACCES_CD := GL_ACC;
            P_RESULT.W_GL_DESC     := '0';
            P_RESULT.W_GL_CUR_CD   := '0';
            P_RESULT.W_AC_DB_BAL   := 0;
            P_RESULT.W_AC_CR_BAL   := 0;
            P_RESULT.W_AC_NET_BAL  := 0;
            P_RESULT.W_BC_DB_BAL   := 0;
            P_RESULT.W_BC_CR_BAL   := 0;
            P_RESULT.W_BC_NET_BAL  := 0;
            P_RESULT.W_DB_ACS      := 0;
            P_RESULT.W_CR_ACS      := 0;
            P_RESULT.W_GL_AC_BAL   := 0;
            P_RESULT.W_GL_BC_BAL   := 0;
            PIPE ROW(P_RESULT);
          END IF;  
        EXCEPTION
          WHEN OTHERS THEN

            P_RESULT.W_PRODUCT_CD  := 0;
            P_RESULT.W_GL_ACCES_CD := GL_ACC;
            P_RESULT.W_GL_DESC     := '0';
            P_RESULT.W_GL_CUR_CD   := '0';
            P_RESULT.W_AC_DB_BAL   := 0;
            P_RESULT.W_AC_CR_BAL   := 0;
            P_RESULT.W_AC_NET_BAL  := 0;
            P_RESULT.W_BC_DB_BAL   := 0;
            P_RESULT.W_BC_CR_BAL   := 0;
            P_RESULT.W_BC_NET_BAL  := 0;
            P_RESULT.W_DB_ACS      := 0;
            P_RESULT.W_CR_ACS      := 0;
            P_RESULT.W_GL_AC_BAL   := 0;
            P_RESULT.W_GL_BC_BAL   := 0;
            PIPE ROW(P_RESULT);
        END FN_CUSTGL_BAL;
      BEGIN
       
      END PKG_CUSTGL;

        • 1. Re: pipeline
          K.S.I.

          Hi.

          Show  output data this query after compile yout package   :

          select * from user_errors where name = ' PKG1'

          • 2. Re: pipeline
            1043556

            while executing the pkg starting it self im getting pls00653 aggregate /table functions cannot used in plsql scope

            • 3. Re: pipeline
              1043556

              Hi all

              this is the simple pkg while execution im getting

              pls00653 aggregate /table functions cannot used in plsql scope pls help me

               

               

              create or replace package pkg1 is

               

              type ty_custgl is record(

              w_product_cd number(4),

              w_gl_acces_cd varchar2(15))

               

              type cust_gl is table of ty_custgl;

              function fn_custgl_bal(v_entity_num in number, branch_code in number,

              v_curr_code in varchar2, ason_date in date,

              customer_gl in number, gl_acc in varchar2,w_sup_flag in number default 0)--s.prem 03/01/2012

              return cust_gl

              pipelined;

              end pkg1;

              /

               

              create or replace package body pkg1 is

               

              p_result pkg_custgl.ty_custgl;

               

              function fn_custgl_bal(v_entity_num in number, branch_code in number,

              v_curr_code in varchar2, ason_date in date,

              customer_gl in number, gl_acc in varchar2,w_sup_flag in number default 0)--s.prem 03/01/2012

              return cust_gl

              pipelined is

              //block of code

              p_result.w_product_cd := 0;

              p_result.w_gl_acces_cd := prd.extgl_access_code;

              p_result.w_gl_desc := prd.extgl_ext_head_descn;

              p_result.w_gl_cur_cd := cur.glbbal_curr_code;

              p_result.w_ac_db_bal := w_acy_db_bal;

              p_result.w_ac_cr_bal := w_acy_cr_bal;

              p_result.w_ac_net_bal := w_acy_db_bal + w_acy_cr_bal;

              p_result.w_bc_db_bal := w_lcy_db_bal;

              p_result.w_bc_cr_bal := w_lcy_cr_bal;

              p_result.w_bc_net_bal := w_lcy_db_bal + w_lcy_cr_bal;

              p_result.w_db_acs := w_db_cnt;

              p_result.w_cr_acs := w_cr_cnt;

              p_result.w_gl_ac_bal := w_acy_db_bal;

              p_result.w_gl_bc_bal := w_lcy_db_bal;

              pipe row(p_result);

               

              end;

              • 4. Re: pipeline
                K.S.I.

                Version oracle ?

                • 5. Re: pipeline
                  K.S.I.

                  create or replace package pkg1 is

                   

                  type ty_custgl is record(

                  w_product_cd number(4),

                  w_gl_acces_cd varchar2(15))    -- absent symbol ;

                   

                  type cust_gl is table of ty_custgl;

                  function fn_custgl_bal(v_entity_num in number, branch_code in number,

                  v_curr_code in varchar2, ason_date in date,

                  customer_gl in number, gl_acc in varchar2,w_sup_flag in number default 0)--s.prem 03/01/2012

                  return cust_gl

                  pipelined;

                  end pkg1;

                  /

                  • 6. Re: pipeline
                    K.S.I.

                    create or replace package body pkg1 is

                     

                    p_result pkg_custgl.ty_custgl;    - ??

                     

                    function fn_custgl_bal(v_entity_num in number, branch_code in number,

                    v_curr_code in varchar2, ason_date in date,

                    customer_gl in number, gl_acc in varchar2,w_sup_flag in number default 0)--s.prem 03/01/2012

                    return cust_gl

                    pipelined is

                    //block of code

                    p_result.w_product_cd := 0;

                    p_result.w_gl_acces_cd := prd.extgl_access_code;

                    p_result.w_gl_desc := prd.extgl_ext_head_descn;

                    p_result.w_gl_cur_cd := cur.glbbal_curr_code;

                    p_result.w_ac_db_bal := w_acy_db_bal;

                    p_result.w_ac_cr_bal := w_acy_cr_bal;

                    p_result.w_ac_net_bal := w_acy_db_bal + w_acy_cr_bal;

                    p_result.w_bc_db_bal := w_lcy_db_bal;

                    p_result.w_bc_cr_bal := w_lcy_cr_bal;

                    p_result.w_bc_net_bal := w_lcy_db_bal + w_lcy_cr_bal;

                    p_result.w_db_acs := w_db_cnt;

                    p_result.w_cr_acs := w_cr_cnt;

                    p_result.w_gl_ac_bal := w_acy_db_bal;

                    p_result.w_gl_bc_bal := w_lcy_db_bal;

                    pipe row(p_result);

                     

                     

                    Descriptions of types: records match in packets?

                    • 7. Re: pipeline
                      padders

                      If it is not obvious...

                       

                      Pipelined functions can only be called in SQL via the 'TABLE ()' syntax. They cannot be called in PL/SQL using the assignment operator ':='.

                      • 8. Re: pipeline
                        1043556

                        my oracle version is 11

                        • 9. Re: pipeline
                          K.S.I.

                          Function is returned table of record  cust_gl. in  your source of package....

                           

                          Varrable P_RESULT description as pkg_custgl.ty_custgl  in your source package.


                          what is declaration  table of record  :  pkg_custgl.ty_custgl?


                          for example:

                          create or replace package pkg1 is

                           

                           

                          type ty_custgl is record(

                           

                          w_product_cd number(4),

                           

                          w_gl_acces_cd varchar2(15));

                           

                           

                          type cust_gl is table of ty_custgl;

                           

                          function fn_custgl_bal(v_entity_num in number, branch_code in number,

                           

                          v_curr_code in varchar2, ason_date in date,

                           

                          customer_gl in number, gl_acc in varchar2,w_sup_flag in number default 0)--s.prem 03/01/2012

                           

                          return cust_gl

                           

                          pipelined;

                           

                          end pkg1;

                           

                          /

                           

                           

                           

                          create or replace package body pkg1 is

                           

                           

                           

                          p_result ty_custgl ;  --pkg_custgl.ty_custgl;

                           

                           

                           

                          function fn_custgl_bal(v_entity_num in number,

                                                branch_code   in number,

                                                v_curr_code   in varchar2,

                                                ason_date     in date,

                                                customer_gl   in number,

                                                gl_acc        in varchar2,

                                                w_sup_flag    in number default 0

                                                )--s.prem 03/01/2012

                           

                          return cust_gl

                           

                          pipelined is

                           

                          begin

                           

                          --//block of code

                          /*

                          p_result.w_product_cd := 0;

                           

                          p_result.w_gl_acces_cd := prd.extgl_access_code;

                           

                          p_result.w_gl_desc := prd.extgl_ext_head_descn;

                           

                          p_result.w_gl_cur_cd := cur.glbbal_curr_code;

                           

                          p_result.w_ac_db_bal := w_acy_db_bal;

                           

                          p_result.w_ac_cr_bal := w_acy_cr_bal;

                           

                          p_result.w_ac_net_bal := w_acy_db_bal + w_acy_cr_bal;

                           

                          p_result.w_bc_db_bal := w_lcy_db_bal;

                           

                          p_result.w_bc_cr_bal := w_lcy_cr_bal;

                           

                          p_result.w_bc_net_bal := w_lcy_db_bal + w_lcy_cr_bal;

                           

                          p_result.w_db_acs := w_db_cnt;

                           

                          p_result.w_cr_acs := w_cr_cnt;

                           

                          p_result.w_gl_ac_bal := w_acy_db_bal;

                           

                          p_result.w_gl_bc_bal := w_lcy_db_bal;

                          */

                           

                          p_result.w_product_cd  := 1;

                          p_result.w_gl_acces_cd := gl_acc;

                           

                          pipe row(p_result);

                           

                          return;

                           

                          end;

                           

                          end;

                          /

                           

                          select * from table (pkg1.fn_custgl_bal(v_entity_num=> 1,

                                                branch_code=>   1,

                                                v_curr_code=>   'Test',

                                                ason_date=>     sysdate,

                                                customer_gl=>   1,

                                                gl_acc=>        'Test Call'));

                           

                          SQL>

                          PACKAGE PKG1 compiled

                          PACKAGE BODY PKG1 compiled

                           

                          W_PRODUCT_CD W_GL_ACCES_CD

                          ------------ ---------------

                                     1 Test Call