Hi,
I am translating a MS-SQL SERVER stored procedure into a PL/SQL stored procedure.
I have this snippet:
select COLUMN1 as VAL1, COLUMN2 as VAL2
into MY_GTT_TABLE_1
from MY_TABLE
where 1=2
alter table MY_GTT_TABLE_1 add VAL_ID INTEGER IDENTITY
insert into MY_GTT_TABLE_1 (VAL1,VAL2)
select isnull(COLUMN1,0),COLUMN2
from MY_GTT_TABLE_2
where foo
group by foo
I have created GTT with a script outside my PL/SQL stored procedure.
My translation sound like this:
| EXECUTE IMMEDIATE 'TRUNCATE TABLE MY_GTT_TABLE_1 '; |
| INSERT INTO MY_GTT_TABLE_1 (SELECT COLUMN1 AS VAL1, COLUMN2 as VAL2 FROM MY_TABLE WHERE 1 = 2 ); |
| |
| EXECUTE IMMEDIATE 'ALTER TABLE ttg_VALORE_condizioni ADD VAL_ID NUMBER GENERATED BY DEFAULT AS IDENTITY'; |
| | |
| |
| INSERT INTO MY_GTT_TABLE_1 (VAL1,VAL2) |
| ( SELECT NVL(COLUMN1,0) , COLUMN2 FROM MY_GTT_TABLE_2 WHERE foo GROUP BY foo; |
I'm using Oracle 12c and then I may add a column with "IDENTITY" property but
this instruction fails and column "VAL_ID" isn't added!!!
Thanks in advance!
G.M