Cursor values not accessible

Question

I have written the following piece of code:

PROCEDURE main(errbuf              OUT VARCHAR2,
             retcode             OUT VARCHAR2,
             p_parent_request_id in NUMBER) IS


CURSOR cur_supplier_num  IS
  SELECT stg.rowid, stg.LEGACY_VENDOR_NUMBER, stg.SITE_IDENTIFIER
    FROM XXCONV.GEAS_PA_COSTING_STG STG
  WHERE RECORD_PROCESSING_STATUS IN (1, 2, 5)
  AND STG.LEGACY_VENDOR_NUMBER IS NOT NULL;

TYPE update_line IS TABLE OF   cur_supplier_num%ROWTYPE;
g_supplier_update update_line := update_line();

/* End of addtion 3-Oct-14*/

v_internal_request_id   NUMBER;
v_org_id                NUMBER;
v_vendor_id             NUMBER;
v_parent_request_id     NUMBER;
v_return                NUMBER;
v_record_process_status NUMBER;
v_project_id            NUMBER;
v_task_id               NUMBER;
v_validation_var        VARCHAR2(20);
v_val_user_var          VARCHAR2(20);
v_val_emp_var           VARCHAR2(20);
v_val_exp_type_var      VARCHAR2(20);
v_val_exp_type_link_var VARCHAR2(20);
v_val_proj_name_var     VARCHAR2(20);
v_val_gl_date_var       VARCHAR2(20);
v_val_exp_end_var       VARCHAR2(20);
v_val_exp_item_var      VARCHAR2(20);
v_val_cr_code_var       VARCHAR2(20);
v_val_dr_code_var       VARCHAR2(20);
v_val_curr_var          VARCHAR2(20);
v_r12_project_number    VARCHAR2(240);

BEGIN



dynamic_log('Validation Program Start ');  





BEGIN



    OPEN cur_supplier_num;
    LOOP
        FETCH cur_supplier_num BULK COLLECT
        INTO g_supplier_update LIMIT 2000;

     CLOSE  cur_supplier_num;

    IF g_supplier_update.COUNT > 0
    THEN

       FORALL i IN 1..g_supplier_update.COUNT
           UPDATE XXCONV.GEAS_PA_COSTING_STG stg
           set R12_VENDOR_NUMBER  = (
                            select r12_supplier_code
                            FROM xxconv.supplier_legacy_r12_xref xref
                            WHERE xref.legacy_supplier_code =                                                                                                                              g_supplier_update.Legacy_VENDOR_NUMBER
                            AND xref.site_identifier = g_supplier_update.site_identifier)
           WHERE stg.Legacy_VENDOR_NUMBER = g_supplier_update.LEGACY_VENDOR_NUMBER;
     END IF;
 END LOOP; 
END;

When I compile the code, I get the error message:

Error(285,46): PL/SQL: ORA-00904: "G_SUPPLIER_UPDATE"."LEGACY_VENDOR_NUMBER": invalid identifier
Error(285,64): PLS-00302: component 'LEGACY_VENDOR_NUMBER' must be declared

I'm not able to understand why LEGACY_VENDOR_NUMBER can't be recognised. Any help on this would be welcome

Vivek

Answer 1

You have use it like g_supplier_update(I).XXXXXX to access a collection, I here is the index generated by the FOR LOOP

FORALL i IN 1..g_supplier_update.COUNT
           UPDATE XXCONV.GEAS_PA_COSTING_STG stg
           set R12_VENDOR_NUMBER  = (
                            select r12_supplier_code
                            FROM xxconv.supplier_legacy_r12_xref xref
                            WHERE xref.legacy_supplier_code = g_supplier_update(I).Legacy_VENDOR_NUMBER
                            AND xref.site_identifier = g_supplier_update(I).site_identifier)
           WHERE stg.Legacy_VENDOR_NUMBER = g_supplier_update(I).LEGACY_VENDOR_NUMBER;