The following sample code is designed to display the use of accepting a list of values in a single parameter and process the same in the where clause inside a procedure.
STEP1:-First create an user defined type .
STEP2:-Create a function to split the list of values in a single parameter.
STEP3:-Finally try this sample stored procedure. In the list of parameters user may pass more than one value(coma separated) in a single parameter as a string. (this is defined for a search)
STEP1:-First create an user defined type .
Code:
CREATE OR REPLACE TYPE SPLIT_TBL AS TABLE OF VARCHAR2(32767);
Code:
CREATE OR REPLACE FUNCTION SPLIT
(
P_LIST VARCHAR2,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL PIPELINED
IS
L_IDX PLS_INTEGER;
L_LIST VARCHAR2(32767) := P_LIST;
L_VALUE VARCHAR2(32767);
BEGIN
LOOP
L_IDX := INSTR(L_LIST,P_DEL);
IF L_IDX > 0 THEN
PIPE ROW(SUBSTR(L_LIST,1,L_IDX-1));
L_LIST := SUBSTR(L_LIST,L_IDX+LENGTH(P_DEL));
ELSE
PIPE ROW(L_LIST);
EXIT;
END IF;
END LOOP;
RETURN;
END SPLIT;
Code:
CREATE OR REPLACE PROCEDURE proc_company_advance_search
--PURPOSE----------CREATED TO BE USED FOR COMPANY ADVANCE SEARCH
--DATE-------------05-JUN-2009
--DEVELOPED BY-----DEBASIS DAS
--MODIFICATIONS----
(
p_company_id VARCHAR2,
p_company_name VARCHAR2,
p_firm_type VARCHAR2,
p_instit_type VARCHAR2,
p_country VARCHAR2,
p_state VARCHAR2,
p_city VARCHAR2,
p_status VARCHAR2,
p_oper_asset VARCHAR2,
p_assets_1 NUMBER,
p_assets_2 NUMBER,
p_oper_date VARCHAR2,
p_date1 DATE,
p_date2 DATE,
rec OUT sys_refcursor
)
IS
mainqry VARCHAR2 (500);
qry1 VARCHAR2 (800);
qry2 VARCHAR2 (1400);
qry3 VARCHAR2 (2000);
qry4 VARCHAR2 (3000);
qry5 VARCHAR2 (4000);
qry6 VARCHAR2 (5000);
qry7 VARCHAR2 (1000);
qry8 VARCHAR2 (400);
qry9 VARCHAR2 (50):=' order by csv.company_name ';
dateqry VARCHAR2 (600);
assetqry VARCHAR2 (600);
finalqry VARCHAR2 (32000);
tmpstr1 VARCHAR2 (6000);
tmpstr2 VARCHAR2 (8000);
tmpstr3 VARCHAR2 (10000);
tmpstr4 VARCHAR2 (12000);
tmpstr5 VARCHAR2 (14000);
tmpstr6 VARCHAR2 (16000);
tmpstr7 VARCHAR2 (18000);
tmpstr8 VARCHAR2 (20000);
tmpstr9 VARCHAR2 (22000);
BEGIN
REFRESH_COMPANY;
mainqry := 'SELECT TO_NUMBER(CSV.COMPANY_ID) AS COMPANY_ID,
CSV.COMPANY_STATUS,
CSV.COMPANY_NAME,
CSV.FIRM_TYPE_ID,
CSV.FIRM_TYPE,
CSV.INSTIT_TYPE,
CSV.INSTIT_TYPE_ID,
CSV.ASSETS,
CSV.AREA_CODE,
CSV.COUNTRY_CODE,
CSV.PHONE_NUMBER,
CSV.ADDRESS_LINE1,
CSV.ADDRESS_LINE2,
CSV.CITY,
CSV.STATE,
CSV.STATE_ID,
CSV.COUNTRY_NAME,
CSV.COUNTRY_ID,
CSV.ZIP,
CSV.MOD_DATE,
CSV.MOD_USER
FROM MV_COMPANY_SEARCH CSV ';
IF p_company_id IS NOT NULL
THEN
qry1 :=' WHERE TO_CHAR(LOWER(CSV.COMPANY_ID)) LIKE '''
|| LOWER (TRIM (p_company_ID))
|| '%''';
ELSE
qry1 := '';
END IF;
-- DBMS_OUTPUT.put_line (qry1);
IF p_company_name IS NOT NULL AND qry1 IS NULL
THEN
qry2 :=
' WHERE LOWER(CSV.COMPANY_NAME) LIKE '''
|| LOWER (TRIM (p_company_name))
|| '%''';
ELSIF p_company_name IS NOT NULL
THEN
qry2 :=
' AND LOWER(CSV.COMPANY_NAME) LIKE '''
|| LOWER (TRIM (p_company_name))
|| '%''';
ELSE
qry2 := '';
END IF;
tmpstr1 := qry1 || qry2;
-- DBMS_OUTPUT.put_line (tmpstr1);
IF p_firm_type IS NOT NULL AND tmpstr1 IS NULL
THEN
qry3 :=
' WHERE CSV.FIRM_TYPE_ID IN (SELECT * FROM TABLE(SPLIT('''
|| p_firm_type
|| ''')))';
ELSIF p_firm_type IS NOT NULL
THEN
qry3 :=
' AND CSV.FIRM_TYPE_ID IN (SELECT * FROM TABLE(SPLIT('''
|| p_firm_type
|| ''')))';
ELSE
qry3 := '';
END IF;
tmpstr2 := tmpstr1 || qry3;
-- DBMS_OUTPUT.put_line (tmpstr2);
IF p_instit_type IS NOT NULL AND tmpstr2 IS NULL
THEN
qry4 :=
' WHERE CSV.INSTIT_TYPE_ID IN (SELECT * FROM TABLE(SPLIT('''
|| p_instit_type
|| ''')))';
ELSIF p_instit_type IS NOT NULL
THEN
qry4 :=
' AND CSV.INSTIT_TYPE_ID IN (SELECT * FROM TABLE(SPLIT('''
|| p_instit_type
|| ''')))';
ELSE
qry4 := '';
END IF;
tmpstr3 := tmpstr2 || qry4;
-- DBMS_OUTPUT.put_line (tmpstr3);
IF p_country IS NOT NULL AND tmpstr3 IS NULL
THEN
qry5 :=
' WHERE CSV.COUNTRY_ID IN (SELECT * FROM TABLE(SPLIT('''
|| p_country
|| ''')))';
ELSIF p_country IS NOT NULL
THEN
qry5 :=
' AND CSV.COUNTRY_ID IN (SELECT * FROM TABLE(SPLIT('''
|| p_country
|| ''')))';
ELSE
qry5 := '';
END IF;
tmpstr4 := tmpstr3 || qry5;
-- DBMS_OUTPUT.put_line (tmpstr4);
IF p_state IS NOT NULL AND tmpstr4 IS NULL
THEN
qry6 :=
' WHERE CSV.STATE_ID IN (SELECT * FROM TABLE(SPLIT('''
|| p_state
|| ''')))';
ELSIF p_state IS NOT NULL
THEN
qry6 :=
' AND CSV.STATE_ID IN (SELECT * FROM TABLE(SPLIT('''
|| p_state
|| ''')))';
ELSE
qry6 := '';
END IF;
tmpstr5 := tmpstr4 || qry6;
-- DBMS_OUTPUT.put_line (tmpstr5);
IF p_city IS NOT NULL AND tmpstr5 IS NULL
THEN
qry7 :=
' WHERE CSV.CITY IN (SELECT * FROM TABLE(SPLIT('''
|| p_city
|| ''')))';
ELSIF p_city IS NOT NULL
THEN
qry7 :=
' AND CSV.CITY IN (SELECT * FROM TABLE(SPLIT(''' || p_city
|| ''')))';
ELSE
qry7 := '';
END IF;
tmpstr6 := tmpstr5 || qry7;
-- DBMS_OUTPUT.put_line (tmpstr6);
IF p_status IS NOT NULL AND tmpstr6 IS NULL
THEN
qry8 :=
' WHERE CSV.COMPANY_STATUS IN (SELECT * FROM TABLE(SPLIT('''
|| p_status
|| ''')))';
ELSIF p_status IS NOT NULL
THEN
qry8 :=
' AND CSV.COMPANY_STATUS IN (SELECT * FROM TABLE(SPLIT('''
|| p_status
|| ''')))';
ELSE
qry8 := '';
END IF;
tmpstr7 := tmpstr6 || qry8;
-- DBMS_OUTPUT.put_line (tmpstr7);
IF p_oper_asset IS NOT NULL AND tmpstr7 IS NULL
THEN
IF p_oper_asset = '<'
THEN
assetqry := ' WHERE CSV.ASSETS < ' || p_assets_1;
ELSIF p_oper_asset = '>'
THEN
assetqry := ' WHERE CSV.ASSETS > ' || p_assets_1;
ELSIF p_oper_asset = '='
THEN
assetqry := ' WHERE CSV.ASSETS = ' || p_assets_1;
ELSIF p_oper_asset = 'BETWEEN'
THEN
assetqry :=
' WHERE CSV.ASSETS BETWEEN ' || p_assets_1 || ' AND '
|| p_assets_2;
--ELSIF P_OPER_ASSET ='BLANK' THEN
--ASSETQRY:=' WHERE CSV.ASSETS IS NULL';
ELSIF p_oper_asset = 'NONBLANK'
THEN
assetqry := ' WHERE CSV.ASSETS IS NOT NULL';
END IF;
ELSIF tmpstr7 IS NOT NULL
THEN
IF p_oper_asset = '<'
THEN
assetqry := ' AND CSV.ASSETS < ' || p_assets_1;
ELSIF p_oper_asset = '>'
THEN
assetqry := ' AND CSV.ASSETS > ' || p_assets_1;
ELSIF p_oper_asset = '='
THEN
assetqry := ' AND CSV.ASSETS = ' || p_assets_1;
ELSIF p_oper_asset = 'BETWEEN'
THEN
assetqry :=
' AND CSV.ASSETS BETWEEN ' || p_assets_1 || ' AND ' || p_assets_2;
--ELSIF P_OPER_ASSET ='BLANK' THEN
--ASSETQRY:=' AND CSV._ASSETS IS NULL';
ELSIF p_oper_asset = 'NONBLANK'
THEN
assetqry := ' AND CSV.ASSETS IS NOT NULL';
END IF;
ELSIF p_oper_asset IS NULL
THEN
assetqry := '';
END IF;
tmpstr8 := tmpstr7 || assetqry;
-- DBMS_OUTPUT.put_line (tmpstr8);
IF p_oper_date IS NOT NULL AND tmpstr8 IS NULL
THEN
IF p_oper_date = '<'
THEN
dateqry := ' WHERE TRUNC(CSV.MOD_DATE) < ''' || p_date1 || '''';
ELSIF p_oper_date = '>'
THEN
dateqry := ' WHERE TRUNC(CSV.MOD_DATE) > ''' || p_date1 || '''';
ELSIF p_oper_date = '='
THEN
dateqry := ' WHERE TRUNC(CSV.MOD_DATE) = ''' || p_date1 || '''';
ELSIF p_oper_date = 'BETWEEN'
THEN
dateqry :=
' WHERE TRUNC(CSV.MOD_DATE) BETWEEN '''
|| p_date1
|| ''''
|| ' AND '''
|| p_date2
|| '''';
--ELSIF P_OPER_DATE ='BLANK' THEN
--DATEQRY:=' WHERE TRUNC(CSV.MOD_DATE) IS NULL';
ELSIF p_oper_date = 'NONBLANK'
THEN
dateqry := ' WHERE TRUNC(CSV.MOD_DATE) IS NOT NULL';
END IF;
ELSIF tmpstr8 IS NOT NULL
THEN
IF p_oper_date = '<'
THEN
dateqry := ' AND TRUNC(CSV.MOD_DATE) < ''' || p_date1 || '''';
ELSIF p_oper_date = '>'
THEN
dateqry := ' AND TRUNC(CSV.MOD_DATE) > ''' || p_date1 || '''';
ELSIF p_oper_date = '='
THEN
dateqry := ' AND TRUNC(CSV.MOD_DATE) = ''' || p_date1 || '''';
ELSIF p_oper_date = 'BETWEEN'
THEN
dateqry :=
' AND TRUNC(CSV.MOD_DATE) BETWEEN '''
|| p_date1
|| ''''
|| ' AND '''
|| p_date2
|| '''';
--ELSIF P_OPER_DATE ='BLANK' THEN
--DATEQRY:=' AND TRUNC(CSV.MOD_DATE) IS NULL';
ELSIF p_oper_date = 'NONBLANK'
THEN
dateqry := ' AND TRUNC(CSV.MOD_DATE) IS NOT NULL';
END IF;
ELSIF p_oper_date IS NULL
THEN
dateqry := '';
END IF;
tmpstr9 := tmpstr8 || dateqry;
-- DBMS_OUTPUT.put_line (tmpstr9);
finalqry :=
mainqry
|| qry1
|| qry2
|| qry3
|| qry4
|| qry5
|| qry6
|| qry7
|| qry8
|| assetqry
|| dateqry
|| qry9;
OPEN rec FOR finalqry;
DBMS_OUTPUT.put_line (finalqry);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
raise_application_error (-20001, 'NO DATA FOUND...!');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END proc_company_advance_search;