eureka7
19-Sep-2025 22:41:19
Hi all, I am trying to run a procedure, and keep receiving error in the dynamic SQL. I am just showing here the part of code which is having issue:
v_insert_sql := 'INSERT /*+ parallel(4) enable_parallel_dml */
INTO CALL_ARCHIVED_FETCHED
(call_date, connect_time, calling_number, called_number,
entry_tg, exit_tg, duration)
SELECT * FROM (
SELECT call_date,
new_ani AS calling_number,
called_number
FROM ' || v_table_name || '
WHERE call_date BETWEEN :1 AND :2
AND egress_duration > 0
AND (plat_entry_point LIKE ''%'' || :3 || ''%''
OR plat_exit_point LIKE ''%'' || :3 || ''%'')
AND SUBSTR(cost_error_code, 1, 3) LIKE ''%E''
AND (called_number LIKE ''%'' || :4
OR new_ani LIKE ''%'' || :4 || ''%'')';
IF v_limit IS NOT NULL THEN
v_insert_sql := v_insert_sql || ' AND ROWNUM <= ' || v_limit;
END IF;
v_insert_sql := v_insert_sql || ')';
-- Enable parallel DML
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
-- Execute insert
EXECUTE IMMEDIATE v_insert_sql
USING p_start_date, p_end_date, p_tg_filter, p_number;
v_rows := SQL%ROWCOUNT;
I changed to this thinking maybe the issue is the date column into :1 and :2 bind variable but still same issue:
FROM ' || v_table_name || '
WHERE call_date BETWEEN TO_DATE(:1, ''YYYY-MM-DD'')
AND TO_DATE(:2, ''YYYY-MM-DD'')
AND egress_duration > 0
AND (plat_entry_point LIKE ''%'' || :3 || ''%''
OR plat_exit_point LIKE ''%'' || :3 || ''%'')
AND SUBSTR(cost_error_code, 1, 3) LIKE ''%E''
AND (called_number LIKE ''%'' || :4
OR new_ani LIKE ''%'' || :4 || ''%'')';
IF v_limit IS NOT NULL THEN
v_insert_sql := v_insert_sql || ' AND ROWNUM <= :5';
END IF;
v_insert_sql := v_insert_sql || ')';
-- Now pass dates as strings
IF v_limit IS NOT NULL THEN
EXECUTE IMMEDIATE v_insert_sql
USING TO_CHAR(p_start_date,'YYYY-MM-DD'),
TO_CHAR(p_end_date,'YYYY-MM-DD'),
p_tg_filter,
p_number,
v_limit;
ELSE
EXECUTE IMMEDIATE v_insert_sql
USING TO_CHAR(p_start_date,'YYYY-MM-DD'),
TO_CHAR(p_end_date,'YYYY-MM-DD'),
p_tg_filter,
p_number;
END IF;
I can provide full code if needed...
Thanks
Any idea ?