By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE customer_master (customer_id, solution_type) AS
SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 3;
3 rows affected
CREATE TABLE sim_master (customer_id, activation_date) AS
SELECT LEVEL, DATE '2023-02-01' FROM DUAL CONNECT BY LEVEL <= 3;
3 rows affected
CREATE TABLE solution_type (id_key) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 3;
3 rows affected
create or replace PROCEDURE PROC_REPORT (
v_customer_id IN NUMBER,
v_user_id IN VARCHAR2 DEFAULT NULL,
monthYear IN VARCHAR2 DEFAULT NULL,
solutionType IN VARCHAR2 DEFAULT NULL,
customerAccNo IN VARCHAR2 DEFAULT NULL,
productType IN VARCHAR2 DEFAULT NULL,
output OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN output FOR
SELECT *
FROM customer_master cm
inner join sim_master sm on sm.customer_id = cm.customer_id
inner join solution_type st on st.id_key = cm.solution_type
WHERE ( monthYear IS NULL
OR sm.activation_date >= TO_DATE(monthYear,'MON YYYY', 'NLS_DATE_LANGUAGE = English')
);
END PROC_REPORT;
/
DECLARE
v_cur SYS_REFCURSOR;
BEGIN
proc_report(
v_customer_id => 1,
monthYear => 'FEB 2003',
output => v_cur
);
END;
/
1 rows affected