Select multiple columns into multiple variables

sql oracle plsql select-into
Hélder Gonçalves picture Hélder Gonçalves · Aug 7, 2013 · Viewed 89.3k times · Source

How can I do in one select with multiple columns and put each column in a variable?

Something like this:

--code here
V_DATE1 T1.DATE1%TYPE;
V_DATE2 T1.DATE2%TYPE;
V_DATE3 T1.DATE3%TYPE;

SELECT T1.DATE1 INTO V_DATE1, T1.DATE2 INTO V_DATE2, T1.DATE3 INTO V_DATE3
FROM T1
WHERE ID='X';

--code here

Answer

Bob Jarvis - Reinstate Monica picture Bob Jarvis - Reinstate Monica · Aug 7, 2013

Your query should be:

SELECT T1.DATE1, T1.DATE2, T1.DATE3
INTO V_DATE1, V_DATE2, V_DATE3
FROM T1
WHERE ID='X';

Related questions

How to use SELECT... INTO with a JOIN?

I have the following example code DECLARE myRow table%rowtype myVar table2.column%type BEGIN SELECT table.col1, table.col3, table.col4, table2.column INTO myRow FROM table JOIN table2 On table.col6 = table2.col1; END; How can I refactor …

Read More
SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More