1 Reply Latest reply: Apr 30, 2013 1:04 PM by Denes Kubicek RSS

    ORA-06531: Reference to uninitialized collection

    806228
      i have created the below TYPE in sql prompt.

      CREATE OR REPLACE TYPE APPL.TYP_TAB_OTCCCXPNL
      AS OBJECT
      ( v_ocpd_id number
      ,v_cob_date date
      ,v_legal_entity_name varchar2(40)
      ,v_business_area_name varchar2(20)
      ,v_profit_centre_name varchar2(20)
      ,v_desk_name varchar2(20)
      ,v_loc_name varchar2(20)
      ,v_boo_id NUMBER
      ,v_balsheet_grpdesc varchar2(40)
      ,v_pro_id NUMBER
      ,v_trade_ccy varchar2(5)
      ,v_ytd number
      ,v_ytdeur_inclfx number
      ,v_ltdccy number
      ,v_ltdeur number
      ,v_bs_type varchar2(10)
      );

      Based on the above TYPE, created the nested table below

      CREATE OR REPLACE TYPE APPL.rec_typ_tab_otcccxpnl is table of typ_tab_otcccxpnl;

      i have created the object for the nested table in package body declaration section. given below,

      l_otcccxpnl_token rec_typ_tab_otcccxpnl;

      While assigning values to the nested table variable iam getting " ORA-06531: Reference to uninitialized collection " error.

      Please guide me to solve this error. Thanks in advance.... Below is the package body...



      CREATE OR REPLACE PACKAGE BODY otcccxfunctions
      IS
      --------------------------------------------------------------------------------
      --------------------------------------------------------------------------------

      --------------------------------------------------------------------------------
      --------------------------------------------------------------------------------
      TYPE t_lines IS TABLE OF base_table.bt_line%TYPE
      INDEX BY PLS_INTEGER;

      --------------------------------------------------------------------------------
      --------------------------------------------------------------------------------
      gvt_tab_otcbook_structure t_tab_otcbook_structure;
      gvt_tab_otcbook_ignore t_tab_otcbook_ignore;
      gvt_tab_otcbook_import t_tab_otcbook_import;

      l_otcccxpnl_token           rec_typ_tab_otcccxpnl;
      --------------------------------------------------------------------------------
      --------------------------------------------------------------------------------

      --------------------------------------------------------------------------------
      --------------------------------------------------------------------------------
      FUNCTION import_ccx_pnl (cp IN ctrl_process%ROWTYPE
      --,g_tab_otcccxpnl_token in out t_tab_otcccxpnl_token
      --,c_rpd_rp_date in date
      )
      RETURN NUMBER
      IS

      --Type created to SUM ltd_ccy
      l_tab_otcccxpnl_token_ins t_tab_otcccxpnl_token;

      l_rec_count NUMBER := 0;
      v_count NUMBER := 0;
      v_return NUMBER := 0;
      v_func_return NUMBER;
      v_lines t_lines;
      v_portid VARCHAR2 (12);
      v_token_tab importutilities.tokentabletype;

      --Type created to assign final values after the SUM of ltd_ccy

      -----------------------------------------------------------------------------
      CURSOR lines
      IS
      SELECT bt_line v_inhalt
      FROM base_table
      WHERE ((bt_cp_id = cp.ID));
      -----------------------------------------------------------------------------
      BEGIN
      -----------------------------------------------------------------------------
      dbms_output.put_line('start');
      messagefunctions.add_function ('otcccxfunctions', 'import_ccx_pnl');
      v_return := 0;
      -----------------------------------------------------------------------------
      messagefunctions.set_action ('initialise global variable');
      otcccxfunctions.init;
      dbms_output.put_line('after init ');
      --
      v_signedoff := gorfunctions.is_signed_off (cp.cp_rpd_rp_date);
      --
      messagefunctions.add_action ('Exit if the file has signed-off');

      IF v_signedoff > 0
      THEN
      messagefunctions.create_message
      ('GlobalInformation',
      'GL already signed-off for this date.Please delete GL to run the process again.'
      );
      RAISE exc_abort_rollback;
      END IF;

      IF cp.cp_rpd_rp_date <>
      rpdfunctions.get_lst_rpt_date_of_mnth (cp.cp_rpd_rp_date)
      THEN
      messagefunctions.create_message
      ('GlobalInformation',
      'Report date should equal to last reportdate of its month'
      );
      RAISE exc_abort_rollback;
      END IF;

      OPEN lines;

      FETCH lines
      BULK COLLECT INTO v_lines;

      -----------------------------------------------------------------------------
      FOR v_line IN 1 .. v_lines.COUNT
      LOOP
      BEGIN
      v_lines (v_line) :=
      importutilities.rep_char_in_strings (v_lines (v_line),
      ',',
      '~',
      '"'
      );
      -----------------------------------------------------------------------------

      v_lines (v_line) :=
      importutilities.replace_character (v_lines (v_line), ',',
      v_sep);
      -----------------------------------------------------------------------------

      v_lines (v_line) :=
      importutilities.replace_character (v_lines (v_line), '~', ',');
      -----------------------------------------------------------------------------

      v_func_return :=
      importutilities.get_all_string_token (v_lines (v_line),
      v_sep,
      '"&',
      v_token_tab
      );
      --------------------------------------------------------------------------
      IF v_func_return <> 0
      THEN
      -----------------------------------------------------------------------
      RAISE exc_abort_rollback;
      -----------------------------------------------------------------------
      END IF;
      --------------------------------------------------------------------------
      IF TO_DATE (v_token_tab (1),
      'dd-Mon-RR',
      'NLS_DATE_LANGUAGE = ENGLISH'
      ) <> cp.cp_rpd_rp_date

      THEN
      v_counter := v_counter + 1;
      RAISE exc_abort_rollback;
      END IF;
      IF (upper(v_token_tab (6)) <> upper('London (Frankfurt)'))
      THEN

      RAISE exc_ignore_line;
      END IF;
      IF (NVL (boofunctions.get_id (v_token_tab (7),'OTC','FFT'), 0) = 0)
      THEN
      v_counter := v_counter + 1;
      RAISE exc_error_in_record;
      END IF;
      IF (gvt_tab_otcbook_structure(boofunctions.get_id (v_token_tab (7),'OTC','FFT')).v_boo_short_name is null) THEN
      v_counter := v_counter + 1;
      RAISE exc_error_in_record;

      END IF;
      IF (gvt_tab_otcbook_import(boofunctions.get_id (v_token_tab (7),'OTC','FFT')).v_boo_short_name is null) THEN
      IF (gvt_tab_otcbook_ignore(boofunctions.get_id (v_token_tab (7),'OTC','FFT')).v_boo_short_name) is null THEN

      v_counter := v_counter + 1;
      RAISE exc_error_in_record;
      ELSE
      RAISE exc_ignore_line;
      END IF;

      END IF;
      dbms_output.put_line('Checking balance sheet item is valid '||v_token_tab (9) );
      IF (profunctions.get_id_from_Desc (v_token_tab (9)) IS NULL)
      THEN
      v_counter := v_counter + 1;
      messagefunctions.create_message
      ('GlobalInformation',
      'Unknown Balance Sheet Item Description '
      || v_token_tab (9)
      );
      messagefunctions.set_action
      ('Checking message log count >= 100');
      RAISE exc_error_in_record;
      END IF;
      dbms_output.put_line('Checking currency number is valid '||v_token_tab (13) );
      messagefunctions.set_action ('Checking currency number is valid?');

      IF NOT (importutilities.is_legal_number (v_token_tab (13)))
      THEN
      v_counter := v_counter + 1;
      messagefunctions.create_message ('GlobalInformation',
      'Invalid LTD CCY number'
      );
      RAISE exc_abort_rollback;
      END IF;
      dbms_output.put_line('Checking currency is valid '||v_token_tab (10) );
      messagefunctions.set_action
      ('Checking trade currency is not valid');

      IF (cufunctions.is_legal_currency (v_token_tab (10)))
      THEN
      v_ltd_ccy := 0;
      messagefunctions.set_action
      ('Checking trade currency is technical currency');

      IF (v_token_tab (10) IN ('CNH', 'BRO'))
      THEN
      v_ltd_ccy :=
      v_token_tab (13)
      * frfunctions.get_rate (CASE
      WHEN v_token_tab (10) = 'CNH'
      THEN 'CNY'
      ELSE 'BRL'
      END,
      'RMS_EOD',
      cp.cp_rpd_rp_date
      );
      ---------------------------------------------------------------------
      messagefunctions.set_action ('v_ytd');
      l_otcccxpnl_token (v_line).v_ytd := v_token_tab (11);
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_ytdeur_inclfx');
      l_otcccxpnl_token (v_line).v_ytdeur_inclfx :=
      v_token_tab (12);
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_ltdccy');
      l_otcccxpnl_token (v_line).v_ltdccy := v_ltd_ccy;
      ---------------------------------------------------------------------
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_ltdeur');
      l_otcccxpnl_token (v_line).v_ltdeur := v_token_tab (14);
      ----------------------------------------------------------------------
      l_otcccxpnl_token (v_line).v_trade_ccy :=
      CASE
      WHEN v_token_tab (10) = 'CNH'
      THEN 'CNY'
      ELSE 'BRL'
      END;
      ELSIF (v_token_tab (10) = 'UDI')
      THEN
      messagefunctions.set_action ('v_ltdccy');
      l_otcccxpnl_token (v_line).v_ltdccy :=
      v_token_tab (13)
      * ( (frfunctions.get_rate ('UDI',
      'RMS_EOD',
      cp.cp_rpd_rp_date
      )
      )
      / (frfunctions.get_rate (v_token_tab (10),
      'RMS_EOD',
      cp.cp_rpd_rp_date
      )
      )
      );
      ---------------------------------------------------------------------
      messagefunctions.set_action ('v_ytd');
      l_otcccxpnl_token (v_line).v_ytd :=
      v_token_tab (11)
      * ( (frfunctions.get_rate ('UDI',
      'RMS_EOD',
      cp.cp_rpd_rp_date
      )
      )
      / (frfunctions.get_rate (v_token_tab (10),
      'RMS_EOD',
      cp.cp_rpd_rp_date
      )
      )
      );
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_ytdeur_inclfx');
      l_otcccxpnl_token (v_line).v_ytdeur_inclfx :=
      v_token_tab (12)
      * ( (frfunctions.get_rate ('UDI',
      'RMS_EOD',
      cp.cp_rpd_rp_date
      )
      )
      / (frfunctions.get_rate (v_token_tab (10),
      'RMS_EOD',
      cp.cp_rpd_rp_date
      )
      )
      );
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_ltdeur');
      l_otcccxpnl_token (v_line).v_ltdeur :=
      v_token_tab (14)
      * ( (frfunctions.get_rate ('UDI',
      'RMS_EOD',
      cp.cp_rpd_rp_date
      )
      )
      / (frfunctions.get_rate (v_token_tab (10),
      'RMS_EOD',
      cp.cp_rpd_rp_date
      )
      )
      );
      ----------------------------------------------------------------------
      l_otcccxpnl_token (v_line).v_trade_ccy := 'MXN';
      ELSE
      dbms_output.put_line('v_ltd_ccy = '||v_ltd_ccy );
      v_ltd_ccy := v_token_tab (13);
      ---------------------------------------------------------------------
      messagefunctions.set_action ('v_ytd');
      l_otcccxpnl_token (v_line).v_ytd := v_token_tab (11);
      dbms_output.put_line('l_otcccxpnl_token (v_line).v_ytd '||l_otcccxpnl_token (v_line).v_ytd );
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_ytdeur_inclfx');
      l_otcccxpnl_token (v_line).v_ytdeur_inclfx :=
      v_token_tab (12);
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_ltdccy');
      l_otcccxpnl_token (v_line).v_ltdccy := v_ltd_ccy;
      l_otcccxpnl_token (v_line).v_trade_ccy := v_token_tab (10);
      dbms_output.put_line('l_otcccxpnl_token (v_line).v_ltdccy '||l_otcccxpnl_token (v_line).v_ltdccy );
      dbms_output.put_line('l_otcccxpnl_token (v_line).v_trade_ccy '||l_otcccxpnl_token (v_line).v_trade_ccy );
      END IF;
      ELSE
      v_counter := v_counter + 1;
      messagefunctions.create_message ('GlobalInformation',
      'Invalid CCY'
      );
      RAISE exc_error_in_record;
      END IF;

      messagefunctions.set_action ('Assigning values to plsql table');
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_legal_entity_name');
      l_otcccxpnl_token (v_line).v_cob_date := v_token_tab (1);
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_legal_entity_name');
      l_otcccxpnl_token (v_line).v_legal_entity_name := v_token_tab (2);
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_business_area_name');
      l_otcccxpnl_token (v_line).v_business_area_name := v_token_tab (3);
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_profit_centre_name');
      l_otcccxpnl_token (v_line).v_profit_centre_name := v_token_tab (4);
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_desk_name');
      l_otcccxpnl_token (v_line).v_desk_name := v_token_tab (5);
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_loc_name');
      l_otcccxpnl_token (v_line).v_loc_name := v_token_tab (6);
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_portfolio_name');
      l_otcccxpnl_token (v_line).v_boo_id := boofunctions.get_id (v_token_tab (7),'OTC','FFT');
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_balsheet_grpdesc');
      l_otcccxpnl_token (v_line).v_balsheet_grpdesc := v_token_tab (8);
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_balsheet_itmdesc');
      l_otcccxpnl_token (v_line).v_pro_id := profunctions.get_id_from_Desc (v_token_tab (9));
      ----------------------------------------------------------------------
      messagefunctions.set_action ('v_bs_type');
      l_otcccxpnl_token (v_line).v_bs_type := v_token_tab (15);
      ----------------------------------------------------------------------
      EXCEPTION
      WHEN exc_error_in_record
      THEN
      IF (v_counter > 100)
      THEN
      RAISE exc_abort_rollback;
      END IF;
      WHEN exc_ignore_line
      THEN
      -------------------------------------------------------------------
      messagefunctions.set_action ('Skip the line');
      messagefunctions.create_message ('SuspectValue',
      ' The line '
      || v_line
      || ' is being ignored '
      || ' Record not imported. '
      );
      NULL;
      -------------------------------------------------------------------
      WHEN exc_invalid
      THEN
      -------------------------------------------------------------------
      messagefunctions.set_action ('delete invalid line');
      -------------------------------------------------------------------
      messagefunctions.create_message ('SuspectValue',
      ' The line '
      || v_line
      || ' is being ignored '
      || ' Record not imported. '
      );
      -------------------------------------------------------------------
      l_otcccxpnl_token.DELETE (v_line);
      -------------------------------------------------------------------
      WHEN OTHERS
      THEN
      -------------------------------------------------------------------
      messagefunctions.add_action ('Exception-Handling1');
      -------------------------------------------------------------------
      messagefunctions.create_message ('OracleError', SQLERRM);
      -------------------------------------------------------------------
      l_otcccxpnl_token.DELETE;
      v_lines.DELETE;
      RAISE exc_abort_rollback;
      -----------------------------------------------------------------------
      END;
      END LOOP;

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

      -----------------------------------------------------------------------------
      messagefunctions.set_action
      ('assigning extracted values to nested table (grec_typ_tab_otcccxpnl)'
      );

      -----------------------------------------------------------------------------
      IF (v_counter > 0)
      THEN
      RAISE exc_abort_rollback;
      END IF;

      v_count := NVL (l_otcccxpnl_token.COUNT, 0);
      -----------------------------------------------------------------------------
      messagefunctions.set_action
      ('Value selection from nested table (grec_typ_tab_otcccxpnl)');

      -----------------------------------------------------------------------------
      BEGIN
      FOR rec IN (SELECT tx.v_cob_date, tx.v_legal_entity_name,
      tx.v_business_area_name,
      tx.v_profit_centre_name, tx.v_desk_name,
      tx.v_loc_name, tx.v_boo_id,
      tx.v_balsheet_grpdesc, tx.v_pro_id,
      tx.v_trade_ccy, tx.v_ytd, tx.v_ytdeur_inclfx,
      tx.v_ltdeur, tx.v_bs_type,
      SUM (tx.v_ltdccy) AS v_ltdccy
      FROM TABLE (l_otcccxpnl_token) tx
      GROUP BY tx.v_cob_date,
      tx.v_legal_entity_name,
      tx.v_business_area_name,
      tx.v_profit_centre_name,
      tx.v_desk_name,
      tx.v_loc_name,
      tx.v_boo_id,
      tx.v_balsheet_grpdesc,
      tx.v_pro_id,
      tx.v_trade_ccy,
      tx.v_ytd,
      tx.v_ytdeur_inclfx,
      tx.v_ltdeur,
      tx.v_bs_type)
      LOOP
      l_rec_count := l_rec_count + 1;

      SELECT ocpd_seq.NEXTVAL
      INTO l_tab_otcccxpnl_token_ins (l_rec_count).v_ocpd_id
      FROM DUAL;

      l_tab_otcccxpnl_token_ins (l_rec_count).v_cob_date :=
      rec.v_cob_date;
      l_tab_otcccxpnl_token_ins (l_rec_count).v_legal_entity_name :=
      rec.v_legal_entity_name;
      l_tab_otcccxpnl_token_ins (l_rec_count).v_business_area_name :=
      rec.v_business_area_name;
      l_tab_otcccxpnl_token_ins (l_rec_count).v_profit_centre_name :=
      rec.v_profit_centre_name;
      l_tab_otcccxpnl_token_ins (l_rec_count).v_desk_name :=
      rec.v_desk_name;
      l_tab_otcccxpnl_token_ins (l_rec_count).v_loc_name :=
      rec.v_loc_name;
      l_tab_otcccxpnl_token_ins (l_rec_count).v_portfolio_name :=
      rec.v_boo_id;
      l_tab_otcccxpnl_token_ins (l_rec_count).v_balsheet_grpdesc :=
      rec.v_balsheet_grpdesc;
      l_tab_otcccxpnl_token_ins (l_rec_count).v_balsheet_itmdesc :=
      rec.v_pro_id;
      l_tab_otcccxpnl_token_ins (l_rec_count).v_trade_ccy :=
      rec.v_trade_ccy;
      l_tab_otcccxpnl_token_ins (l_rec_count).v_ytd := rec.v_ytd;
      l_tab_otcccxpnl_token_ins (l_rec_count).v_ytdeur_inclfx :=
      rec.v_ytdeur_inclfx;
      l_tab_otcccxpnl_token_ins (l_rec_count).v_ltdeur := rec.v_ltdeur;
      l_tab_otcccxpnl_token_ins (l_rec_count).v_bs_type := rec.v_bs_type;
      l_tab_otcccxpnl_token_ins (l_rec_count).v_ltdccy := rec.v_ltdccy;
      END LOOP;

      v_func_return :=
      ocpdfunctions.sql_create_record (l_tab_otcccxpnl_token_ins);
      COMMIT;
      EXCEPTION
      WHEN OTHERS
      THEN
      messagefunctions.add_action
      ('Exception on selecting values from nested table (grec_typ_tab_otcccxpnl)'
      );
      --------------------------------------------------------------------------

      --------------------------------------------------------------------------
      messagefunctions.create_message ('OracleError', SQLERRM);
      --------------------------------------------------------------------------
      messagefunctions.remove_function;
      RETURN 100;
      --------------------------------------------------------------------------
      END;

      -----------------------------------------------------------------------------
      messagefunctions.remove_function;
      RETURN (v_return);
      -----------------------------------------------------------------------------
      EXCEPTION
      WHEN exc_abort_rollback
      THEN
      ROLLBACK;
      --------------------------------------------------------------------------
      messagefunctions.add_action ('Exception-Handling EXC_ABORT_ROLLBACK');
      messagefunctions.remove_function;
      --------------------------------------------------------------------------
      RETURN 100;
      l_otcccxpnl_token.DELETE;
      v_lines.DELETE;
      --------------------------------------------------------------------------
      WHEN OTHERS
      THEN
      --------------------------------------------------------------------------
      messagefunctions.add_action ('Exception-Handling2');
      --------------------------------------------------------------------------
      l_otcccxpnl_token.DELETE;
      v_lines.DELETE;
      --------------------------------------------------------------------------
      messagefunctions.create_message ('OracleError', SQLERRM);
      messagefunctions.remove_function;
      --------------------------------------------------------------------------
      RETURN 100;
      --------------------------------------------------------------------------
      END import_ccx_pnl;

      END otcccxfunctions;
      /
        • 1. Re: ORA-06531: Reference to uninitialized collection
          Denes Kubicek
          This is a question for the PL/SQL forum.

          Denes Kubicek
          -------------------------------------------------------------------
          http://deneskubicek.blogspot.com/
          http://www.apress.com/9781430235125
          http://apex.oracle.com/pls/apex/f?p=31517:1
          http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
          -------------------------------------------------------------------