Discussions
Categories
- 382.5K All Categories
- 2.1K Data
- 212 Big Data Appliance
- 1.9K Data Science
- 448.4K Databases
- 221.1K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 25 Multilingual Engine
- 540 MySQL Community Space
- 469 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 499 SQLcl
- 3.9K SQL Developer Data Modeler
- 186.3K SQL & PL/SQL
- 21.1K SQL Developer
- 293.8K Development
- 7 Developer Projects
- 128 Programming Languages
- 290.5K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.6K Java
- 24 Java Learning Subscription
- 36.9K Database Connectivity
- 151 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.9K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 146 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 200 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 289 LiveLabs
- 36 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 168 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 234 Portuguese
PLSQL - How to store multiple values into a variable and return those stored values
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
Best 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.
Answers
-
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.
-
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
-
And what is file_one, file_two,... where do they come from?
SY.
-
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.