Create PL/SQL script with 2 cursors, a parameter and give results from a table?

Question

I need to create a script that puts a key number from table A (which will be used as a parameter later), then flow that parameter or key number into a query and then dump those results into a holding record or table for later manipulation and such. Because each fetch has more than 1 row (in reality there are 6 rows per query results or per claim key) I decided to use the Bulk Collect clause. Though my initial test on a different database worked, I have not yet figured out why the real script is not working.

Here is the test script that I used:

DECLARE

--Cursors--
CURSOR prod_id is select distinct(product_id) from product order by 1 asc;

CURSOR cursorValue(p_product_id NUMBER) IS
        SELECT h.product_description,o.company_short_name
        FROM company o,product h
        WHERE o.product_id =h.product_id
        AND h.product_id =p_product_id
        AND h.product_id IS NOT NULL
        ORDER by 2;

    --Table to store Cursor data--        
    TYPE indx IS TABLE OF cursorValue%ROWTYPE
    INDEX BY PLS_INTEGER;
    indx_tab  indx;

    ---Variable objects---
    TotalIDs PLS_INTEGER;
    TotalRows PLS_INTEGER := 0 ;

BEGIN
      --PARAMETER CURSOR RUNS---
    FOR prod_id2 in prod_id LOOP
    dbms_output.put_line('Product ID: ' || prod_id2.product_id);
    TotalIDs := prod_id%ROWCOUNT;

          --FLOW PARAMETER TO SECOND CURSOR--
        Open cursorValue(prod_id2.product_id);
        Loop 
        Fetch cursorValue Bulk collect into indx_tab;

          ---data dump into table---
        --dbms_output.put_line('PROD Description: ' || indx_tab.product_description|| ' ' ||'Company Name'|| indx_tab.company_short_name);
        TotalRows := TotalRows + cursorValue%ROWCOUNT;
        EXIT WHEN cursorValue%NOTFOUND;
        End Loop;
        CLOSE cursorValue;
    End Loop;    
dbms_output.put_line('Product ID Total: ' || TotalIDs);
dbms_output.put_line('Description Rows: ' || TotalRows);
END;


Test Script Results:
anonymous block completed
Product ID: 1
Product ID: 2
Product ID: 3
Product ID: 4
Product ID: 5
Product ID Total: 5
Description Rows: 6

Update: Marking question as "answered" Thanks.

Answer 1

The first error is on line 7. On line 4 you have:

  CURSOR CUR_CLAIMNUM IS
                      SELECT DISTINCT(CLAIM_NO)FROM R7_OPENCLAIMS;

... and that seems to be valid, so your column name is CLAIM_NO. On line 7:

  CURSOR OPEN_CLAIMS (CLAIM_NUM  R7_OPENCLAIMS.CLAIM_NUM%TYPE)  IS

... so you've mistyped the column name as CLAIM_NUM, which doesn't exist in that table. Which is what the error message is telling you, really.

The other errors are because the cursor is invalid, becuase of that typo.

When you open the second cursor you have the same name confusion:

          OPEN OPEN_CLAIMS (CUR_CLAIMNUM2.CLAIM_NUM);

... which fails because the cursor is querying CLAIMNO not CLAIMNUM; except here it's further confused by the distinct. You haven't aliased the column name so Oracle applies one, which you could refer to, but it's simpler to add your own:

  CURSOR CUR_CLAIMNUM IS
                      SELECT DISTINCT(CLAIM_NO) AS CLAIM_NO FROM R7_OPENCLAIMS;

and then

          OPEN OPEN_CLAIMS (CUR_CLAIMNUM2.CLAIM_NO);

But I'd suggest you also change the cursor name from CUR_CLAIMNUM to CUR_CLAIM_NO, both in the definition and the loop declaration. And having the cursor iterator called CUR_CLAIMNUM2 is odd as it suggests that is itself a cursor name; maybe something like ROW_CLAIM_NO would be clearer.