The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE select_clause(
cTransaction_Type IN VARCHAR2,
cCommunication_Method IN VARCHAR2,
cInterface_Table IN VARCHAR2,
cExt_Table OUT VARCHAR2,
cInt_Table OUT CharTable,
cInt_Column OUT CharTable,
nRecord_Num OUT NumTable,
nData_Pos OUT NumTable,
nCol_Width OUT NumTable,
iRow_count OUT NUMBER,
cSelect_string OUT VARCHAR2,
cFrom_string OUT VARCHAR2,
cWhere_string OUT VARCHAR2)
IS
cSelect_stmt VARCHAR2(32000) := 'SELECT ';
Select min(eic.Table_Name) Table_Name,
eic.Column_Name Column_Name,
eic.Record_Number Record_Number,
eic.Position Position,
eic.Width New_width,
atc.Data_Length Width,
atc.Data_Type Col_Type
From all_tab_columns atc,
ece_interface_columns eic,
ece_interface_tables eit
Where eit.Transaction_Type = cTransaction_Type
and eit.Interface_Table_Name = cInterface_Table
and eit.interface_table_id = eic.interface_table_id
and eic.Column_Name = atc.Column_Name
and eic.Table_Name = atc.Table_Name
and eic.Position is not null
and eic.Record_Number is not null
Group by eic.Column_Name, eic.Record_Number, eic.Position, eic.Width, atc.Data_Length, atc.Data_Type
Order By Record_Number, Position;
Select min(EIC.Table_Name) Table_Name, EIC.Column_Name Column_Name,
EIC.Record_Number Record_Number, EIC.Position Position,
EIC.Width New_width,
ATC.Data_Length Width, ATC.Data_Type Col_Type
From ALL_TAB_COLUMNS ATC, ECE_INTERFACE_COLUMNS EIC
Where EIC.Transaction_Type = cTransaction_Type
and EIC.Interface_Table_Name = cInterface_Table
and EIC.Column_Name = ATC.Column_Name
and EIC.Table_Name = ATC.Table_Name
Group by EIC.Column_Name, EIC.Record_Number, EIC.Position, ATC.Data_Length, ATC.Data_Type
Order By Record_Number, Position;
cSelect_stmt := cSelect_stmt || ' ' || cWord1 || c1_rec.Table_Name || '.' ||
c1_rec.Column_Name || cWord2 || ',';
Select EIT.Extension_Table_Name -- select extension table name
Into cExtension_Table
From ECE_INTERFACE_TABLES EIT
Where EIT.Transaction_Type = cTransaction_Type
And EIT.Interface_Table_Name = cInterface_Table;
cSelect_string := RTRIM (cSelect_stmt, ',');
END select_clause;
cInsert_stmt VARCHAR2(32000);
cInsert_stmt := ' ' || TO_CHAR(nRecord_Num(1));
iLine_pos := LENGTH(cInsert_stmt);
cInsert_stmt := cInsert_stmt || substrb(rpad(nvl(cReport_data(i),' '),nData_width(i),' '),1,nData_width(i));
insert into ece_output( RUN_ID, LINE_ID, TEXT) values
(iRun_id, ece_output_lines_s.nextval, substrb(cInsert_stmt,1,iOutput_width));
cInsert_stmt := '*' || TO_CHAR(nRecord_Num(i+1));
insert into ece_output( RUN_ID, LINE_ID, TEXT) values
(iRun_id, ece_output_lines_s.nextval, substrb(cInsert_stmt,1,iOutput_width));