[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select eel.start_element,
eit.interface_table_id,
eit.key_column_name,
eit.primary_address_type
from ece_interface_tables eit,
ece_level_matrices elm,
ece_external_levels eel
where eit.transaction_type = p_transaction_type
and eit.interface_table_id = elm.interface_table_id
and elm.external_level_id = eel.external_level_id
and eel.map_id = p_map_id
order by to_number(external_level);
select DISTINCT(eic.record_number) record_number
, eel.external_level external_level
, eel.start_element start_element
, COUNT(*) counter
from ece_interface_tables eit,
ece_interface_columns eic,
ece_level_matrices elm,
ece_external_levels eel
where eit.transaction_type = p_transaction_type
and eic.interface_table_id = eit.interface_table_id
and eit.interface_table_id = elm.interface_table_id
and elm.external_level_id = eel.external_level_id
and eic.record_number IS NOT NULL
and eic.position IS NOT NULL
and eel.map_id = p_map_id
group by eel.external_level, eic.record_number, eel.start_element
order by eel.external_level, eic.record_number;
i_level_info.DELETE;
i_stage_record_type.DELETE;
i_level_info(i_level).Select_Cursor := 0;
Make a copy of the PL/SQL table and save it. After Inserting the Data into the
staging table , initialize the PL/SQL table with values from saved PL/SQL table.
**/
i_empty_tbl := i_stage_record_type;
Build each record's SELECT statement
**/
l_next_file_pos :=1;
get_select_stmt(record_level.external_level,
i_record_info(i_record).record_number,
i_record,
l_next_file_pos,
i_record_info(i_record).counter);
The Cursors for the Select From Stage table are not closed in the Select_From_Stage_table
procedure call. Since the Cursor handles are maintained in the I_LEVEL_INFO PL/SQL table,
Cursors for the all Levels are closed using these Cursor handles.
**/
FOR i in 1..i_level_info.COUNT
LOOP
IF dbms_sql.IS_OPEN(i_level_info(i).Select_Cursor)
THEN
dbms_sql.Close_cursor(i_level_info(i).Select_Cursor);
Delete all records from the staging table
**/
ec_outbound.delete_stage_data
(
i_run_id,
NULL
);
SELECT STAGE_ID,TRANSACTION_LEVEL
FROM ECE_STAGE
WHERE TRANSACTION_TYPE = p_transaction_type
AND RUN_ID = p_run_id
ORDER BY STAGE_ID; -- bug 3133379
i_select_cursor INTEGER := 0;
i_select_cursor := NVL(i_level_info(b_transaction_level(i)).select_cursor,0);
Select_From_Stage_Table(
B_TRANSACTION_LEVEL(i),
B_STAGE_ID(i),
i_select_cursor,
i_common_key
);
b_stage_id.delete;
b_transaction_level.delete;
--cSelect_stmt := cSelect_stmt||cFrom_stmt||cWhere_stmt;
-- dbms_sql.parse(i_stage_cursor,cSelect_stmt,dbms_sql.native);
-- ece_error_handling_pvt.print_parse_error (error_position,cSelect_stmt);
ec_debug.pl(3,'EC','ECE_STAGE_SELECTED',NULL);
ec_debug.pl(3,cSelect_stmt);
ec_debug.pl(0,cSelect_stmt);
ec_debug.pl(3,'i_level_info(v_transaction_level).select_cursor',i_level_info(v_transaction_level).select_cursor);
Select_From_Stage_Table(
v_transaction_level,
v_stage_id,
i_select_cursor,
i_common_key
);
SELECT eic.interface_table_id,
eic.interface_column_name,
eic.staging_column,
eic.record_number,
eic.record_layout_code,
eic.record_layout_qualifier,
eic.data_type,
eic.position,
eic.width
FROM ece_interface_tables eit,
ece_level_matrices elm,
ece_external_levels eel,
ece_interface_columns eic
WHERE eit.interface_table_id = eic.interface_table_id
AND eit.transaction_type = p_transaction_type
AND eic.external_level = p_level
AND eit.interface_table_id = elm.interface_table_id
AND elm.external_level_id = eel.external_level_id
AND eel.map_id = p_map_id
and eic.record_number IS NOT NULL
and eic.position IS NOT NULL
ORDER BY eic.record_number, eic.position;
This procedure formats the main body of a SELECT statement for each record number of
a given transaction and saves the result in a local PL/SQL table for later parsing.
This procedure is called once for each record number regardless of the number of
columns in the staging table in order to save on the number of PL/SQL string operations
required
**/
PROCEDURE get_select_stmt
(
i_current_level IN NUMBER,
i_record_num IN number,
i_file_pos IN number,
i_next_file_pos IN OUT NOCOPY number,
i_total_rec_unit IN number
)
IS
i_rec_cd ece_interface_columns.record_layout_code%TYPE;
ec_debug.push('EC_OUTBOUND_STAGE.GET_SELECT_STMT');
Build Application Data SELECT statement
**/
FOR k IN i_next_file_pos..i_next_file_pos+i_total_rec_unit
LOOP
IF i_next_file_pos <= i_stage_record_type.count
THEN
-- ec_debug.pl(3,'k:interface_level',k||'|'||i_stage_record_type(k).interface_level);
i_record_info(i_file_pos).select_stmt := i_record_info(i_file_pos).select_stmt||
'||RPAD(NVL('||
NVL(i_stage_record_type(k).staging_column,'NULL')||
','||
c_local_chr_39 ||g_rec_appd_fl||c_local_chr_39||
'),'||
i_stage_record_type(k).width||
','||
c_local_chr_39||g_rec_appd_fl||c_local_chr_39||
')';
i_record_info(i_file_pos).select_stmt :=
c_local_chr_39||
LPAD(NVL(TO_CHAR(i_record_info(i_file_pos).record_number),g_rec_num_fl),
g_rec_num_ln, g_rec_num_fl)||c_local_chr_39||'||'||
c_local_chr_39||
RPAD(NVL(i_rec_cd, g_rec_lcd_fl),g_rec_lcd_ln, g_rec_lcd_fl)||
c_local_chr_39||'||'||
c_local_chr_39||RPAD(NVL(i_rec_ql, g_rec_lql_fl),
g_rec_lql_ln, g_rec_lql_fl)||c_local_chr_39||
i_record_info(i_file_pos).select_stmt;
i_record_info(i_file_pos).select_stmt);
ec_debug.pop('EC_OUTBOUND_STAGE.GET_SELECT_STMT');
ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.GET_SELECT_STMT');
END get_select_stmt;
procedure Select_From_Stage_Table
(
i_level IN NUMBER,
i_stage_id IN NUMBER,
i_select_cursor IN OUT NOCOPY NUMBER,
i_common_key IN OUT NOCOPY VARCHAR2
)
is
cSelect_stmt varchar2(32000) := 'SELECT ';
i_select_count INTEGER := 0;
ec_debug.push('EC_OUTBOUND_STAGE.SELECT_FROM_STAGE_TABLE');
ec_debug.pl(3,'i_select_cursor',i_select_cursor);
if i_select_cursor = 0
then
i_select_cursor := -911;
if i_select_cursor < 0
then
cSelect_stmt := cSelect_stmt||
NVL(i_level_info(i_level).tp_code_staging,'NULL')||
','||
NVL(i_level_info(i_level).Key_Column_Staging,'NULL')||
',';
Get Select Statement from PLSQL table
**/
i_select_count := i_select_count + 1;
cSelect_stmt := cSelect_stmt||
i_record_info(k).select_stmt||
',';
cSelect_stmt := RTRIM(cSelect_stmt,',');
cSelect_stmt := cSelect_stmt||cFrom_stmt||cWhere_stmt;
ec_debug.pl(3,'cSelect_stmt',cSelect_stmt);
i_select_cursor := dbms_sql.Open_Cursor;
dbms_sql.parse(i_select_cursor,cSelect_stmt,dbms_sql.native);
i_level_info(i_level).select_cursor := i_select_cursor;
i_level_info(i_level).total_records := i_select_count;
ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.SELECT_FROM_STAGE_TABLE');
ece_error_handling_pvt.print_parse_error (error_position,cSelect_stmt);
ec_debug.pl(3,'EC','ECE_STAGE_SELECT_LEVEL','LEVEL',i_level,null);
if i_select_cursor > 0
then
/**
Bind values for Primary Key
**/
dbms_sql.bind_variable(i_select_cursor,'a1',i_stage_id);
dbms_sql.define_column(i_select_cursor,1,v_dummy_tp_code,2000);
dbms_sql.define_column(i_select_cursor,2,v_dummy_key_staging,2000);
dbms_sql.define_column(i_select_cursor,n,v_dummy(n),2000);
dummy := dbms_sql.execute(i_select_cursor);
ec_debug.pl(3,'EC','ECE_STAGE_SELECTED',NULL);
ec_debug.pl(3,'i_select_cursor', i_select_cursor);
ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL', 'EC_OUTBOUND_STAGE.SELECT_FROM_STAGE_TABLE');
ec_debug.pl(0,cSelect_stmt);
WHILE dbms_sql.fetch_rows(i_select_cursor) > 0
LOOP
/** You can comment out this call if you don't want the Common Key to be formatted on the flat file.
This is provides a performance boost due to the slow PL/SQL string operations required for the common key
**/
dbms_sql.column_value(i_select_cursor,1,v_dummy_tp_code);
dbms_sql.column_value(i_select_cursor,2,v_dummy_key_staging);
Select_Common_key(
i_level,
v_dummy_tp_code,
v_dummy_key_staging,
i_common_key
);
dbms_sql.column_value(i_select_cursor,m,v_dummy(m));
ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL', 'EC_OUTBOUND_STAGE.SELECT_FROM_STAGE_TABLE');
ec_debug.pl(3,'i_select_cursor',i_select_cursor);
ec_debug.pop('EC_OUTBOUND_STAGE.SELECT_FROM_STAGE_TABLE');
IF dbms_sql.IS_OPEN(i_select_cursor)
then
dbms_sql.close_cursor(i_select_cursor);
ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.SELECT_FROM_STAGE_TABLE');
END Select_From_Stage_Table;
procedure Select_Common_Key
(
i_level IN NUMBER,
i_tp_code IN VARCHAR2,
i_key_column IN VARCHAR2,
i_common_key IN OUT NOCOPY VARCHAR2
)
is
i_common_key_ln INTEGER := 0;
ec_debug.push('EC_OUTBOUND_STAGE.SELECT_COMMON_KEY');
Build Common Key TP CODE SELECT
**/
i_common_key := RPAD(SUBSTRB(NVL(i_tp_code,g_tp_ckey_fl),1,g_tp_ckey_ln),g_tp_ckey_ln,g_tp_ckey_fl);
ec_debug.pop('EC_OUTBOUND_STAGE.SELECT_COMMON_KEY');
ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.SELECT_COMMON_KEY');
END Select_Common_Key;