SELECT INTO with UNION

[szeiss]

Is it possible to use a SELECT INTO clause with a UNION?

SELECT
table1.COLUMN
INTO v_column
FROM
TABLE1
WHERE
.....
UNION
SELECT
table2.COLUMN
INTO v_column
FROM
TABLE2
WHERE
.....

I get the following error ORA-01744:, inappropriate INTO

I want to insert v_column into a table

Thanks,
Sherry

 

[Dagon]

You could do it by means of an inline view:

DECLARE
v_column number;
BEGIN
select col1
into v_column
from
(SELECT
1 as col1
FROM DUAL
UNION
SELECT 2 as col2
FROM DUAL);
END;

The code fragment also illustrates the problem with your whole approach. SELECT..INTO will only ever allow a single row to be returned but a UNION, by its nature, is likely to return more than one. You need to look at using a CURSOR instead.