PLSQL - How to store multiple values into a variable and return those stored values

Jasper Tanglib

Hi,

I have a sample code below. This will obviously result to an error but the idea is, I want to store values depending on IF conditions into a variable by appending whatever value is already there ,and then use that variable in the end to show all the accumulated values that were stored. How can I achieve this?

I have already tried bulk collect and cursor, but if you see my sample below, bulk collect and cursor does not work since they will overwrite the values in the variable instead of adding new values.

DECLARE

    v_var varchar2(100);

BEGIN

    SELECT xt 

    INTO v_var

    FROM data_source

    WHERE xt = 'myData';

    IF v_var IS NOT NULL THEN

        -- store myData value into v_var

    END IF;

    SELECT xt 

    INTO v_var

    FROM data_source

    WHERE xt = 'myData2';

    IF v_var IS NOT NULL THEN

        -- store myData2 value into v_var

    END IF;

    SELECT xt 

    INTO v_var

    FROM data_source

    WHERE xt = 'myData3';

    

    IF v_var IS NOT NULL THEN

        -- store myData3 value into v_var

    END IF;

FOR i IN v_var.count

    LOOP

        dbms_output.put_line('Values from v_var that are not null' || v_var(i));

    END LOOP;

END;

Any ideas or suggestions are appreciated!

- Jazz

Solomon Yakobson

Anyway:

DECLARE
    CURSOR V_CUR
      IS
        SELECT  XT
          FROM  DATA_SOURCE
          WHERE XT IN (
                       NVL2(file_one,'myData',NULL),
                       NVL2(file_two,'myData2',NULL),
                       NVL2(file_three,'myData3',NULL)
                      );
BEGIN
    FOR V_REC IN V_CUR LOOP
      DBMS_OUTPUT.PUT_LINE('Values from v_var that are not null' || V_REC.XT);
    END LOOP;
END;
/

SY.

Solomon Yakobson

Why not simply using cursor?

DECLARE
    CURSOR V_CUR
      IS
        SELECT  XT
          FROM  DATA_SOURCE
          WHERE XT IN (
                       'myData',
                       'myData2',
                       'myData3'
                      );
BEGIN
    FOR V_REC IN V_CUR LOOP
      DBMS_OUTPUT.PUT_LINE('Values from v_var that are not null' || V_REC.XT);
    END LOOP;
END;
/

SY.

Jasper Tanglib

Because I need to do some sort of preprocessing, it should only store values that have passed a specific condition. Something like this.

DECLARE

    v_var varchar2(100);

BEGIN

    IF file_one IS NULL THEN

        NULL;

    ELSIF NOT NULL THEN

        -- add value myData1

        SELECT XT

        INTO v_var

        FROM DATA_SOURCE

        WHERE XT = 'myData1'

    END IF;

    IF file_two IS NULL THEN

        NULL;

    ELSIF NOT NULL THEN

        -- add value myData2

        SELECT XT

        INTO v_var

        FROM DATA_SOURCE

        WHERE XT = 'myData2'

    END IF;

    IF file_three IS NULL THEN

        NULL;

    ELSIF NOT NULL THEN

        -- store value myData3

        SELECT XT

        INTO v_var

        FROM DATA_SOURCE

        WHERE XT = 'myData3'

    END IF;

    

    FOR i IN v_var LOOP

      DBMS_OUTPUT.PUT_LINE('Values from v_var that are not null: ' || v_var(i);

    END LOOP;

END;

Based from the code, assuming file_one and file_three are not null while file_two is null, then output should only be myData1 and myData3.

Not related but, how did you paste your code like that. It looks neat

Solomon Yakobson

And what is file_one, file_two,... where do they come from?

SY.

Solomon Yakobson

Anyway:

DECLARE
    CURSOR V_CUR
      IS
        SELECT  XT
          FROM  DATA_SOURCE
          WHERE XT IN (
                       NVL2(file_one,'myData',NULL),
                       NVL2(file_two,'myData2',NULL),
                       NVL2(file_three,'myData3',NULL)
                      );
BEGIN
    FOR V_REC IN V_CUR LOOP
      DBMS_OUTPUT.PUT_LINE('Values from v_var that are not null' || V_REC.XT);
    END LOOP;
END;
/

SY.