|
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; |
| |