can any one please help me on this.
I have to do the merge on the dyanamic query.
departure date,period column have the 1970 year data also.
I have to use the FORALL only. we implemented switch method and other code is with
sql code(it gives the performance more).
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE test_alff_R (
p_season_year VARCHAR2,
p_season_type VARCHAR2,
p_arr_gateway_list VARCHAR2,
p_ros_calc_mode CHAR
)
IS
V_PERIOD VARCHAR2(34);
p_accom_code VARCHAR2(34);
l_sql1 VARCHAR2(3500);
TYPE tab_accom_code IS TABLE OF alff_cache.accom_code%TYPE INDEX BY PLS_INTEGER;
TYPE tab_room_type IS TABLE OF alff_cache.room_type_code%TYPE INDEX BY PLS_INTEGER;
TYPE tag_gateway IS TABLE OF yield_region_gateway.gateway_code_arrival%TYPE INDEX BY PLS_INTEGER;
TYPE tab_duration IS TABLE OF alff_cache.duration%TYPE INDEX BY PLS_INTEGER;
TYPE tab_period_type IS TABLE OF alff_cache.period_type%TYPE INDEX BY PLS_INTEGER;
TYPE tab_source_mar_code IS TABLE OF alff_cache.source_market_code%TYPE INDEX BY PLS_INTEGER;
TYPE tab_number IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE tab_date IS TABLE OF DATE INDEX BY PLS_INTEGER;
t_accom_code tab_accom_code;
t_gateway tag_gateway;
t_dept_date tab_date;
t_duration tab_duration;
t_room_type tab_room_type;
t_period tab_date;
t_period_type tab_period_type;
t_rb_idp_ros tab_number;
t_free_kids_bkd tab_number;
t_unpaid_rooms_cnx_7days_ago tab_number;
t_unpaid_rooms_cnx_total tab_number;
t_source_mar_code tab_source_mar_code;
L_CNT NUMBER := 0;
L_CNT2 NUMBER := 0;
BEGIN
v_period := p_season_type || p_season_year;
l_sql1 :=
'SELECT /*+ parallel(ac 8) */
DISTINCT ac.accom_code,
ab.gateway_code_arrival,
to_date(to_char(ac.departure_date, ''DD/MM/RRRR''),''DD/MM/RRRR'') departure_date,
ac.duration,
ac.room_type_code,
to_date(to_char(ab.period, ''DD/MM/RRRR''),''DD/MM/RRRR'') period,
ab.period_type,
ab.rooms_used_packaged_ros rb_idp_ros,
ab.free_kids_bkd,
ab.unpaid_rooms_cnx_7days_ago,
ab.unpaid_rooms_cnx_total,
ab.source_market_code
FROM temp_alff_bk2' || v_period || ' ab, alff_availability_cache ac
WHERE ab.season_year = ''' || p_season_year || '''
AND ab.season_type = ''' || p_season_type || '''
AND ab.season_year = ac.season_year
AND ab.season_type = ac.season_type
AND ab.accom_code = ac.accom_code
AND ab.departure_date = ac.min_avail_date_packaged
AND ab.source_market_code = ac.source_market_code
AND ab.duration = ac.duration
AND ac.duration > 1
AND ab.room_type_code = ac.room_type_code
AND ab.gateway_code_arrival IN (''AYT'', ''RHO'')';
DBMS_OUTPUT.PUT_LINE('Query: ' || l_sql1);
EXECUTE IMMEDIATE l_sql1
BULK COLLECT INTO t_accom_code,
t_gateway,
t_dept_date,
t_duration,
t_room_type,
t_period,
t_period_type,
t_rb_idp_ros,
t_free_kids_bkd,
t_unpaid_rooms_cnx_7days_ago,
t_unpaid_rooms_cnx_total,
t_source_mar_code;
L_CNT := t_accom_code.COUNT;
DBMS_OUTPUT.PUT_LINE('START loop count: ' || L_CNT);
IF t_accom_code.COUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE('In loop');
FORALL k IN t_dept_date.FIRST .. t_dept_date.LAST SAVE EXCEPTIONS
EXECUTE IMMEDIATE 'MERGE INTO temp_alff_bk2' || v_period || ' ab
USING (SELECT ''' || p_season_year || ''' AS season_year,
''' || p_season_type || ''' AS season_type,
:t_gateway AS gateway_code_arr,
:t_accom_code AS accom_code,
:t_room_type AS room_type,
:t_dept_date AS dept_date,
:t_duration AS duration,
:t_period AS period,
:t_period_type AS period_type,
:t_rb_idp_ros AS rb_idp_ros,
:t_free_kids_bkd AS free_kids_bkd,
:t_unpaid_rooms_cnx_7days_ago AS unpaid_rooms_cnx_7days_ago,
:t_unpaid_rooms_cnx_total AS unpaid_rooms_cnx_total,
:t_source_mar_code AS source_market_code
FROM dual) t
ON (ab.season_year = t.season_year AND
ab.season_type = t.season_type AND
ab.gateway_code_arrival = t.gateway_code_arr AND
ab.accom_code = t.accom_code AND
ab.room_type_code = t.room_type AND
TO_DATE(TO_CHAR(ab.departure_date, ''DD/MM/RRRR''), ''DD/MM/RRRR'') = TO_DATE(TO_CHAR(t.dept_date, ''DD/MM/RRRR''), ''DD/MM/RRRR'') AND
ab.duration = t.duration AND
TO_DATE(TO_CHAR(ab.period, ''DD/MM/RRRR''), ''DD/MM/RRRR'') = TO_DATE(TO_CHAR(t.period, ''DD/MM/RRRR''), ''DD/MM/RRRR'') AND
ab.period_type = t.period_type AND
ab.source_market_code = t.source_market_code)
WHEN MATCHED THEN
UPDATE SET ab.rb_idp_ros = t.rb_idp_ros,
ab.free_kids_bkd = t.free_kids_bkd,
ab.unpaid_rooms_cnx_7days_ago = t.unpaid_rooms_cnx_7days_ago,
ab.unpaid_rooms_cnx_total = t.unpaid_rooms_cnx_total
WHEN NOT MATCHED THEN
INSERT(season_year,
season_type,
accom_code,
room_type_code,
departure_date,
duration,
gateway_code_arrival,
period,
period_type,
rb_idp_ros,
free_kids_bkd,
unpaid_rooms_cnx_7days_ago,
unpaid_rooms_cnx_total,
source_market_code)
VALUES(
t.season_year,
t.season_type,
t.accom_code,
t.room_type,
TO_DATE(TO_CHAR(t.dept_date, ''DD/MM/RRRR''), ''DD/MM/RRRR'') ,
t.duration,
t.gateway_code_arr,
TO_DATE(TO_CHAR(t.period, ''DD/MM/RRRR''), ''DD/MM/RRRR'') ,
t.period_type,
t.rb_idp_ros,
t.free_kids_bkd,
t.unpaid_rooms_cnx_7days_ago,
t.unpaid_rooms_cnx_total,
t.source_market_code) '
USING t_accom_code(k),
t_gateway(k),
t_dept_date(k),
t_duration(k),
t_room_type(k),
t_period(k),
t_period_type(k),
t_rb_idp_ros(k),
t_free_kids_bkd(k),
t_unpaid_rooms_cnx_7days_ago(k),
t_unpaid_rooms_cnx_total(k),
t_source_mar_code(k);
L_CNT2 := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('L_CNT2: ' || L_CNT2);
COMMIT;
END IF;
END;
/
CL SCR;
SET SERVEROUTPUT ON;
BEGIN
test_err_alff('2024','S','''ALL''','F');
END;
/
Error report -
ORA-01008: not all variables bound
ORA-06512: at "YIELD.TEST_ERR_ALFF", line 87
ORA-06512: at line 2
01008. 00000 - "not all variables bound"
Below are the table structure.
