How to store return value of a PLSQL function into a variable-CodePudding

Im trying to store the return value of a function in a variable while declaring it and re-use the variable in insert statement.

Following is the minimum reproducible problem.

SET SERVEROUTPUT ON;
SET DEFINE OFF;

DECLARE
    FUNCTION getBase64Encoded(tnc IN VARCHAR2)
        RETURN CLOB
        IS
        encodedTnC CLOB;
    BEGIN
        encodedTnC := TO_CLOB(utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(tnc))));

        RETURN encodedTnC;
    END;

    INSERT_STATEMENT  VARCHAR2(10000) := 'INSERT INTO SOME_TABLE ' ||
                                         '(PRODUCT_ID, ENCODED_TEXT) ' ||
                                         'VALUES (:1, :2)';
    encodedTextForProd1 VARCHAR2(10000) := getBase64Encoded('some plain text here');
    encodedTextForProd2 VARCHAR2(10000) := getBase64Encoded('some plain text here again');

BEGIN

    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to X company', encodedTextForProd1;
    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to Y company', encodedTextForProd1;
    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to Z company', encodedTextForProd1;


    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to X company', encodedTextForProd2;
    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to Y company', encodedTextForProd2;
    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to Z company', encodedTextForProd2;


    COMMIT;


EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END;
/

I'm calling getBase64Encoded() function and storing it in variable encodedTextForProd1 and use it for inserting multiple rows.

But I get the following error:


[2022-08-18 19:13:54] [65000][6550]
[2022-08-18 19:13:54]   ORA-06550: line 12, column 5:
[2022-08-18 19:13:54]   PLS-00103: Encountered the symbol "encodedTextForProd1" when expecting one of the following:
[2022-08-18 19:13:54]      begin function pragma procedure

However, If I call the method in the insert statement, it works without any problem.

    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'anyIdHere', getBase64Encoded('Some plain text');

I want to store the function return value in a variable because I have a lot of rows using the same value. So storing the result will reduce the function calls and boost the performance. How do I make this happen?

CodePudding user response：

Function definitions have to come after variable declarations, so you need to swap things around; but doing so will mean you current assignments that call the function won't be valid, as the function isn't yet in scope. So you will also need to separate the variable declaration and assignment:

DECLARE
    INSERT_STATEMENT  VARCHAR2(10000) := 'INSERT INTO SOME_TABLE ' ||
                                         '(PRODUCT_ID, ENCODED_TEXT) ' ||
                                         'VALUES (:1, :2)';
    encodedTextForProd1 VARCHAR2(10000);
    encodedTextForProd2 VARCHAR2(10000);

    FUNCTION getBase64Encoded(tnc IN VARCHAR2)
        RETURN CLOB
        IS
        encodedTnC CLOB;
    BEGIN
        encodedTnC := TO_CLOB(utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(tnc))));

        RETURN encodedTnC;
    END;

BEGIN

    encodedTextForProd1 := getBase64Encoded('some plain text here');
    encodedTextForProd2 := getBase64Encoded('some plain text here again');

    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to X company', encodedTextForProd1;
    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to Y company', encodedTextForProd1;
    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to Z company', encodedTextForProd1;


    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to X company', encodedTextForProd2;
    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to Y company', encodedTextForProd2;
    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to Z company', encodedTextForProd2;


    COMMIT;


EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END;
/

If you particularly want to keep the variable declaration and assignment together, you could also declare the function before the variables, and define it afterwards (see nested subprograms and forward declaration):

DECLARE
    -- declare function only so it is in scope for following variables
    FUNCTION getBase64Encoded(tnc IN VARCHAR2)
        RETURN CLOB;

    INSERT_STATEMENT  VARCHAR2(10000) := 'INSERT INTO SOME_TABLE ' ||
                                         '(PRODUCT_ID, ENCODED_TEXT) ' ||
                                         'VALUES (:1, :2)';
    encodedTextForProd1 VARCHAR2(10000) := getBase64Encoded('some plain text here');
    encodedTextForProd2 VARCHAR2(10000) := getBase64Encoded('some plain text here again');

    -- define previously declared function
    FUNCTION getBase64Encoded(tnc IN VARCHAR2)
        RETURN CLOB
        IS
        encodedTnC CLOB;
    BEGIN
        encodedTnC := TO_CLOB(utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(tnc))));

        RETURN encodedTnC;
    END;

BEGIN

    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to X company', encodedTextForProd1;
    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to Y company', encodedTextForProd1;
    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to Z company', encodedTextForProd1;


    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to X company', encodedTextForProd2;
    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to Y company', encodedTextForProd2;
    EXECUTE IMMEDIATE INSERT_STATEMENT USING 'productId when sold to Z company', encodedTextForProd2;


    COMMIT;


EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END;
/

db<>fiddle

Presumably this is a demonstration as there's no need for dynamic SQL in what you've shown; and you are silently squashing any runtime errors that are thrown, which you hopefully wouldn't really do.