String Input To An Oracle Stored Procedure
This is a scenario that occurs when a string is input to an Oracle stored procedure as an input parameter. When a string of numbers is input, a non-numeric input error is displayed if the where clause were to use the literal string as the input parameter as shown in the query below.
 
"SELECT ... FROM sales_detail WHERE division_id IN (p_any_string)".
 
In such cases, the string of numbers need to be parsed into an array and data summarized element by element of the array. Below is an example that shows how to get a resultset for an input array. The string input used in the example is comma delimited. The code can be tweaked to accept other types of string delimiters.
 
A generic package for type declarations
 
CREATE OR REPLACE PACKAGE data_io_types AS
 TYPE refcursor IS REF CURSOR;
 TYPE tab_Div IS TABLE OF
 dw.division_master.division_id%TYPE INDEX BY BINARY_INTEGER;
END data_io_types;
 
Procedure To Get Sales
 
CREATE OR REPLACE PROCEDURE get_location_sales(p_divisions IN VARCHAR2, p_out OUT data_io_types.refcursor) AS
   v_div    data_io_types.tab_Div;
   v_sdiv    VARCHAR2(2000);
   i    BINARY_INTEGER := 0;
   j    BINARY_INTEGER := 0;
   k    BINARY_INTEGER := 0;
   v_count    BINARY_INTEGER := 0;
   v_cost    NUMBER := 0;
   v_tcost    NUMBER := 0;
BEGIN
  v_sdiv := p_divisions;
  WHILE INSTR(v_sdiv,',') >= 0
  LOOP
    i := i + 1;
    IF INSTR(v_sdiv,',') > 0 THEN
     v_div(i) := SUBSTR(v_sdiv,1,INSTR(v_sdiv,',')-1);
     v_sdiv := SUBSTR(v_sdiv,INSTR(v_sdiv,',')+1);
    ELSE
     v_div(i) := v_sdiv;
     SELECT COUNT(*), NVL(SUM(sales),0)
     INTO k, v_cost
     FROM dw.sales_detail
     WHERE legacy_division_id = v_div(i);
     v_count := v_count + k;
     v_tcost := v_tcost + v_cost;
     EXIT WHEN INSTR(v_sdiv,',') = 0;
    END IF;
     SELECT COUNT(*), NVL(SUM(sales),0)
     INTO k, v_cost
     FROM dw.sales_detail
     WHERE legacy_division_id = v_div(i);
     v_count := v_count + k;
     v_tcost := v_tcost + v_cost;
  END LOOP;
 
  OPEN p_out FOR
  SELECT
  i total_divisions, v_count active_divisions, v_tcost total_sales
  FROM DUAL;
 
  EXCEPTION
   WHEN OTHERS THEN RAISE;
  END get_location_sales;
 

 
Testing The Code
 




For corrections or feedback, other useful links please contact webmaster

  1473