PL/SQL: ORA-01744: inappropriate INTO (2)-CodePudding

I have the following SQL statement:

DECLARE 
    OLD_CUSTOMER_ID VARCHAR(8); 
    NEW_CUSTOMER_ID VARCHAR(8); 
    T_ENTITY             T_CUSTOMER_GRNT_TEMPLATE_BASE.ENTITY%TYPE;
    C_CUSTOMER_ID        T_CUSTOMER_PROFILE_BASE.CUSTOMER_ID%TYPE;
    T_TEMPLATE_ID        T_CUSTOMER_GRNT_TEMPLATE_BASE.TEMPLATE_ID%TYPE;
    T_AUTHORIZATIONS     T_CUSTOMER_GRNT_TEMPLATE_BASE.AUTHORIZATIONS%TYPE;

BEGIN
    OLD_CUSTOMER_ID := '00000081';
    NEW_CUSTOMER_ID := '00000587';

    MERGE INTO T_CUSTOMER_GRNT_TEMPLATE_BASE TGT
    USING   (
            SELECT  T.ENTITY, C.CUSTOMER_ID, T.TEMPLATE_ID, T.AUTHORIZATIONS 
            INTO    T_ENTITY, C_CUSTOMER_ID, T_TEMPLATE_ID, T_AUTHORIZATIONS
            FROM    T_CUSTOMER_PROFILE_BASE C
            JOIN    T_CUSTOMER_GRNT_TEMPLATE_BASE T ON C.CUSTOMER_ID = T.CUSTOMER_ID
            WHERE   C.CUSTOMER_ID = OLD_CUSTOMER_ID
            ) SRC
    ON  (   TGT.ENTITY          = SRC.ENTITY
        AND TGT.CUSTOMER_ID     = SRC.CUSTOMER_ID
        AND TGT.TEMPLATE_ID     = SRC.TEMPLATE_ID
        )
    WHEN    MATCHED THEN UPDATE SET SRC.AUTHORIZATIONS = TGT.AUTHORIZATIONS
    WHEN    NOT MATCHED THEN INSERT VALUES (SRC.ENTITY, NEW_CUSTOMER_ID, SRC.TEMPLATE_ID, SRC.AUTHORIZATIONS)
    WHERE   TGT.CUSTOMER_ID = NEW_CUSTOMER_ID;
COMMIT;

END;

When running this statement I am getting this error:

Error report -
ORA-06550: line 16, column 13:
PL/SQL: ORA-01744: inappropriate INTO
ORA-06550: line 13, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

What am I doing wrong here? (I tried following instruction here https://www.tutorialspoint.com/plsql/plsql_variable_types.htm)

CodePudding user response：

The SELECT ... INTO syntax is for putting the results of a query into PL/SQL variables; so you could do this:

DECLARE 
    OLD_CUSTOMER_ID VARCHAR(8); 
    NEW_CUSTOMER_ID VARCHAR(8); 
    T_ENTITY             T_CUSTOMER_GRNT_TEMPLATE_BASE.ENTITY%TYPE;
    C_CUSTOMER_ID        T_CUSTOMER_PROFILE_BASE.CUSTOMER_ID%TYPE;
    T_TEMPLATE_ID        T_CUSTOMER_GRNT_TEMPLATE_BASE.TEMPLATE_ID%TYPE;
    T_AUTHORIZATIONS     T_CUSTOMER_GRNT_TEMPLATE_BASE.AUTHORIZATIONS%TYPE;

BEGIN
    OLD_CUSTOMER_ID := '00000081';
    NEW_CUSTOMER_ID := '00000587';

    SELECT  T.ENTITY, C.CUSTOMER_ID, T.TEMPLATE_ID, T.AUTHORIZATIONS 
    INTO    T_ENTITY, C_CUSTOMER_ID, T_TEMPLATE_ID, T_AUTHORIZATIONS
    FROM    T_CUSTOMER_PROFILE_BASE C
    JOIN    T_CUSTOMER_GRNT_TEMPLATE_BASE T ON C.CUSTOMER_ID = T.CUSTOMER_ID
    WHERE   C.CUSTOMER_ID = OLD_CUSTOMER_ID;

    -- do something with the variables
END;

But it is a PL/SQL extension of the SELECT statement. You aren't using a SELECT statement; you are using a MERGE statement which has a subquery - which means that SELECT is in a SQL context, not a PL/SQL context, and the PL/SQL extension isn't appropriate or meaningful.

So you can just remove the extra INTO clause, and the variables you don't need:

DECLARE 
    OLD_CUSTOMER_ID VARCHAR(8); 
    NEW_CUSTOMER_ID VARCHAR(8); 

BEGIN
    OLD_CUSTOMER_ID := '00000081';
    NEW_CUSTOMER_ID := '00000587';

    MERGE INTO T_CUSTOMER_GRNT_TEMPLATE_BASE TGT
    USING   (
            SELECT  T.ENTITY, C.CUSTOMER_ID, T.TEMPLATE_ID, T.AUTHORIZATIONS 
            FROM    T_CUSTOMER_PROFILE_BASE C
            JOIN    T_CUSTOMER_GRNT_TEMPLATE_BASE T ON C.CUSTOMER_ID = T.CUSTOMER_ID
            WHERE   C.CUSTOMER_ID = OLD_CUSTOMER_ID
            ) SRC
    ON  (   TGT.ENTITY          = SRC.ENTITY
        AND TGT.CUSTOMER_ID     = SRC.CUSTOMER_ID
        AND TGT.TEMPLATE_ID     = SRC.TEMPLATE_ID
        )
    WHEN    MATCHED THEN UPDATE SET AUTHORIZATIONS = SRC.AUTHORIZATIONS
    WHEN    NOT MATCHED THEN INSERT VALUES (SRC.ENTITY, NEW_CUSTOMER_ID, SRC.TEMPLATE_ID, SRC.AUTHORIZATIONS)
    WHERE   TGT.CUSTOMER_ID = NEW_CUSTOMER_ID;
COMMIT;

END;

(Not really relevant, but you should be using VARCHAR2 rather than VARCHAR for your variable data types; or T_CUSTOMER_GRNT_TEMPLATE_BASE.CUSTOMER_ID%TYPE.)