Can someone tell me or point me in the direction of some documentation that shows how to account for an IDENTITY column in a Bulk Collect select statement? I haven't had much luck so far in my searches...
Here is an example of what I'm currently trying to do -
I create my table, with id being my 'autoincrement' column
create table xxtm.tm_test_table
(
id number GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY
, created_by number NOT NULL
, creation_date date default sysdate NOT NULL
, last_update_by number NOT NULL
, last_updated_date date default sysdate NOT NULL
)
I create my type in my package spec:
type testRecs is table of xxtm.tm_test_table%ROWTYPE index by BINARY_INTEGER;
I write my procedure to populate my type:
procedure tm_test
is
v_user_id number := 1111;
testRec testRecs;
begin
select v_user_id created_by
, sysdate creation_date
, v_user_id last_update_by
, sysdate last_update_date
BULK COLLECT into testRec
from dual;
end tm_test;
When I try to compile, I get the error 'ORA-00913: too many values'.
If I place something in the select to account for the id column, like below, my code then compiles...so I'm guessing I need to account for the ID somehow, but I thought declaring it like I did means you don't have to specifically put it in your statements? (it will default to the next sequence value?)
This compiles ok -
procedure tm_test
is
v_user_id number := 1111;
testRec testRecs;
begin
select 1 id, v_user_id created_by, sysdate creation_date, v_user_id last_update_by, sysdate last_update_date
BULK COLLECT into testRec
from dual;
end tm_test;
When I look in Toad at the Table definition, it has under the default column "XXTM"."ISEQ$$_4697897".nextval. Is that what I need to reference in the Bulk Collect statement? But won't that value change when I move environments and rebuild my table?
Thanks!
Janel