Reference to uninitialized collection - Help!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Reference to uninitialized collection - Help!

  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Reference to uninitialized collection - Help!

    I am having an issue returning a collection back from a function. It works comletely fine if I test the function with the same user (xxssi). As soon as I change the user to apps it fails with the error:

    ORA-06512: at "XXSSI.XXSSI_PRODUCT_RESTRICT_PKG", line 434
    ORA-06512: at line 12
    06531. 00000 - "Reference to uninitialized collection"

    A little history:
    1) We are returning a table of number which is what the inventory_item_id is defined as...
    Type pr_type IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE;

    2) I originally had this as a parameter to my function but it was returning over 64K in results which blows up a PL/SQL limit so I changed to a return from the function

    3) The function is defined as this:
    FUNCTION get_restricted_product_list(vc_return_code OUT VARCHAR2, i_customer_id IN NUMBER, i_store_id IN NUMBER, i_log_level IN NUMBER DEFAULT 2) RETURN pr_type as x_relatedid_tbl pr_type;

    4) The code to populate the table is fairly straight forward:
    -- Fill in the result table...
    SELECT COUNT(1)
    INTO i_cust_prod_cnt
    FROM xxssi_assignment_temp_tbl;
    x_relatedid_tbl := pr_type();
    x_relatedid_tbl.extend(i_cust_prod_cnt);
    IF i_cust_prod_cnt > 0 THEN
    -- initialize return value x_relatedid_tbl

    l_table_index := 1;
    ELSE
    x_relatedid_tbl := NULL;
    END IF;
    FOR j IN
    (SELECT item_id
    FROM xxssi_assignment_temp_tbl)
    LOOP
    xxssi_log_util_pkg.debug('List of Intersect Items :' || j.item_id);
    --x_relatedid_tbl.extend;
    x_relatedid_tbl(l_table_index) := j.item_id;
    l_table_index := l_table_index + 1;
    END LOOP;
    DBMS_OUTPUT.put_line('Size of List: ' || i_temp_sum || ' with count of: ' || x_relatedid_tbl.count);

    5) I run the code from sql developer under the xxssi user and it works perfectly with the following driver:
    DECLARE
    VC_RETURN_CODE VARCHAR2(200);
    I_CUSTOMER_ID NUMBER;
    I_STORE_ID NUMBER;
    I_LOG_LEVEL NUMBER;
    v_Return XXSSI.XXSSI_PRODUCT_RESTRICT_PKG.PR_TYPE;
    BEGIN
    I_CUSTOMER_ID := 350052;
    I_STORE_ID := 10065;
    I_LOG_LEVEL := 2;

    v_Return := XXSSI_PRODUCT_RESTRICT_PKG.GET_RESTRICTED_PRODUCT_LIST(
    VC_RETURN_CODE => VC_RETURN_CODE,
    I_CUSTOMER_ID => I_CUSTOMER_ID,
    I_STORE_ID => I_STORE_ID,
    I_LOG_LEVEL => I_LOG_LEVEL
    );
    DBMS_OUTPUT.PUT_LINE('VC_RETURN_CODE = ' || VC_RETURN_CODE);
    -- Modify the code to output the variable
    -- DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
    END;

    6) If I run the exact same code from the apps user it fails with the message:

    Error starting at line 1 in command:
    DECLARE
    VC_RETURN_CODE VARCHAR2(200);
    I_CUSTOMER_ID NUMBER;
    I_STORE_ID NUMBER;
    I_LOG_LEVEL NUMBER;
    v_Return XXSSI.XXSSI_PRODUCT_RESTRICT_PKG.PR_TYPE;
    BEGIN
    I_CUSTOMER_ID := 350052;
    I_STORE_ID := 10065;
    I_LOG_LEVEL := 2;

    v_Return := XXSSI_PRODUCT_RESTRICT_PKG.GET_RESTRICTED_PRODUCT_LIST(
    VC_RETURN_CODE => VC_RETURN_CODE,
    I_CUSTOMER_ID => I_CUSTOMER_ID,
    I_STORE_ID => I_STORE_ID,
    I_LOG_LEVEL => I_LOG_LEVEL
    );
    DBMS_OUTPUT.PUT_LINE('VC_RETURN_CODE = ' || VC_RETURN_CODE);
    -- Modify the code to output the variable
    -- DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
    END;
    Error report:
    ORA-06531: Reference to uninitialized collection
    ORA-06512: at "XXSSI.XXSSI_PRODUCT_RESTRICT_PKG", line 434
    ORA-06512: at line 12
    06531. 00000 - "Reference to uninitialized collection"
    *Cause: An element or member function of a nested table or varray
    was referenced (where an initialized collection is needed)
    without the collection having been initialized.
    *Action: Initialize the collection with an appropriate constructor
    or whole-object assignment.


    7) I did grant execute permissions to apps and the function worked completely fine when it did not have this as a return parameter but as a variable (except for the large list over 64k as I mentioned)

    Any ideas?

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Make sure that the user running the procedure can run the underlying query. You can also just return a blob or clob.
    this space intentionally left blank

  3. #3
    Join Date
    Feb 2009
    Posts
    2
    Well, I think I determined the problem. The records I am pulling inside the function are from a global temporary table and when I return them it must be going out of scope (session is over) for that user. This is causing the issue. It worked fine when it was a variable to the function but now that I have it as a return variable it is not working.

    I have tried the following:
    1) Return the data in a VARRAY instead of a TABLE OF NUMBER - No luck
    2) Changed the function to return a reference cursor and then modified the function to return records from a real table while joining to my temporary table... This did not work:
    SELECT msi.inventory_item_id
    FROM mtl_system_items_b msi
    ,(SELECT item_id
    FROM xxssi_assignment_temp_tbl) a
    WHERE msi.organization_id = 105
    AND msi.inventory_item_id = a.item_id;


    Is there any type of way to return this and do a "copy on write" or something like that where we can allocate new memory so it does not lose the reference?

    I modified the code from #2 above to go strictly at a real table and did not change anything else (kept the ref cursor) and it worked fine.

    Any ideas?

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    If you load the rows into a PL/SQL collection it will be in memory and not relying on the reference to the global temp table remaining.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width