4 Replies Latest reply: Apr 23, 2013 2:43 PM by gaverill RSS

    Error while assigning values to Table type

    Venkat Rakesh
      Hi,

      I am trying to insert some values to a Collection type , while doing that I am facing an error ,
      " PLS-00306: wrong number or types of arguments in call to
       'XXSCP_AVL_CSP_SQR_O_TP' "
      PFB my collection type declaration and and assignment .

      Object type :
      TYPE XXSCP_AVL_CSP_SQR_O_TP AS                  
      OBJECT  (                                       
                  PLAN_ID             NUMBER,         
                  SR_INSTANCE_ID      NUMBER,         
                  INVENTORY_ITEM_ID   NUMBER,         
                  BASE_ITEM_ID        NUMBER,              
                   ITEM_NAME           VARCHAR2(50),       
                   SUPPLIER_ID         NUMBER,             
                  SUPPLIER_SITE_ID    NUMBER,         
                  NORMAL_BUY_USAGE    VARCHAR2(5),    
                  AVAILABLE_QUANTITY  NUMBER ,        
                  SPL_PEG_FLAG        VARCHAR2(10),   
                  SQR_LINE_ID         VARCHAR2(2000), 
                  SQR_NUMBER          VARCHAR2(2000), 
                  SQR_MOQ             NUMBER,         
                  BPA_PO_LINE_ID      NUMBER,         
                  SUGG_BPA_NUMBER     VARCHAR2(30),   
                  BPA_BUY_PRICE_BASIS VARCHAR2(10),   
                  BPA_LINE_NUM        NUMBER,         
                  BPA_PO_LINE_LOC_ID  NUMBER,         
                  BPA_CURRENCY_CODE   VARCHAR2(10),   
                  BPA_PRICE           NUMBER,         
                  BPA_PO_HEADER_ID    NUMBER,         
                  BPA_EXPIRATION_DATE DATE,           
                  BPA_QUANTITY        NUMBER,         
                  ORIG_AVAILABLE_QTY  NUMBER          
              );        
      
      Table type :
      
      TYPE XXSCP_AVL_CSP_SQR_C_TP AS TABLE OF XXSCP_AVL_CSP_SQR_O_TP;
      Declaraion:
       l_tab_avl_csp_sqr   XXSCP_AVL_CSP_SQR_C_TP := XXSCP_AVL_CSP_SQR_C_TP();
      Assignment in body :
      l_tab_avl_csp_sqr.EXTEND(1);
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT) := XXSCP_AVL_CSP_SQR_O_TP();
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).plan_id           := l_tab_planorders (i).l_num_plan_id;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).sr_instance_id    := l_tab_planorders (i).l_num_sr_instance_id;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).inventory_item_id := l_tab_planorders (i).l_num_inventory_item_id;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).base_item_id      := l_tab_planorders (i).l_num_base_item_id;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).item_name         := l_tab_planorders (i).l_chr_item_name;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).supplier_id       := l_tab_planorders (i).l_num_supplier_id;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).supplier_site_id  := l_tab_planorders (i).l_num_supplier_site_id;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).available_quantity := l_rec_pegged_list (j).remaining_aval_qty;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).spl_peg_flag       := 'Y';
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).sqr_line_id        := l_rec_pegged_list (j).sqr_line_id;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).sqr_number         := l_rec_pegged_list (j).sqr_number;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).sqr_moq            := l_rec_pegged_list (j).sqr_moq;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).bpa_po_line_id     := l_rec_pegged_list (j).bpa_po_line_id;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).sugg_bpa_number    := l_rec_pegged_list (j).bpa_number;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).bpa_buy_price_basis := l_rec_pegged_list (j).bpb_type;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).bpa_line_num        := l_rec_pegged_list (j).bpa_line_num;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).bpa_po_line_loc_id  := l_rec_pegged_list (j).bpa_lloc_id;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).bpa_currency_code   := l_rec_pegged_list (j).bpa_curr_code;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).bpa_price           := l_rec_pegged_list (j).bpa_price;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).bpa_expiration_date := l_rec_pegged_list (j).bpa_expiration_date ;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).bpa_quantity        := l_rec_pegged_list (j).bpa_quantity ;
      l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT).orig_available_qty  := l_rec_pegged_list (j).remaining_aval_qty ;
      Can anyone help in this issue.

      Thanks in advance,
      Rakesh

      Edited by: Venkat Rakesh on Apr 24, 2013 12:18 AM
        • 1. Re: Error while assigning values to Table type
          gaverill
          you need to add an empty-arg user-defined constructor for your type in order to use, "l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT) := XXSCP_AVL_CSP_SQR_O_TP();"
          create or replace
          TYPE XXSCP_AVL_CSP_SQR_O_TP AS                  
          OBJECT  (                                       
                      PLAN_ID             NUMBER,         
                      SR_INSTANCE_ID      NUMBER,         
                      INVENTORY_ITEM_ID   NUMBER,         
                      BASE_ITEM_ID        NUMBER,              
                       ITEM_NAME           VARCHAR2(50),       
                       SUPPLIER_ID         NUMBER,             
                      SUPPLIER_SITE_ID    NUMBER,         
                      NORMAL_BUY_USAGE    VARCHAR2(5),    
                      AVAILABLE_QUANTITY  NUMBER ,        
                      SPL_PEG_FLAG        VARCHAR2(10),   
                      SQR_LINE_ID         VARCHAR2(2000), 
                      SQR_NUMBER          VARCHAR2(2000), 
                      SQR_MOQ             NUMBER,         
                      BPA_PO_LINE_ID      NUMBER,         
                      SUGG_BPA_NUMBER     VARCHAR2(30),   
                      BPA_BUY_PRICE_BASIS VARCHAR2(10),   
                      BPA_LINE_NUM        NUMBER,         
                      BPA_PO_LINE_LOC_ID  NUMBER,         
                      BPA_CURRENCY_CODE   VARCHAR2(10),   
                      BPA_PRICE           NUMBER,         
                      BPA_PO_HEADER_ID    NUMBER,         
                      BPA_EXPIRATION_DATE DATE,           
                      BPA_QUANTITY        NUMBER,         
                      ORIG_AVAILABLE_QTY  NUMBER,
          /*
          Constructor
          */
                      constructor function XXSCP_AVL_CSP_SQR_O_TP return self as result
                  );        
           
          create or replace
          TYPE body XXSCP_AVL_CSP_SQR_O_TP AS                  
          /*
          Constructor
          */
                      constructor function XXSCP_AVL_CSP_SQR_O_TP return self as result
                      is
                      begin
                        return;
                      end;
          end;        
          alternatively, assign your attributes as you construct your instance:
          l_tab_avl_csp_sqr.EXTEND(1);
          l_tab_avl_csp_sqr(l_tab_avl_csp_sqr.COUNT) := new XXSCP_AVL_CSP_SQR_O_TP(
            l_tab_planorders (i).l_num_plan_id,
            l_tab_planorders (i).l_num_sr_instance_id.
            l_tab_planorders (i).l_num_inventory_item_id,
            l_tab_planorders (i).l_num_base_item_id,
            l_tab_planorders (i).l_chr_item_name,
            l_tab_planorders (i).l_num_supplier_id,
            l_tab_planorders (i).l_num_supplier_site_id,
            l_rec_pegged_list (j).remaining_aval_qty,
            'Y',
            l_rec_pegged_list (j).sqr_line_id,
            l_rec_pegged_list (j).sqr_number,
            l_rec_pegged_list (j).sqr_moq,
            l_rec_pegged_list (j).bpa_po_line_id,
            l_rec_pegged_list (j).bpa_number,
            l_rec_pegged_list (j).bpb_type,
            l_rec_pegged_list (j).bpa_line_num,
            l_rec_pegged_list (j).bpa_lloc_id,
            l_rec_pegged_list (j).bpa_curr_code,
            l_rec_pegged_list (j).bpa_price,
            l_rec_pegged_list (j).bpa_expiration_date,
            l_rec_pegged_list (j).bpa_quantity,
            l_rec_pegged_list (j).remaining_aval_qty );
          Gerard

          Edited by: gaverill on Apr 23, 2013 12:12 PM
          • 2. Re: Error while assigning values to Table type
            Venkat Rakesh
            Hi Gerard,

            I am using this furthur in as a table type in XXSCP_AVL_CSP_SQR_C_TP.
            If a place this empty arg constructor in the object type, can I link this to my Table type ?

            Can you please suggest .

            Thanks & Regards,
            Rakesh
            • 3. Re: Error while assigning values to Table type
              Venkat Rakesh
              Hi Gerard,
              It worked :) .. Thank you very much for your help.

              Thanks & Regards,
              Rakesh
              • 4. Re: Error while assigning values to Table type
                gaverill
                "can I link this to my Table type ?"

                i'm sorry, i don't understand.

                Gerard