Error: INTO clause is expected in this SELECT statement

Question

I'm trying to run different code based on the db instance using an if statement but I keep getting this error:

Error report - ORA-06550: line 9, column 8: PLS-00428: an INTO clause is expected in this SELECT statement 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

I'm trying to use a system variable to determine if i'm in prod, qa, dev, then run various statements based on where I am.

This is what I've tried:

DECLARE 
        ENV VARCHAR(256);
    BEGIN
        SELECT sys_context('USERENV','DB_NAME') AS Instance
        INTO ENV
        FROM dual;

        IF ENV = 'CSTMP' THEN
            CREATE MV_TEST_CSTMP ...;
        ELSIF ENV = 'PROD' THEN
            CREATE MV_TEST_PROD ...;
        ELSE
            CREATE MV_DEFAULT ...;
        END IF;
    END;

But I get: PLS-00428: an INTO clause is expected in this SELECT statement

EDIT: I've also tried this

VAR ENV VARCHAR(255);

SELECT sys_context('USERENV','DB_NAME') AS Instance
INTO ENV
FROM DUAL;

IF ENV = 'CSTMP' THEN
    execute immediate 'CREATE MATERIALIZED VIEW TEST_CSTMP
    USING INDEX 
    REFRESH 
    NEXT trunc(SYSDATE, ''hh'') + 1/24       
    FORCE 
    USING DEFAULT LOCAL ROLLBACK SEGMENT 
    ENABLE QUERY REWRITE AS 
    SELECT count(m.work_ownerid)
            FROM MV_WWORK_SHRUNK m
            WHERE WORK_STATUS = 2';

ELSIF ENV = 'PROD' THEN
    execute immediate 'CREATE MATERIALIZED VIEW PROD
    USING INDEX 
    REFRESH 
    NEXT trunc(SYSDATE, ''hh'') + 1/24       
    FORCE 
    USING DEFAULT LOCAL ROLLBACK SEGMENT 
    ENABLE QUERY REWRITE AS 
    SELECT count(m.work_ownerid)
            FROM MV_WWORK_SHRUNK m
            WHERE WORK_STATUS = 3';

ELSE
    execute immediate 'CREATE MATERIALIZED VIEW TEST_ELSE
    USING INDEX 
    REFRESH 
    NEXT trunc(SYSDATE, ''hh'') + 1/24       
    FORCE 
    USING DEFAULT LOCAL ROLLBACK SEGMENT 
    ENABLE QUERY REWRITE AS 
    SELECT count(m.work_ownerid)
            FROM MV_WWORK_SHRUNK m
            WHERE WORK_STATUS = 4';
END IF;

: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended"

What don't you understand? A programming block needs for all queries to return values into variables. — Gordon Linoff
What if I don't need that, is there a way to write an if/else where I'm not storing something in a variable? — Batman
I don't believe DDL is directly allowed in PL/SQL the way you wrote your CREATE statements. — access_granted
So is there no way of creating tables, views, etc based on conditions? — Batman
You must use an execute immediate command to create your views. However, you should get a different error code for this mistake, not the one you reported. Is that the EXACT code you are running? — mathguy

Batman Batman · Accepted Answer · 2018-03-28T20:50:35

Got it working like this:

DECLARE 
 ENV VARCHAR(255);

BEGIN

    SELECT sys_context('USERENV','DB_NAME') AS Instance
    INTO ENV
    FROM DUAL;

    IF ENV = 'CSTMP' THEN
        execute immediate 'CREATE MATERIALIZED VIEW TEST_CSTMP
        USING INDEX 
        REFRESH 
        NEXT trunc(SYSDATE, ''hh'') + 1/24       
        FORCE 
        USING DEFAULT LOCAL ROLLBACK SEGMENT 
        ENABLE QUERY REWRITE AS 
        SELECT count(m.work_ownerid)
                FROM MV_WWORK_SHRUNK m
                WHERE WORK_STATUS = 2';

    ELSIF ENV = 'PROD' THEN
        execute immediate 'CREATE MATERIALIZED VIEW PROD
        USING INDEX 
        REFRESH 
        NEXT trunc(SYSDATE, ''hh'') + 1/24       
        FORCE 
        USING DEFAULT LOCAL ROLLBACK SEGMENT 
        ENABLE QUERY REWRITE AS 
        SELECT count(m.work_ownerid)
                FROM MV_WWORK_SHRUNK m
                WHERE WORK_STATUS = 3';

    ELSE
        execute immediate 'CREATE MATERIALIZED VIEW TEST_ELSE
        USING INDEX 
        REFRESH 
        NEXT trunc(SYSDATE, ''hh'') + 1/24       
        FORCE 
        USING DEFAULT LOCAL ROLLBACK SEGMENT 
        ENABLE QUERY REWRITE AS 
        SELECT count(m.work_ownerid)
                FROM MV_WWORK_SHRUNK m
                WHERE WORK_STATUS = 4';
    END IF;
END;

Error: INTO clause is expected in this SELECT statement

1 Answers