Select several rows into several variables - Oracle PL/SQL

Question

I have one select statement that returns a query like this:

SELECT value FROM dummy WHERE condition = 1;

    ROW | value
   -----|-----
    1   | val1
    2   | val2
    3   | val3
    4   | val4

I have 4 variables

r1, r2, r3, r4

What is the cleanest way to assign those variables the values of my query? Can I use a SELECT INTO statement?

Define the array x and fill it using select ... bulk collect into x. — Pavel Gatnar
– Pavel Gatnar, Commented Feb 18, 2020 at 15:12
You had better add and rownum < 5 to your where clause. I hope this is concept code and not production code. — Gary_W
– Gary_W, Commented Feb 18, 2020 at 15:26
It's not going anywhere near a productive system, but thanks for your concern and advice. — mre
– mre, Commented Feb 18, 2020 at 15:35

Gordon Linoff · Accepted Answer · 2020-02-18 15:09:21Z

2

One method is:

SELECT p_v1 = MAX(CASE WHEN row = 1 THEN value end),
       p_v2 = MAX(CASE WHEN row = 2 THEN value end),
       p_v3 = MAX(CASE WHEN row = 3 THEN value end),
       p_v4 = MAX(CASE WHEN row = 4 THEN value end)
FROM dummy
WHERE condition = 1;

This is an aggregation query that returns one row. If a particular row is missing, then the value will be NULL.

answered Feb 18, 2020 at 15:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mre Over a year ago

I had to replace row with rownum for this to work, but it's a nice solution.

Popeye · Accepted Answer · 2020-02-18 15:19:48Z

1

You can uae PIVOT as following;

Select v1, v2, v3, v4 
       Into val1, val2, val3, val4
From (SELECT value FROM dummy WHERE condition = 1)
Pivot
(Max(value) for row in (1 as v1, 2 as v2, 3 as v3, 4 as v4))

Cheers!!

answered Feb 18, 2020 at 15:19

Popeye

36k4 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

Select several rows into several variables - Oracle PL/SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related