Hi,
DB Version : 12c
We have multiple stage table which extract data from source:
INIT_STG1, INIT_STG2, INIT_STG3, INIT_STG4 and so on, each table will holds a particular year of data.
Now requirement is we need to insert all these stage table data into a final stage table.
What i tried
i create a procedure and get all table name in a For cursor as "SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'INIT_STG%'"
now i tried to insert all these data in FINAL_STG using below Procedure as below:
but getting error Table doesn't exist.
CREATE OR REPLACE PROCEDURE FINAL_STG_LOAD
AS
BEGIN
FOR stg_table IN
(SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'INIT_STG%')
LOOP
INSERT INTO FINAL_STG
(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10)
(SELECT COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10 FROM stg_table.TABLE_NAME);
END LOOP;
END FINAL_STG_LOAD;