6 Replies Latest reply: Feb 22, 2013 9:55 AM by Jaffee2 RSS

    Nested table

    Jaffee2
      Hello,

      I have a nested table. I delete from the nested table leaving the data set in the nested table without values. I then try to repopulate the nested portion of the
      table by inserting values.

      I receive the message ORA-22908 reference to NULL table value (insert into nest). How do I resolve this issue? Thanks.


      Regards
        • 1. Re: Nested table
          JustinCave
          It would be very helpful if you could post a test case that shows exactly what you are doing. I can come up with a few different reasonable interpretations.

          Justin
          • 2. Re: Nested table
            rp0428
            >
            I have a nested table. I delete from the nested table leaving the data set in the nested table without values. I then try to repopulate the nested portion of the
            table by inserting values.

            I receive the message ORA-22908 reference to NULL table value (insert into nest). How do I resolve this issue? Thanks.
            >
            There are examples of the correct way to use DML on nested tables.

            Make sure your DML operations on your nested table follow the examples in the object-relational doc
            http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjcol.htm#CHDDBICH
            >
            Example 5-21 Piecewise Operations on Collections

            -- Requires Ex. 5-1 and 5-3
            INSERT INTO TABLE(SELECT d.dept_emps
            FROM department_persons d
            WHERE d.dept_no = 101)
            VALUES (5, 'Kevin Taylor', '1-408-555-0199');

            UPDATE TABLE(SELECT d.dept_emps
            FROM department_persons d
            WHERE d.dept_no = 101) e
            SET VALUE(e) = person_typ(5, 'Kevin Taylor', '1-408-555-0199')
            WHERE e.idno = 5;

            DELETE FROM TABLE(SELECT d.dept_emps
            FROM department_persons d
            WHERE d.dept_no = 101) e
            WHERE e.idno = 5;
            Example 5-22 shows VALUE used to return object instance rows for updating:

            Example 5-22 Using VALUE to Update a Nested Table

            -- Requires Ex. 5-1, 5-3
            UPDATE TABLE(SELECT d.dept_emps FROM department_persons d
            WHERE d.dept_no = 101) p
            SET VALUE(p) = person_typ(2, 'Diane Smith', '1-650-555-0148')
            WHERE p.idno = 2;
            • 3. Re: Nested table
              Jaffee2
              Hello,

              Here is the question.

              The code below does not update the table. I ran the procedure in debug and I can see the inserts commited to the database. As I am running debug I check the table for the insertions.
              However, after the debug session is complete, I re-check the table and the insertions are not there.

              Any help would be appreciated. Thanks.

               
              SET SERVEROUTPUT ON SIZE 1000000;
              CREATE OR REPLACE PACKAGE BODY CAMSADM.gl260_debbie_chart_init
              
              IS 
              PROCEDURE   insert_gtas_acct_from_charts_s (  p_account_no  varchar2 ) 
              IS                            
              
              
              BEGIN   -- BEGINNING OF Procedure
              
              --
              DECLARE 
              
                --  attribute    varchar2(40);
                  --domain       varchar2(100);
                  NbrOfRecords            number := 0;
                  deletedNbrOfRecords            number := 0;
                  v_account_no varchar2(6);
                  commit_flag boolean := FALSE;                        
                 
                  v_error_code            number;
                  v_error_messages        varchar2(255);
                  
                  CURSOR chart_record_cursor   IS
                       Select distinct CH.ACCOUNT_NO,    
              -- Attribute flags         
                       ch.APPORT_CATEGORY_FLAG  ,     
                       ch.APPORT_B_CAT_NO_FLAG  ,       
                       ch.AUTHORITY_TYPE_FLAG ,      
                       ch.AVAILABILITY_TIME_FLAG  ,      
                       ch.BEA_CAT_INDICATOR_FLAG               ,    
                       ch. BEGIN_END_INDICATOR_FLAG   ,                           
                       ch.BORROWING_SOURCE_FLAG            ,         
                       ch.BUDGETARY_IMPACT_FLAG            ,               
                       ch.COHORT_YEAR_FLAG          ,           
                       ch.CUSTODIAL_NONCUSTODIAL_FLAG      ,
                       ch.DEBIT_CREDIT_FLAG               ,         
                       ch.EXCHANGE_NONEXCHANGE_FLAG       ,        
                       ch.TRANSACTION_PARTNER_FLAG        ,        
                       ch.PY_ADJUSTMENT_CODE_FLAG        ,       
                       ch.PROGRAM_INDICATOR_FLAG           ,        
                       ch.PROGRAM_REPORT_CATEGORY_FLAG    ,        
                       ch. DIRECT_FLAG               ,          
                       ch.TP_AGENCY_FLAG         ,       
                       ch.TP_MAIN_FLAG    ,       
                       ch.YEAR_OF_BUDGET_AUTH_FLAG     ,
                -- Domains  
                      ch. APPORT_CATEGORY_DOMAIN ,          
                    ch. APPORT_B_CAT_NO_DOMAIN ,            
                    ch.AUTHORITY_TYPE_DOMAIN          ,
                    ch.AVAILABILITY_TIME_DOMAIN           ,
                    ch. BEA_CAT_INDICATOR_DOMAIN             ,
                    ch.BEGIN_END_INDICATOR_DOMAIN         ,                     
                    ch.BORROWING_SOURCE_DOMAIN ,       
                    ch.BUDGETARY_IMPACT_DOMAIN  ,                   
                    ch.COHORT_YEAR_DOMAIN ,                                   
                    ch.CUSTODIAL_NONCUSTODIAL_DOMAIN ,                
                    ch.DEBIT_CREDIT_DOMAIN ,          
                    ch.EXCHANGE_NONEXCHANGE_DOMAIN,        
                    ch.TRANSACTION_PARTNER_DOMAIN ,                
                    ch. PY_ADJUSTMENT_CODE_DOMAIN ,               
                    ch.PROGRAM_INDICATOR_DOMAIN ,              
                    ch. PROGRAM_REPORT_CATEGORY_DOMAIN ,             
                    ch.DIRECT_FLAG_DOMAIN ,        
                    ch.TP_AGENCY_DOMAIN,                
                    ch.TP_MAIN_DOMAIN   ,            
                    ch.YEAR_OF_BUDGET_AUTH_DOMAIN                  
                        FROM chart ch  
                        where  
                        CH.ACTIVE_STATUS = 'Y'
                        order by ch.account_no;
                          
                        r_fileRec chart_record_cursor%ROWTYPE;    
                
              BEGIN
                    --  dbms_output.enable( 500000 );
                      dbms_output.put_line( 'Begin Program ' );
                      
                      OPEN chart_record_cursor;
              
              LOOP
              
                 DECLARE 
                      e_invalid EXCEPTION;
                      PRAGMA EXCEPTION_INIT (e_invalid , -22908);
              BEGIN
                        commit_flag := FALSE;
                        
                      fetch  chart_record_cursor into r_fileRec;                         
                               exit when  chart_record_cursor%notfound;        
                        
                    if  chart_record_cursor%notfound then
                           null; -- continue
                    end if;
                       
                       NbrOfRecords :=  NbrOfRecords + 1;
                             
                             DELETE FROM TABLE (SELECT gta.ATTRIBUTE_LIST
                                                                  FROM    gtas_accounts gta
                                                                   WHERE  GTA.USSGL_ACCOUNT_NO  =   r_fileRec.ACCOUNT_NO    --p_account_no
                                                                   
                                                                    ) attrDom 
                              WHERE  attrDom.attribute  IS NOT NULL ;                                       
                                     
                          deletedNbrOfRecords := deletedNbrOfRecords + 1;    
              
                         commit;
                        
                        update gtas_accounts
                                      set  ATTRIBUTE_LIST =  GL260_ATTRIBUTES_TBL_TYP( gl260_ATTRIBUTES_REC_TYP (NULL, NULL)  );                      
              --             
                         IF    r_fileRec.APPORT_CATEGORY_FLAG = 'Y'          
                         THEN        
                              Insert into TABLE (
                                      SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO -- p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('apportion_category_code ',  r_fileRec.APPORT_CATEGORY_DOMAIN)                                
                                   
                                                  ) ;
                              commit_flag := TRUE;
                         END IF;                           
              --                                 
                         IF    r_fileRec.APPORT_B_CAT_NO_FLAG = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO --p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('apportion_cat_b_program_code',  r_fileRec.APPORT_B_CAT_NO_DOMAIN)
                                      
                                                  ) ;
                               commit_flag := TRUE;                                    
                        END IF;                                              
               --                                                              
                         IF    r_fileRec.AUTHORITY_TYPE_FLAG = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO -- p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('authority_type_code',  r_fileRec.AUTHORITY_TYPE_DOMAIN)
                                        
                                                  ) ;      
                               commit_flag := TRUE;    
                           END IF;      
                           
               --         AVAIL                                                      
                         IF    r_fileRec.AVAILABILITY_TIME_FLAG = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO -- p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('availability_time_code',  r_fileRec.AVAILABILITY_TIME_DOMAIN)
                                        
                                                  ) ;      
              
                               commit_flag := TRUE;    
                               
                           END IF;
                           
               --         BEA                                                       
                         IF    r_fileRec.BEA_CAT_INDICATOR_FLAG = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO --p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('bea_category_code',  r_fileRec. BEA_CAT_INDICATOR_DOMAIN )
                                        
                                                  ) ;      
                               commit_flag := TRUE;    
                               
                           END IF;                  
              
               --         BEGIN END                                                     
                         IF    r_fileRec.BEGIN_END_INDICATOR_FLAG  = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO --p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('begin_end_code',  r_fileRec.BEGIN_END_INDICATOR_DOMAIN  )
                                        
                                                  ) ;      
                               commit_flag := TRUE;                 
                           
                           END IF;
                           
              --         BORROWING SOURCE                                                      
                         IF    r_fileRec.BORROWING_SOURCE_FLAG    = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO --p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('borrowing_source_code',  r_fileRec.BORROWING_SOURCE_DOMAIN )
                                        
                                                  ) ;
                               commit_flag := TRUE;    
                                                                         
                           END IF;
                           
              --         BUDGET IMPACT                                                      
                         IF    r_fileRec.BUDGETARY_IMPACT_FLAG     = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO -- p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP(' budgetary_impact_code',  r_fileRec.BUDGETARY_IMPACT_DOMAIN )
                                        
                                                  ) ;
                                                  
                               commit_flag := TRUE;                                              
                           END IF;
                           
              --         COHORT                                                      
                         IF    r_fileRec.COHORT_YEAR_FLAG     = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO --p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('credit_cohort_year',  r_fileRec.COHORT_YEAR_DOMAIN )
                                        
                                                  ) ;  
                               commit_flag := TRUE;                                            
                           END IF;
                           
              --         CUSTODIAL                                                     
                         IF    r_fileRec.CUSTODIAL_NONCUSTODIAL_FLAG      = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO-- p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('custodial_noncustodial_coder',  r_fileRec.CUSTODIAL_NONCUSTODIAL_DOMAIN )
                                        
                                                  ) ;      
              
                               commit_flag := TRUE;                                        
                           END IF;
                           
              --         DEBIT CREDIT                                                    
                         IF    r_fileRec.DEBIT_CREDIT_FLAG        = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO --p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('debit_credit_code',  r_fileRec.DEBIT_CREDIT_DOMAIN )
                                        
                                                  ) ;      
                                                  
                               commit_flag := TRUE;    
                                                                   
                           END IF;                                                                                                                                                      
              
              --       EXCHANGE                                    
                         IF    r_fileRec.EXCHANGE_NONEXCHANGE_FLAG       = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO =  r_fileRec.ACCOUNT_NO --p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('exchange_nonexchange_code',  r_fileRec.EXCHANGE_NONEXCHANGE_DOMAIN )
                                        
                                                  ) ;    
                                                  
                               commit_flag := TRUE;                                          
                           END IF;                                              
                           
              --       TRANSACTION_PARTNER_FLAG                            
                         IF    r_fileRec.TRANSACTION_PARTNER_FLAG       = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO -- p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('federal_nonfederal_code',  r_fileRec.TRANSACTION_PARTNER_DOMAIN )
                                        
                                                  ) ;      
                               commit_flag := TRUE;                                        
                           END IF;                                     
                           
              --       PRIOR YEAR                                 
                         IF    r_fileRec.PY_ADJUSTMENT_CODE_FLAG       = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO-- p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('prior_year_adjustment_code',  r_fileRec.PY_ADJUSTMENT_CODE_DOMAIN )
                                        
                                                  ) ;      
                               commit_flag := TRUE;                                        
                           END IF;
                           
              --     PROGRAM IND                        
                         IF    r_fileRec.PROGRAM_INDICATOR_FLAG        = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO-- p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('program_indicator_code',  r_fileRec.PROGRAM_INDICATOR_DOMAIN )
                                        
                                                  ) ;     
                               commit_flag := TRUE;                                         
                           END IF;
                           
              --      PROGRAM_REPORT_CATEGORY_FLAG                 
                         IF    r_fileRec.PROGRAM_REPORT_CATEGORY_FLAG         = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO-- p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('program_report_category_code',  r_fileRec.PROGRAM_REPORT_CATEGORY_DOMAIN )
                                        
                                                  ) ;      
                                                  
                               commit_flag := TRUE;                                        
                           END IF;
                           
              --     DIRECT_FLAG   / REIM             
                         IF    r_fileRec.DIRECT_FLAG           = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO =  r_fileRec.ACCOUNT_NO --p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('reimbursable_code',  r_fileRec.DIRECT_FLAG_DOMAIN  )
                                        
                                                  ) ;      
                               commit_flag := TRUE;    
                                                                   
                           END IF;
                           
                           
              --     TP_AGENCY_FLAG           
                         IF    r_fileRec.TP_AGENCY_FLAG             = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO-- p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('trading_pnr_agency_id',  r_fileRec.TP_AGENCY_DOMAIN  )
                                        
                                                  ) ;      
                                                  
                               commit_flag := TRUE;                                        
                           END IF;         
                           
              --     TRADING_PNR_MAIN_ACCOUNT_CODE         
                         IF    r_fileRec.TP_AGENCY_FLAG             = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO =  r_fileRec.ACCOUNT_NO --p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('trading_pnr_main_account_code',  r_fileRec.TP_MAIN_DOMAIN  )
                                        
                                                  ) ;     
                                                  
                               commit_flag := TRUE;                                         
                           END IF;
                           
                           
              --     ch.YEAR_OF_BUDGET_AUTH_FLAG       
                         IF    r_fileRec.YEAR_OF_BUDGET_AUTH_FLAG             = 'Y'
                         THEN            
                             Insert into TABLE (
              
                          SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO-- p_account_no
                 
                                      ) VALUES (
              
                              gl260_ATTRIBUTES_REC_TYP('year_of_budget_authority_code ',  r_fileRec.YEAR_OF_BUDGET_AUTH_DOMAIN  )
                                        
                                                  ) ;      
                                                  
                               commit_flag := TRUE;                                        
                           END IF;                                                                                                                                                                                                 
                           
              --      if commit_flag then                                                        
              --            COMMIT;
              --      end if;                                                                                                                                                                                              
                    
                    
                    EXCEPTION
                          WHEN e_invalid  THEN
                                      dbms_output.put_line('Error ORA-22908: reference to NULL table value.- account no: '    || r_fileRec.ACCOUNT_NO);
                          WHEN no_data_found THEN
                                      DBMS_OUTPUT.put_line('no data find  ');
                                      NULL;
                          WHEN OTHERS THEN 
                                      dbms_output.put_line('The program received an error. Error message returned was: ' || SQLCODE || ',' || SQLERRM);
              
                 
              END;  
              
                 if commit_flag then                                                        
                          COMMIT;
                    end if;   
              
              END LOOP;  
              
              CLOSE  chart_record_cursor;
              
                    
                                  
              DBMS_OUTPUT.put_line('     ');
              DBMS_OUTPUT.put_line('Number of records deleted - Record count: '   || deletedNbrOfRecords);
              DBMS_OUTPUT.put_line('Number of record inserted  - Record count: '   || NbrOfRecords );
              
              END;
              END insert_gtas_acct_from_charts_s;                        
              
              END  gl260_debbie_chart_init;
              /
              Edited by: Jaffee2 on Feb 22, 2013 9:44 AM
              • 4. Re: Nested table
                APC
                Well this must get some some of prize, perhaps * The Most Useless Comment In A Program In Desperate Need Of Comments*, for
                BEGIN   -- BEGINNING OF Procedure
                Cheers, APC
                • 5. Re: Nested table
                  APC
                  Anyway, the meaning of this error message:
                  ORA-22908 reference to NULL table value
                  is that the nested table is null. This is what happens when you delete a nested table. So what you need to do is update the table intead.

                  The basic principle is that you do something like this:
                  update table t
                  set t.ATTRIBUTE_LIST =  
                    nested_table_type(gl260_ATTRIBUTES_REC_TYP('apportion_category_code ',  r_fileRec.APPORT_CATEGORY_DOMAIN))
                  WHERE  t.USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO 
                  ;
                  That obviously isn't the actual solution but it's the best I can do without all the schema info. You should be able to resolve the compilation errors form here.

                  Cheers, APC
                  • 6. Re: Nested table
                    Jaffee2
                    I updated the nested part of the table with null values. I commented out the delete porton.

                    But?

                    I execute the script in debug. I can see the insertion into the table while running debug. While in debug I check the table for a few insertions. I can confirm the insertion in the table while executing debug.
                    However after debug is complete, I cannot see the insertions in the nested table.

                    Any insights into what to look for the problem of why the table is inot updated?


                    See below.
                    CREATE OR REPLACE PACKAGE BODY CAMSADM.gl260_debbie_chart_init
                    
                    IS 
                    PROCEDURE   insert_gtas_acct_from_charts_s (  p_account_no  varchar2 ) 
                    IS                            
                    
                    
                    BEGIN   -- BEGINNING OF Procedure
                    
                    --
                    DECLARE 
                    
                      --  attribute    varchar2(40);
                        --domain       varchar2(100);
                        NbrOfRecords            number := 0;
                        deletedNbrOfRecords            number := 0;
                        v_account_no varchar2(6);
                        commit_flag boolean := FALSE;
                        
                             v_APPORTION_CATEGORY_FLAG  varchar2(1) ;    
                              v_APPORTION_CAT_B_PROGRAM_FLAG  varchar2(1)   ;       
                             v_AUTHORITY_TYPE_FLAG  varchar2(1)  ;
                             v_AVAILABILITY_TIME_FLAG  varchar2(1)   ;      
                             v_BEA_CATEGORY_FLAG  varchar2(1)                 ;    
                              v_BEGIN_END_FLAG  varchar2(1)    ;            
                              v_BORROWING_SOURCE_FLAG  varchar2(1)             ;         
                             v_BUDGETARY_IMPACT_FLAG  varchar2(1)             ;              
                            v_CREDIT_COHORT_YEAR_FLAG  varchar2(1)           ;          
                             v_CUSTODIAL_NONCUSTODIAL_FLAG  varchar2(1)       ;
                              v_DEBIT_CREDIT_FLAG  varchar2(1)                ;         
                             v_EXCHANGE_NONEXCHANGE_FLAG  varchar2(1)        ;        
                             v_FEDERAL_NONFEDERAL_FLAG  varchar2(1)          ;        
                             v_PRIOR_YEAR_ADJUSTMENT_FLAG  varchar2(1)        ;                  
                              v_PROGRAM_INDICATOR_FLAG  varchar2(1)             ;
                              v_PROGRAM_REPORT_CATEGORY_FLAG   varchar2(1)    ;        
                              v_REIMBURSABLE_FLAG   varchar2(1)                ;          
                              v_TRADING_PNR_AGENCY_FLAG   varchar2(1)          ;       
                              v_TRADING_PNR_MAIN_ACCT_FLAG  varchar2(1)     ;       
                              v_YEAR_OF_BUDGET_AUTH_FLAG   varchar2(1)     ;
                              
                              -- Domains
                           v_APPORTION_CATEGORY_DOMAIN varchar2(100)  ;
                            v_APPORT_CAT_B_PROGRAM_DOMAIN varchar2(100)  ;            
                           v_AUTH_DOMAIN varchar2(100)            ;
                           v_AVAILABILITY_TIME_DOMAIN  varchar2(100)            ;
                            -- BEA_DOMAIN            varchar2(100)    ;
                           v_BEGIN_END_DOMAIN       varchar2(100)    ;                    
                            v_BORR_DOMAIN  varchar2(100)   ;
                            v_BUDGET_DOMAIN varchar2(100)   ;                   
                            v_CREDIT_COHORT_YEAR_DOMAIN   varchar2(100)  ;                                   
                            v_CUSTODIAL_NONCUST_DOMAIN  varchar2(100)   ;                
                            v_DEBIT_CREDIT_DOMAIN varchar2(100)   ;
                            v_EXCH_DOMAIN varchar2(100)  ;
                           v_FEDERAL_NONFEDERAL_DOMAIN varchar2(100)   ;                
                       --   v_ PY_DOMAIN varchar2(100)   ; 
                            v_PROGRAM_INDICATOR_DOMAIN varchar2(100)   ;              
                            v_PROGRAM_REPORT_CAT_DOMAIN varchar2(100)   ;             
                            v_REIMBURSABLE_DOMAIN varchar2(100)   ;
                            v_TRADING_PNR_AGENCY_DOMAIN  varchar2(100)  ;                
                            v_TRADING_PNR_MAIN_ACCT_DOMAIN  varchar2(100)  ;            
                            v_YEAR_OF_BUDGET_AUTH_DOMAIN varchar2(100)  ;                  
                       
                        v_error_code            number;
                        v_error_messages        varchar2(255);
                        
                        CURSOR chart_record_cursor   IS
                             Select distinct CH.ACCOUNT_NO,    
                    -- Attribute flags         
                             ch.APPORT_CATEGORY_FLAG  ,     
                             ch.APPORT_B_CAT_NO_FLAG  ,       
                             ch.AUTHORITY_TYPE_FLAG ,      
                             ch.AVAILABILITY_TIME_FLAG  ,      
                             ch.BEA_CAT_INDICATOR_FLAG               ,    
                             ch. BEGIN_END_INDICATOR_FLAG   ,                           
                             ch.BORROWING_SOURCE_FLAG            ,         
                             ch.BUDGETARY_IMPACT_FLAG            ,               
                             ch.COHORT_YEAR_FLAG          ,           
                             ch.CUSTODIAL_NONCUSTODIAL_FLAG      ,
                             ch.DEBIT_CREDIT_FLAG               ,         
                             ch.EXCHANGE_NONEXCHANGE_FLAG       ,        
                             ch.TRANSACTION_PARTNER_FLAG        ,        
                             ch.PY_ADJUSTMENT_CODE_FLAG        ,       
                             ch.PROGRAM_INDICATOR_FLAG           ,        
                             ch.PROGRAM_REPORT_CATEGORY_FLAG    ,        
                             ch. DIRECT_FLAG               ,          
                             ch.TP_AGENCY_FLAG         ,       
                             ch.TP_MAIN_FLAG    ,       
                             ch.YEAR_OF_BUDGET_AUTH_FLAG     ,
                      -- Domains  
                            ch. APPORT_CATEGORY_DOMAIN ,          
                          ch. APPORT_B_CAT_NO_DOMAIN ,            
                          ch.AUTHORITY_TYPE_DOMAIN          ,
                          ch.AVAILABILITY_TIME_DOMAIN           ,
                          ch. BEA_CAT_INDICATOR_DOMAIN             ,
                          ch.BEGIN_END_INDICATOR_DOMAIN         ,                     
                          ch.BORROWING_SOURCE_DOMAIN ,       
                          ch.BUDGETARY_IMPACT_DOMAIN  ,                   
                          ch.COHORT_YEAR_DOMAIN ,                                   
                          ch.CUSTODIAL_NONCUSTODIAL_DOMAIN ,                
                          ch.DEBIT_CREDIT_DOMAIN ,          
                          ch.EXCHANGE_NONEXCHANGE_DOMAIN,        
                          ch.TRANSACTION_PARTNER_DOMAIN ,                
                          ch. PY_ADJUSTMENT_CODE_DOMAIN ,               
                          ch.PROGRAM_INDICATOR_DOMAIN ,              
                          ch. PROGRAM_REPORT_CATEGORY_DOMAIN ,             
                          ch.DIRECT_FLAG_DOMAIN ,        
                          ch.TP_AGENCY_DOMAIN,                
                          ch.TP_MAIN_DOMAIN   ,            
                          ch.YEAR_OF_BUDGET_AUTH_DOMAIN                  
                              FROM chart ch  
                              where  
                              CH.ACTIVE_STATUS = 'Y'
                              order by ch.account_no;
                                
                              r_fileRec chart_record_cursor%ROWTYPE;    
                      
                    BEGIN
                          --  dbms_output.enable( 500000 );
                            dbms_output.put_line( 'Begin Program ' );
                            
                            OPEN chart_record_cursor;
                    
                    LOOP
                    
                       DECLARE 
                            e_invalid EXCEPTION;
                            PRAGMA EXCEPTION_INIT (e_invalid , -22908);
                    BEGIN
                              commit_flag := FALSE;
                              
                            fetch  chart_record_cursor into r_fileRec;                         
                                     exit when  chart_record_cursor%notfound;        
                              
                          if  chart_record_cursor%notfound then
                                 null; -- continue
                          end if;
                             
                             NbrOfRecords :=  NbrOfRecords + 1;
                    
                                 update gtas_accounts
                                       set  ATTRIBUTE_LIST =  GL260_ATTRIBUTES_TBL_TYP( gl260_ATTRIBUTES_REC_TYP (NULL, NULL)  );        
                                 commit;
                     /*                                      
                                   DELETE FROM TABLE (SELECT gta.ATTRIBUTE_LIST
                                                                        FROM    gtas_accounts gta
                                                                         WHERE  GTA.USSGL_ACCOUNT_NO  =   r_fileRec.ACCOUNT_NO    --p_account_no
                                                                         
                                                                          ) attrDom 
                                    WHERE  attrDom.attribute  IS NOT NULL ;                                       
                                           
                                deletedNbrOfRecords := deletedNbrOfRecords + 1;    
                    
                               commit;
                     */                       
                    --             
                               IF    r_fileRec.APPORT_CATEGORY_FLAG = 'Y'          
                               THEN        
                                    Insert into TABLE (
                                            SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO -- p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('apportion_category_code ',  r_fileRec.APPORT_CATEGORY_DOMAIN)                                
                                         
                                                        ) ;
                                    commit_flag := TRUE;
                               END IF;                           
                    --                                 
                               IF    r_fileRec.APPORT_B_CAT_NO_FLAG = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO --p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('apportion_cat_b_program_code',  r_fileRec.APPORT_B_CAT_NO_DOMAIN)
                                            
                                                        ) ;
                                     commit_flag := TRUE;                                    
                              END IF;                                              
                     --                                                              
                               IF    r_fileRec.AUTHORITY_TYPE_FLAG = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO -- p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('authority_type_code',  r_fileRec.AUTHORITY_TYPE_DOMAIN)
                                              
                                                        ) ;      
                                     commit_flag := TRUE;    
                                 END IF;      
                                 
                     --         AVAIL                                                      
                               IF    r_fileRec.AVAILABILITY_TIME_FLAG = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO -- p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('availability_time_code',  r_fileRec.AVAILABILITY_TIME_DOMAIN)
                                              
                                                        ) ;      
                    
                                     commit_flag := TRUE;    
                                     
                                 END IF;
                                 
                     --         BEA                                                       
                               IF    r_fileRec.BEA_CAT_INDICATOR_FLAG = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO --p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('bea_category_code',  r_fileRec. BEA_CAT_INDICATOR_DOMAIN )
                                              
                                                        ) ;      
                                     commit_flag := TRUE;    
                                     
                                 END IF;                  
                    
                     --         BEGIN END                                                     
                               IF    r_fileRec.BEGIN_END_INDICATOR_FLAG  = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO --p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('begin_end_code',  r_fileRec.BEGIN_END_INDICATOR_DOMAIN  )
                                              
                                                        ) ;      
                                     commit_flag := TRUE;                 
                                 
                                 END IF;
                                 
                    --         BORROWING SOURCE                                                      
                               IF    r_fileRec.BORROWING_SOURCE_FLAG    = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO --p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('borrowing_source_code',  r_fileRec.BORROWING_SOURCE_DOMAIN )
                                              
                                                        ) ;
                                     commit_flag := TRUE;    
                                                                               
                                 END IF;
                                 
                    --         BUDGET IMPACT                                                      
                               IF    r_fileRec.BUDGETARY_IMPACT_FLAG     = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO -- p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP(' budgetary_impact_code',  r_fileRec.BUDGETARY_IMPACT_DOMAIN )
                                              
                                                        ) ;
                                                        
                                     commit_flag := TRUE;                                              
                                 END IF;
                                 
                    --         COHORT                                                      
                               IF    r_fileRec.COHORT_YEAR_FLAG     = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO --p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('credit_cohort_year',  r_fileRec.COHORT_YEAR_DOMAIN )
                                              
                                                        ) ;  
                                     commit_flag := TRUE;                                            
                                 END IF;
                                 
                    --         CUSTODIAL                                                     
                               IF    r_fileRec.CUSTODIAL_NONCUSTODIAL_FLAG      = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO-- p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('custodial_noncustodial_coder',  r_fileRec.CUSTODIAL_NONCUSTODIAL_DOMAIN )
                                              
                                                        ) ;      
                    
                                     commit_flag := TRUE;                                        
                                 END IF;
                                 
                    --         DEBIT CREDIT                                                    
                               IF    r_fileRec.DEBIT_CREDIT_FLAG        = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO --p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('debit_credit_code',  r_fileRec.DEBIT_CREDIT_DOMAIN )
                                              
                                                        ) ;      
                                                        
                                     commit_flag := TRUE;    
                                                                         
                                 END IF;                                                                                                                                                      
                    
                    --       EXCHANGE                                    
                               IF    r_fileRec.EXCHANGE_NONEXCHANGE_FLAG       = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO =  r_fileRec.ACCOUNT_NO --p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('exchange_nonexchange_code',  r_fileRec.EXCHANGE_NONEXCHANGE_DOMAIN )
                                              
                                                        ) ;    
                                                        
                                     commit_flag := TRUE;                                          
                                 END IF;                                              
                                 
                    --       TRANSACTION_PARTNER_FLAG                            
                               IF    r_fileRec.TRANSACTION_PARTNER_FLAG       = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO -- p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('federal_nonfederal_code',  r_fileRec.TRANSACTION_PARTNER_DOMAIN )
                                              
                                                        ) ;      
                                     commit_flag := TRUE;                                        
                                 END IF;                                     
                                 
                    --       PRIOR YEAR                                 
                               IF    r_fileRec.PY_ADJUSTMENT_CODE_FLAG       = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO-- p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('prior_year_adjustment_code',  r_fileRec.PY_ADJUSTMENT_CODE_DOMAIN )
                                              
                                                        ) ;      
                                     commit_flag := TRUE;                                        
                                 END IF;
                                 
                    --     PROGRAM IND                        
                               IF    r_fileRec.PROGRAM_INDICATOR_FLAG        = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO-- p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('program_indicator_code',  r_fileRec.PROGRAM_INDICATOR_DOMAIN )
                                              
                                                        ) ;     
                                     commit_flag := TRUE;                                         
                                 END IF;
                                 
                    --      PROGRAM_REPORT_CATEGORY_FLAG                 
                               IF    r_fileRec.PROGRAM_REPORT_CATEGORY_FLAG         = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO-- p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('program_report_category_code',  r_fileRec.PROGRAM_REPORT_CATEGORY_DOMAIN )
                                              
                                                        ) ;      
                                                        
                                     commit_flag := TRUE;                                        
                                 END IF;
                                 
                    --     DIRECT_FLAG   / REIM             
                               IF    r_fileRec.DIRECT_FLAG           = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO =  r_fileRec.ACCOUNT_NO --p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('reimbursable_code',  r_fileRec.DIRECT_FLAG_DOMAIN  )
                                              
                                                        ) ;      
                                     commit_flag := TRUE;    
                                                                         
                                 END IF;
                                 
                                 
                    --     TP_AGENCY_FLAG           
                               IF    r_fileRec.TP_AGENCY_FLAG             = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO-- p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('trading_pnr_agency_id',  r_fileRec.TP_AGENCY_DOMAIN  )
                                              
                                                        ) ;      
                                                        
                                     commit_flag := TRUE;                                        
                                 END IF;         
                                 
                    --     TRADING_PNR_MAIN_ACCOUNT_CODE         
                               IF    r_fileRec.TP_AGENCY_FLAG             = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO =  r_fileRec.ACCOUNT_NO --p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('trading_pnr_main_account_code',  r_fileRec.TP_MAIN_DOMAIN  )
                                              
                                                        ) ;     
                                                        
                                     commit_flag := TRUE;                                         
                                 END IF;
                                 
                                 
                    --     ch.YEAR_OF_BUDGET_AUTH_FLAG       
                               IF    r_fileRec.YEAR_OF_BUDGET_AUTH_FLAG             = 'Y'
                               THEN            
                                   Insert into TABLE (
                    
                                SELECT  ATTRIBUTE_LIST FROM  GTAS_ACCOUNTS WHERE  USSGL_ACCOUNT_NO = r_fileRec.ACCOUNT_NO-- p_account_no
                       
                                            ) VALUES (
                    
                                    gl260_ATTRIBUTES_REC_TYP('year_of_budget_authority_code ',  r_fileRec.YEAR_OF_BUDGET_AUTH_DOMAIN  )
                                              
                                                        ) ;      
                                                        
                                     commit_flag := TRUE;                                        
                                 END IF;                                                                                                                                                                                                 
                                 
                                 
                    --      if commit_flag then                                                        
                    --            COMMIT;
                    --      end if;                                                                                                                                                                                              
                          
                          
                          EXCEPTION
                                WHEN e_invalid  THEN
                                            dbms_output.put_line('Error ORA-22908: reference to NULL table value.- account no: '    || r_fileRec.ACCOUNT_NO);
                                WHEN no_data_found THEN
                                            DBMS_OUTPUT.put_line('no data find  ');
                                            NULL;
                                WHEN OTHERS THEN 
                                            dbms_output.put_line('The program received an error. Error message returned was: ' || SQLCODE || ',' || SQLERRM);
                    
                       
                    END;  
                    
                       if commit_flag then                                                        
                                COMMIT;
                          end if;   
                    
                    END LOOP;  
                    
                    CLOSE  chart_record_cursor;
                    
                          
                                        
                    DBMS_OUTPUT.put_line('     ');
                    DBMS_OUTPUT.put_line('Number of records deleted - Record count: '   || deletedNbrOfRecords);
                    DBMS_OUTPUT.put_line('Number of record inserted  - Record count: '   || NbrOfRecords );
                    
                    END;
                    END insert_gtas_acct_from_charts_s;                        
                    
                    END  gl260_debbie_chart_init;
                    /