Include RowId value in Nested Table

Question

I have the below sample table:

create table data_test
(
    data_id     number,
    data_value  varchar2(100)
);

I want to use this as a nested table parameter in the below sample Stored Procedure by doing the below declaration:

create or replace package dat_pkg is

    type typ_dat_tst is table of data_test%rowtype index by pls_integer;

    procedure proc_test (p_dat  typ_dat_tst);

end dat_pkg;
/

I want proc_test to update the rows of data_test based on the rowid of the nested table:

create or replace package body dat_pkg is

    procedure proc_test (p_dat  typ_dat_tst)
    is
    begin

        for i in 1..p_dat.count loop

            update  data_test        
            set     data_value  = p_dat(i).data_value  
            where   data_id     = p_dat(i).data_id
            and     rowid       = p_dat(i).rowid;

        end loop;

    end proc_test;

end dat_pkg;
/    

however i am getting the error PLS-00302: component 'ROWID' must be declared because its looking for the physical column rowid in the Nested Table. The same error is being raised when i use the function rowidtochar().

How can i include rowid as a physicial column in the Type declaration?

Answer 1

ROWID is a pseudocolumn, it isn't part of the data dictionary view of the table (e.g. it doesn't appear in dba_tab_columns), so it isn't included in the %rowtype. A PL/SQL record - which is what you are constructing a PL/SQL table of - has no physical storage, so no real or pseudo rowid.

If you really want to store the row ID in a record/table you would have to declare the type explicitly:

create or replace package dat_pkg is

    type typ_dat_rec is record (
        data_id     data_test.data_id%type,
        data_value  data_test.data_value%type,
        data_rowid  rowid);

    type typ_dat_tst is table of data_test%rowtype index by pls_integer;

    procedure proc_test (p_dat  typ_dat_tst);

end dat_pkg;
/

You can't call the record field just rowid as that is a data type, so I've prefixed it with data_ but you might prefer something else. And then you need to use that field name in your package body, obviously:

create or replace package body dat_pkg is

    procedure proc_test (p_dat  typ_dat_tst)
    is
    begin

        for i in 1..p_dat.count loop

            update  data_test        
            set     data_value  = p_dat(i).data_value  
            where   data_id     = p_dat(i).data_id
            and     rowid       = p_dat(i).data_rowid;

        end loop;

    end proc_test;

end dat_pkg;
/

You could, as you suggested, store the entire row type and the row ID as two fields in the record type:

create or replace package dat_pkg is

    type typ_dat_rec is record (
        data_rec    data_test%rowtype,
        data_rowid  rowid);

    type typ_dat_tst is table of typ_dat_rec index by pls_integer;

    procedure proc_test (p_dat  typ_dat_tst);

end dat_pkg;
/

but that makes referring to the fields a bit more awkward:

...
        for i in 1..p_dat.count loop

            update  data_test        
            set     data_value  = p_dat(i).data_rec.data_value  
            where   data_id     = p_dat(i).data_rec.data_id
            and     rowid       = p_dat(i).data_rowid;

        end loop;
...

and it will probably make populating the collection more awkward too. As you have to know all the column/field names anyway to be able to refer to them in the loop, I'm not sure there's much advantage, but you may find it neater.

Of course, doing this at all assumes your collection is being populated from a subset of data from the table in the same DB and even session, since a row's rowid can change over time. You might also want to look into the forall syntax to replace your for loop, depending on what you are really doing. (But you should also consider whether you need the collection at all - if you are just populating the collection and then using that for the update then a single SQL update would be faster still...)