Any programmer when thinks about PL/SQL expects certain functionality by default to be provided by the programming language for rapid coding. This helps to concentrate more on the business part rather than the technical complexities.With that in mind I would like to share a simple topic of converting a string in to array in PL/SQL, which is simple but a yet a powerful tool to ease most of out programming burdens.
Challenge
PL/SQL does not provide a function to convert string in to array just like any other programming language, where in the string is delimited by a special character say comma.
The attached procedure capabilities:
1) The procedure can split the string in to an array
2) if no delimiter is found it will return the string in array.
Execution
Once can use in the PL/SQL Code anf invoke them like,
SPLIT2ARRAY (‘1,2,3’, ‘,’) ;
The output will be an array [1,2,3].
The Procedure is as follows:
set serveroutput on ;
CREATE OR REPLACE PACKAGE STRING_FNC
IS
TYPE t_array IS TABLE OF VARCHAR2(3900)
INDEX BY BINARY_INTEGER;
FUNCTION SPLIT2ARRAY (p_in_string VARCHAR2, p_delim VARCHAR2) RETURN t_array;
END;
/
CREATE OR REPLACE PACKAGE BODY STRING_FNC
IS
FUNCTION SPLIT2ARRAY (p_in_string VARCHAR2, p_delim VARCHAR2) RETURN t_array
IS
i number :=0;
pos number :=0;
lv_str varchar2(300) := p_in_string;
strings t_array;
BEGIN
— determine first chuck of string
pos := instr(lv_str,p_delim,1,1);
IF pos = 0 THEN
strings(1) := lv_str;
END IF ;
— while there are chunks left, loop
WHILE ( pos != 0) LOOP
— increment counter
i := i + 1;
— create array element for chuck of string
strings(i) := substr(lv_str,1,pos-1);
— remove chunk from string
lv_str := substr(lv_str,pos+1,length(lv_str));
— determine next chunk
pos := instr(lv_str,p_delim,1,1);
— no last chunk, add to array
IF pos = 0 THEN
strings(i+1) := lv_str;
END IF;
END LOOP;
— return array
RETURN strings;
END SPLIT2ARRAY;
END;
/
show error ;
