When I execute the below insert script in the YYY schema. The data from select script is getting inserted in the Um_Schema_Table_Compare table.
**Insert Script:**
iNSERT INTO Um_Schema_Table_Compare
Select 'PREPRODHCM' AS OWNER,COLUMN_NAME,DATA_TYPE,DATA_LENGTh
From
(
Select column_name , data_type, data_length
From sys.All_Tab_Columns
Where Table_Name = 'C_INVOICE' And Owner = 'PREPRODHCM'
);
COMMIT;
**Output:**
103 rows inserted.
committed.
But when i execute the same insert script via stored procedure. The data from select script is not getting inserted in the Um_Schema_Table_Compare table. For the reference i have specified the sql scripts below. Any one help me to resolve this issue.
**SQL Script:**
create or replace PROCEDURE SCHEMA_TABLE_COMPARE Is
Begin
exeCUTE IMMEDIATE ('DELETE FROM Um_Schema_Table_Compare');
COMMIT;
INSERT INTO Um_Schema_Table_Compare
Select 'PREPRODHCM' AS OWNER,COLUMN_NAME,DATA_TYPE,DATA_LENGTh
From
(
Select column_name , data_type, data_length
From sys.All_Tab_Columns
Where Table_Name = 'C_INVOICE' And Owner = 'PREPRODHCM'
);
COMMIT;
End;
EXECUTE SCHEMA_TABLE_COMPARE;
**Output:**
anonymous block completed