CODEWITHSUNDEEP
oracle-databaseplsql
Migs
Migs

Reputation: 1480

Assign Value to a Nested Table without Looping

say i have the table XX_TEST_DATA and its being used to populate a nested table inside a PL/SQL block below (please note the comments):

DECLARE

    CURSOR XX_DATA_CUR
    IS
    SELECT  *
    FROM    XX_TEST_DATA;

    TYPE TYP_XX_TEST IS TABLE OF XX_TEST_DATA%ROWTYPE INDEX BY PLS_INTEGER;
    XX_REC  TYP_XX_TEST;

BEGIN
    OPEN    XX_DATA_CUR;
    FETCH   XX_DATA_CUR
    BULK COLLECT 
    INTO    XX_REC;
    CLOSE   XX_DATA_CUR;

    for i in 1..XX_REC.count loop

        XX_REC(i).BATCH_NAME := 'Batch 1'; -- This is the Line

    end loop;

END;

I would like to assign the value "Batch 1" to ALL records inside the nested table. Would this be possible without looping through all the records? Something like below:

BEGIN
    OPEN    XX_DATA_CUR;
    FETCH   XX_DATA_CUR
    BULK COLLECT 
    INTO    XX_REC;
    CLOSE   XX_DATA_CUR;

    XX_REC.BATCH_NAME := 'Batch 1'; -- 

END;

I know the above block would not work, but I was hoping to achive something like that.

DDL of the Test Table

Create table XX_TEST_DATA
(
    XX_ID          NUMBER
,   XX_DATA1       VARCHAR2(100)
,   XX_DATA2       VARCHAR2(100)
,   BATCH_NAME  VARCHAR2(100)
);

Upvotes: 1

Views: 319

Answers (1)

Jon Heller
Jon Heller

Reputation: 36912

The value could be set as part of the SELECT and then there would be no need to update the collection.

DECLARE
    TYPE TYP_XX_TEST IS TABLE OF XX_TEST_DATA%ROWTYPE INDEX BY PLS_INTEGER;
    XX_REC  TYP_XX_TEST;
BEGIN
    SELECT XX_ID, XX_DATA1, XX_DATA2, 'Batch 1'
    BULK COLLECT INTO XX_REC
    FROM XX_TEST_DATA;
END;
/

Upvotes: 1

Related Questions