STEP1:-First create an user defined type .
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE TYPE SPLIT_TBL AS TABLE OF VARCHAR2(32767);
Expand|Select|Wrap|Line Numbers
- 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;
Expand|Select|Wrap|Line Numbers
- 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;