Fetch multiple rows into a variable

Question

I am attempting to fetch multiple unique account ID's (transactions.ID) and store in Variable v_Trxn. The problem is that the query count returns incorrect. Expected result is 300485 rows.

DECLARE
l_Partition transactions.Partitionkey%TYPE;
v_Trxn transactions.ID%TYPE;
l_Count number;
CURSOR v_Trxn_cur IS
SELECT ID
FROM transactions
WHERE Partitionkey > l_Partition;

BEGIN

SELECT dl_common.Get_Partitionkey(aInstitutionId => DL_COMMON.Get_InstitutionId , aDate => add_months(sysdate,-4)) INTO l_Partition FROM Dual;

OPEN v_Trxn_Cur;
LOOP
FETCH v_Trxn_Cur INTO v_Trxn;
EXIT WHEN v_Trxn_Cur%NOTFOUND;
END LOOP;


SELECT COUNT(UNIQUE(ID)) INTO l_Count FROM transactions WHERE ID In v_Trxn;
DBMS_OUTPUT.PUT_LINE(l_Count || ' Number of Unique Sernos');
CLOSE v_Trxn_Cur;
End;

Output: 1 Number of Unique Sernos

If I put the partitionkey directly into the WHERE clause I get the expected number of rows.

SELECT 
dl_common.Get_Partitionkey(aInstitutionId => DL_COMMON.Get_InstitutionId,
                                  aDate          => add_months(sysdate, -4)) PARTITION
FROM Dual;

Returns: PARTITION 2914365

SELECT 
COUNT(UNIQUE(ID)) C_COUNT 
FROM transactions C 
WHERE C.Partitionkey > 2914365

Returns: C_COUNT 300485

The expected number of rows is fetched.

Please explain what I am doing wrong.

Alex Poole · Accepted Answer · 2015-04-27 13:33:45Z

You're repeatedly selecting a single ID into your scalar variable. It can never hold more than a single value at a time. After the first iteration of your loop your variable holds the first ID returned by your cursor query (which is indeterminate because you don't have an order-by clause). After the second iteration your variable holds the second ID returned by the cursor query. It does not, and cannot, hold both values simultaneously.

To hold multiple values you would need to bulk select into a collection type, which needs to be declared at schema level (i.e. an SQL type, not a PL/SQL type) if you really want to use it as part of a later SQL query.

But storing hundreds of thousands of values in a collection is going to consume a significant amount of memory. Without knowing what you're really going to do with the values once you have them it isn't clear if that is just a price you'll have to pay; if you can do your work in batches (of, say, 1000 IDs at a time; if row-by-row processing is appropriate; or if you really want a join as part of a larger query, without holding them as a PL/SQL variable at all.

Rahul Parit · Accepted Answer · 2015-04-28 10:27:57Z

0

You need to use VARRAY OR NESTED TABLES FOR THIS PURPOSE.

     You can hold multiple values like this-

     Type var_dnames IS VARRAY(1000) transactions.ID%TYPE;
     v_Trxn var_dnames;

Now "v_Trxn" can hold multiple values from fetch statement.

answered Apr 28, 2015 at 10:27

Rahul Parit

3413 silver badges11 bronze badges

Collectives™ on Stack Overflow

Fetch multiple rows into a variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related