Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PLSQL - How to store multiple values into a variable and return those stored values

Jasper TanglibMay 25 2021

Hi,
I have a sample code below. This will obviously result to an error but the idea is, I want to store values depending on IF conditions into a variable by appending whatever value is already there ,and then use that variable in the end to show all the accumulated values that were stored. How can I achieve this?
I have already tried bulk collect and cursor, but if you see my sample below, bulk collect and cursor does not work since they will overwrite the values in the variable instead of adding new values.

DECLARE
v_var varchar2(100);
BEGIN

**SELECT xt**   
**INTO v\_var**  
**FROM data\_source**  
**WHERE xt = 'myData';**  

**IF v\_var IS NOT NULL THEN**  
    **-- store myData value into v\_var**  
**END IF;**  

**SELECT xt**   
**INTO v\_var**  
**FROM data\_source**  
**WHERE xt = 'myData2';**  

**IF v\_var IS NOT NULL THEN**  
    **-- store myData2 value into v\_var**  
**END IF;**  

**SELECT xt**   
**INTO v\_var**  
**FROM data\_source**  
**WHERE xt = 'myData3';**  
  
**IF v\_var IS NOT NULL THEN**  
    **-- store myData3 value into v\_var**  
**END IF;**

FOR i IN v_var.count
LOOP
dbms_output.put_line('Values from v_var that are not null' || v_var(i));
END LOOP;
END;

Any ideas or suggestions are appreciated!
- Jazz
This post has been answered by Solomon Yakobson on May 25 2021
Jump to Answer

Comments

Solomon Yakobson

Why not simply using cursor?

DECLARE
    CURSOR V_CUR
      IS
        SELECT  XT
          FROM  DATA_SOURCE
          WHERE XT IN (
                       'myData',
                       'myData2',
                       'myData3'
                      );
BEGIN
    FOR V_REC IN V_CUR LOOP
      DBMS_OUTPUT.PUT_LINE('Values from v_var that are not null' || V_REC.XT);
    END LOOP;
END;
/

SY.

Jasper Tanglib

Because I need to do some sort of preprocessing, it should only store values that have passed a specific condition. Something like this.
DECLARE
v_var varchar2(100);
BEGIN
IF file_one IS NULL THEN
NULL;
ELSIF NOT NULL THEN
-- add value myData1
SELECT XT
INTO v_var
FROM DATA_SOURCE
WHERE XT = 'myData1'
END IF;

**IF file\_two IS NULL THEN**  
    **NULL;**  
**ELSIF NOT NULL THEN**  
    **-- add value myData2**  
    **SELECT XT**  
    **INTO v\_var**  
    **FROM DATA\_SOURCE**  
    **WHERE XT = 'myData2'**  
**END IF;**  

**IF file\_three IS NULL THEN**  
    **NULL;**  
**ELSIF NOT NULL THEN**  
    **-- store value myData3**  
    **SELECT XT**  
    **INTO v\_var**  
    **FROM DATA\_SOURCE**  
    **WHERE XT = 'myData3'**  
**END IF;**  
  
**FOR i IN v\_var LOOP**  
  **DBMS\_OUTPUT.PUT\_LINE('Values from v\_var that are not null: ' || v\_var(i);**  
**END LOOP;**

END;
Based from the code, assuming file_one and file_three are not null while file_two is null, then output should only be myData1 and myData3.

Not related but, how did you paste your code like that. It looks neat

Solomon Yakobson

And what is file_one, file_two,... where do they come from?
SY.

Solomon Yakobson
Answer

Anyway:

DECLARE
    CURSOR V_CUR
      IS
        SELECT  XT
          FROM  DATA_SOURCE
          WHERE XT IN (
                       NVL2(file_one,'myData',NULL),
                       NVL2(file_two,'myData2',NULL),
                       NVL2(file_three,'myData3',NULL)
                      );
BEGIN
    FOR V_REC IN V_CUR LOOP
      DBMS_OUTPUT.PUT_LINE('Values from v_var that are not null' || V_REC.XT);
    END LOOP;
END;
/

SY.

Marked as Answer by Jasper Tanglib · Sep 30 2021
1 - 4

Post Details

Added on May 25 2021
#code, #oracle, #plsql, #sql
4 comments
15,739 views