473,320 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,320 developers and data experts.

Procedure to accept a list of values in a single parameter

debasisdas
8,127 Expert 4TB
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
0 6302

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Vipul Pathak | last post by:
Hello Friends ! I have the Following Code, that Executes a Stored Procedure and Attempt to read a Returned Integer Value from the StoredProc. But It gives Error ... ADODB.Command (0x800A0BB9)...
7
by: Bill Kellaway | last post by:
Hi there - this should be fairly simple for someone. Basically I can't figure out how to pass the parameters from ASP to a Stored Procedure on SQL. Here's my code: I just need to help in...
5
by: Eugene Anthony | last post by:
I have created the following stored procedure whereby it will check whether the categoryID is valid and if it is then the updation will be performed, else a -1 will be returned. This procedure will...
1
by: Mark Dicken | last post by:
Hi All I have found the following Microsoft Technet 'Q' Article :- Q210368 -ACC2000: How to Pass an Array as an Argument to a Procedure (I've also copied and pasted the whole contents into...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
9
by: serge | last post by:
/* Subject: How to build a procedure that returns different numbers of columns as a result based on a parameter. You can copy/paste this whole post in SQL Query Analyzer or Management Studio...
9
by: fniles | last post by:
I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table....
0
by: Atos | last post by:
SINGLE-LINKED LIST Let's start with the simplest kind of linked list : the single-linked list which only has one link per node. That node except from the data it contains, which might be...
20
by: billmaclean1 | last post by:
I need to write a stored procedure that selects from a table and returns the result set. I don't always know the TableSchema that I need to use when qualifying the table at run-time Example:...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.