Procedure to accept a list of values in a single parameter

debasisdas

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 .

Expand|Select|Wrap|Line Numbers

CREATE OR REPLACE TYPE SPLIT_TBL AS TABLE OF VARCHAR2(32767);

STEP2:-Create a function to split the list of values in a single parameter.

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;

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)

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;

Procedure to accept a list of values in a single parameter

Similar Oracle Database bytes

/bytes/development

/bytes/it

/bytes/about