Skip to Main Content

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

Post Details

Added on May 25 2021
4 comments
15,731 views