By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,307 Members | 1,950 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Procedure to accept a list of values in a single parameter

debasisdas
Expert 5K+
P: 8,127
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
  1. 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
  1. CREATE OR REPLACE FUNCTION SPLIT
  2. (
  3.     P_LIST VARCHAR2,
  4.     P_DEL VARCHAR2 := ','
  5. ) RETURN SPLIT_TBL PIPELINED
  6. IS
  7.     L_IDX    PLS_INTEGER;
  8.     L_LIST    VARCHAR2(32767) := P_LIST;
  9.     L_VALUE    VARCHAR2(32767);
  10. BEGIN
  11.     LOOP
  12.         L_IDX := INSTR(L_LIST,P_DEL);
  13.         IF L_IDX > 0 THEN
  14.             PIPE ROW(SUBSTR(L_LIST,1,L_IDX-1));
  15.             L_LIST := SUBSTR(L_LIST,L_IDX+LENGTH(P_DEL));
  16.  
  17.         ELSE
  18.             PIPE ROW(L_LIST);
  19.             EXIT;
  20.         END IF;
  21.     END LOOP;
  22.     RETURN;
  23. END SPLIT;
  24.  
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
  1. CREATE OR REPLACE PROCEDURE proc_company_advance_search
  2. --PURPOSE----------CREATED TO BE USED FOR COMPANY ADVANCE SEARCH
  3. --DATE-------------05-JUN-2009
  4. --DEVELOPED BY-----DEBASIS DAS
  5. --MODIFICATIONS----
  6. (
  7.    p_company_id           VARCHAR2,
  8.    p_company_name         VARCHAR2,
  9.    p_firm_type            VARCHAR2,
  10.    p_instit_type          VARCHAR2,
  11.    p_country              VARCHAR2,
  12.    p_state                VARCHAR2,
  13.    p_city                 VARCHAR2,
  14.    p_status               VARCHAR2,
  15.    p_oper_asset           VARCHAR2,
  16.    p_assets_1             NUMBER,
  17.    p_assets_2             NUMBER,
  18.    p_oper_date            VARCHAR2,
  19.    p_date1                DATE,
  20.    p_date2                DATE,
  21.    rec              OUT   sys_refcursor
  22. )
  23. IS
  24.    mainqry    VARCHAR2 (500);
  25.    qry1       VARCHAR2 (800);
  26.    qry2       VARCHAR2 (1400);
  27.    qry3       VARCHAR2 (2000);
  28.    qry4       VARCHAR2 (3000);
  29.    qry5       VARCHAR2 (4000);
  30.    qry6       VARCHAR2 (5000);
  31.    qry7       VARCHAR2 (1000);
  32.    qry8       VARCHAR2 (400);
  33.    qry9       VARCHAR2 (50):=' order by csv.company_name ';
  34.  
  35.    dateqry    VARCHAR2 (600);
  36.    assetqry   VARCHAR2 (600);
  37.    finalqry   VARCHAR2 (32000);
  38.    tmpstr1    VARCHAR2 (6000);
  39.    tmpstr2    VARCHAR2 (8000);
  40.    tmpstr3    VARCHAR2 (10000);
  41.    tmpstr4    VARCHAR2 (12000);
  42.    tmpstr5    VARCHAR2 (14000);
  43.    tmpstr6    VARCHAR2 (16000);
  44.    tmpstr7    VARCHAR2 (18000);
  45.    tmpstr8    VARCHAR2 (20000);
  46.    tmpstr9    VARCHAR2 (22000);
  47. BEGIN
  48. REFRESH_COMPANY;
  49. mainqry := 'SELECT TO_NUMBER(CSV.COMPANY_ID) AS COMPANY_ID,
  50. CSV.COMPANY_STATUS,
  51. CSV.COMPANY_NAME,
  52. CSV.FIRM_TYPE_ID,
  53. CSV.FIRM_TYPE,
  54. CSV.INSTIT_TYPE,
  55. CSV.INSTIT_TYPE_ID,
  56. CSV.ASSETS,
  57. CSV.AREA_CODE,
  58. CSV.COUNTRY_CODE,
  59. CSV.PHONE_NUMBER,
  60. CSV.ADDRESS_LINE1,
  61. CSV.ADDRESS_LINE2,
  62. CSV.CITY,
  63. CSV.STATE,
  64. CSV.STATE_ID,
  65. CSV.COUNTRY_NAME,
  66. CSV.COUNTRY_ID,
  67. CSV.ZIP,
  68. CSV.MOD_DATE,
  69. CSV.MOD_USER
  70. FROM MV_COMPANY_SEARCH CSV ';
  71.  
  72.    IF p_company_id IS NOT NULL
  73.    THEN
  74.       qry1 :=' WHERE TO_CHAR(LOWER(CSV.COMPANY_ID)) LIKE '''
  75.          || LOWER (TRIM (p_company_ID))
  76.          || '%''';
  77.    ELSE
  78.       qry1 := '';
  79.    END IF;
  80.  
  81. --   DBMS_OUTPUT.put_line (qry1);
  82.  
  83.    IF p_company_name IS NOT NULL AND qry1 IS NULL
  84.    THEN
  85.       qry2 :=
  86.             ' WHERE LOWER(CSV.COMPANY_NAME) LIKE '''
  87.          || LOWER (TRIM (p_company_name))
  88.          || '%''';
  89.    ELSIF p_company_name IS NOT NULL
  90.    THEN
  91.       qry2 :=
  92.             ' AND LOWER(CSV.COMPANY_NAME) LIKE '''
  93.          || LOWER (TRIM (p_company_name))
  94.          || '%''';
  95.    ELSE
  96.       qry2 := '';
  97.    END IF;
  98.  
  99.    tmpstr1 := qry1 || qry2;
  100. --   DBMS_OUTPUT.put_line (tmpstr1);
  101.  
  102.    IF p_firm_type IS NOT NULL AND tmpstr1 IS NULL
  103.    THEN
  104.       qry3 :=
  105.             ' WHERE CSV.FIRM_TYPE_ID IN (SELECT * FROM TABLE(SPLIT('''
  106.          || p_firm_type
  107.          || ''')))';
  108.    ELSIF p_firm_type IS NOT NULL
  109.    THEN
  110.       qry3 :=
  111.             ' AND CSV.FIRM_TYPE_ID IN (SELECT * FROM TABLE(SPLIT('''
  112.          || p_firm_type
  113.          || ''')))';
  114.    ELSE
  115.       qry3 := '';
  116.    END IF;
  117.  
  118.    tmpstr2 := tmpstr1 || qry3;
  119. --   DBMS_OUTPUT.put_line (tmpstr2);
  120.  
  121.    IF p_instit_type IS NOT NULL AND tmpstr2 IS NULL
  122.    THEN
  123.       qry4 :=
  124.             ' WHERE CSV.INSTIT_TYPE_ID IN (SELECT * FROM TABLE(SPLIT('''
  125.          || p_instit_type
  126.          || ''')))';
  127.    ELSIF p_instit_type IS NOT NULL
  128.    THEN
  129.       qry4 :=
  130.             ' AND CSV.INSTIT_TYPE_ID IN (SELECT * FROM TABLE(SPLIT('''
  131.          || p_instit_type
  132.          || ''')))';
  133.    ELSE
  134.       qry4 := '';
  135.    END IF;
  136.  
  137.    tmpstr3 := tmpstr2 || qry4;
  138. --   DBMS_OUTPUT.put_line (tmpstr3);
  139.  
  140.    IF p_country IS NOT NULL AND tmpstr3 IS NULL
  141.    THEN
  142.       qry5 :=
  143.             ' WHERE CSV.COUNTRY_ID IN (SELECT * FROM TABLE(SPLIT('''
  144.          || p_country
  145.          || ''')))';
  146.    ELSIF p_country IS NOT NULL
  147.    THEN
  148.       qry5 :=
  149.             ' AND CSV.COUNTRY_ID IN (SELECT * FROM TABLE(SPLIT('''
  150.          || p_country
  151.          || ''')))';
  152.    ELSE
  153.       qry5 := '';
  154.    END IF;
  155.  
  156.    tmpstr4 := tmpstr3 || qry5;
  157. --   DBMS_OUTPUT.put_line (tmpstr4);
  158.  
  159.    IF p_state IS NOT NULL AND tmpstr4 IS NULL
  160.    THEN
  161.       qry6 :=
  162.             ' WHERE CSV.STATE_ID IN (SELECT * FROM TABLE(SPLIT('''
  163.          || p_state
  164.          || ''')))';
  165.    ELSIF p_state IS NOT NULL
  166.    THEN
  167.       qry6 :=
  168.             ' AND CSV.STATE_ID IN (SELECT * FROM TABLE(SPLIT('''
  169.          || p_state
  170.          || ''')))';
  171.    ELSE
  172.       qry6 := '';
  173.    END IF;
  174.  
  175.    tmpstr5 := tmpstr4 || qry6;
  176. --   DBMS_OUTPUT.put_line (tmpstr5);
  177.  
  178.    IF p_city IS NOT NULL AND tmpstr5 IS NULL
  179.    THEN
  180.       qry7 :=
  181.             ' WHERE CSV.CITY IN (SELECT * FROM TABLE(SPLIT('''
  182.          || p_city
  183.          || ''')))';
  184.    ELSIF p_city IS NOT NULL
  185.    THEN
  186.       qry7 :=
  187.          ' AND CSV.CITY IN (SELECT * FROM TABLE(SPLIT(''' || p_city
  188.          || ''')))';
  189.    ELSE
  190.       qry7 := '';
  191.    END IF;
  192.  
  193.    tmpstr6 := tmpstr5 || qry7;
  194. --   DBMS_OUTPUT.put_line (tmpstr6);
  195.  
  196.    IF p_status IS NOT NULL AND tmpstr6 IS NULL
  197.    THEN
  198.       qry8 :=
  199.             ' WHERE CSV.COMPANY_STATUS IN (SELECT * FROM TABLE(SPLIT('''
  200.          || p_status
  201.          || ''')))';
  202.    ELSIF p_status IS NOT NULL
  203.    THEN
  204.       qry8 :=
  205.             ' AND CSV.COMPANY_STATUS IN (SELECT * FROM TABLE(SPLIT('''
  206.          || p_status
  207.          || ''')))';
  208.    ELSE
  209.       qry8 := '';
  210.    END IF;
  211.  
  212.    tmpstr7 := tmpstr6 || qry8;
  213. --   DBMS_OUTPUT.put_line (tmpstr7);
  214.  
  215.    IF p_oper_asset IS NOT NULL AND tmpstr7 IS NULL
  216.    THEN
  217.       IF p_oper_asset = '<'
  218.       THEN
  219.          assetqry := ' WHERE CSV.ASSETS < ' || p_assets_1;
  220.       ELSIF p_oper_asset = '>'
  221.       THEN
  222.          assetqry := ' WHERE CSV.ASSETS > ' || p_assets_1;
  223.       ELSIF p_oper_asset = '='
  224.       THEN
  225.          assetqry := ' WHERE CSV.ASSETS = ' || p_assets_1;
  226.       ELSIF p_oper_asset = 'BETWEEN'
  227.       THEN
  228.          assetqry :=
  229.             ' WHERE CSV.ASSETS BETWEEN ' || p_assets_1 || ' AND '
  230.             || p_assets_2;
  231. --ELSIF P_OPER_ASSET ='BLANK' THEN
  232. --ASSETQRY:=' WHERE CSV.ASSETS IS NULL';
  233.       ELSIF p_oper_asset = 'NONBLANK'
  234.       THEN
  235.          assetqry := ' WHERE CSV.ASSETS  IS NOT NULL';
  236.       END IF;
  237.    ELSIF tmpstr7 IS NOT NULL
  238.    THEN
  239.       IF p_oper_asset = '<'
  240.       THEN
  241.          assetqry := ' AND CSV.ASSETS < ' || p_assets_1;
  242.       ELSIF p_oper_asset = '>'
  243.       THEN
  244.          assetqry := ' AND CSV.ASSETS > ' || p_assets_1;
  245.       ELSIF p_oper_asset = '='
  246.       THEN
  247.          assetqry := ' AND CSV.ASSETS = ' || p_assets_1;
  248.       ELSIF p_oper_asset = 'BETWEEN'
  249.       THEN
  250.          assetqry :=
  251.             ' AND CSV.ASSETS BETWEEN ' || p_assets_1 || ' AND ' || p_assets_2;
  252. --ELSIF P_OPER_ASSET ='BLANK' THEN
  253. --ASSETQRY:=' AND CSV._ASSETS IS NULL';
  254.       ELSIF p_oper_asset = 'NONBLANK'
  255.       THEN
  256.          assetqry := ' AND CSV.ASSETS IS NOT NULL';
  257.       END IF;
  258.    ELSIF p_oper_asset IS NULL
  259.    THEN
  260.       assetqry := '';
  261.    END IF;
  262.  
  263.    tmpstr8 := tmpstr7 || assetqry;
  264. --   DBMS_OUTPUT.put_line (tmpstr8);
  265.  
  266.    IF p_oper_date IS NOT NULL AND tmpstr8 IS NULL
  267.    THEN
  268.       IF p_oper_date = '<'
  269.       THEN
  270.          dateqry := ' WHERE TRUNC(CSV.MOD_DATE) < ''' || p_date1 || '''';
  271.       ELSIF p_oper_date = '>'
  272.       THEN
  273.          dateqry := ' WHERE TRUNC(CSV.MOD_DATE) > ''' || p_date1 || '''';
  274.       ELSIF p_oper_date = '='
  275.       THEN
  276.          dateqry := ' WHERE TRUNC(CSV.MOD_DATE) = ''' || p_date1 || '''';
  277.       ELSIF p_oper_date = 'BETWEEN'
  278.       THEN
  279.          dateqry :=
  280.                ' WHERE TRUNC(CSV.MOD_DATE) BETWEEN '''
  281.             || p_date1
  282.             || ''''
  283.             || ' AND '''
  284.             || p_date2
  285.             || '''';
  286. --ELSIF P_OPER_DATE ='BLANK' THEN
  287. --DATEQRY:=' WHERE TRUNC(CSV.MOD_DATE) IS NULL';
  288.       ELSIF p_oper_date = 'NONBLANK'
  289.       THEN
  290.          dateqry := ' WHERE TRUNC(CSV.MOD_DATE)  IS NOT NULL';
  291.       END IF;
  292.    ELSIF tmpstr8 IS NOT NULL
  293.    THEN
  294.       IF p_oper_date = '<'
  295.       THEN
  296.          dateqry := ' AND TRUNC(CSV.MOD_DATE) < ''' || p_date1 || '''';
  297.       ELSIF p_oper_date = '>'
  298.       THEN
  299.          dateqry := ' AND TRUNC(CSV.MOD_DATE) > ''' || p_date1 || '''';
  300.       ELSIF p_oper_date = '='
  301.       THEN
  302.          dateqry := ' AND TRUNC(CSV.MOD_DATE) = ''' || p_date1 || '''';
  303.       ELSIF p_oper_date = 'BETWEEN'
  304.       THEN
  305.          dateqry :=
  306.                ' AND TRUNC(CSV.MOD_DATE) BETWEEN '''
  307.             || p_date1
  308.             || ''''
  309.             || ' AND '''
  310.             || p_date2
  311.             || '''';
  312. --ELSIF P_OPER_DATE ='BLANK' THEN
  313. --DATEQRY:=' AND TRUNC(CSV.MOD_DATE) IS NULL';
  314.       ELSIF p_oper_date = 'NONBLANK'
  315.       THEN
  316.          dateqry := ' AND TRUNC(CSV.MOD_DATE) IS NOT NULL';
  317.       END IF;
  318.    ELSIF p_oper_date IS NULL
  319.    THEN
  320.       dateqry := '';
  321.    END IF;
  322.  
  323.    tmpstr9 := tmpstr8 || dateqry;
  324. --   DBMS_OUTPUT.put_line (tmpstr9);
  325.    finalqry :=
  326.          mainqry
  327.       || qry1
  328.       || qry2
  329.       || qry3
  330.       || qry4
  331.       || qry5
  332.       || qry6
  333.       || qry7
  334.       || qry8
  335.       || assetqry
  336.       || dateqry
  337.       || qry9;
  338.  
  339.    OPEN rec FOR finalqry;
  340.  
  341.    DBMS_OUTPUT.put_line (finalqry);
  342. EXCEPTION
  343.    WHEN NO_DATA_FOUND
  344.    THEN
  345.       raise_application_error (-20001, 'NO DATA FOUND...!');
  346.    WHEN OTHERS
  347.    THEN
  348.       DBMS_OUTPUT.put_line (SQLERRM);
  349. END proc_company_advance_search;
  350.  
May 18 '09 #1
Share this Article
Share on Google+