PL-SQL stored procedure split string

Question

What I would like to do is this:

In java I have an array like ['AB','BC','CD','DE'] which I want to concat to something like "AB,BC,CD,DE" and send it to the procedure as an argument.

In the procedure, my idea is, I would like to do something like

v_passedArgs --(AB,BC,CD,DE)

SELECT * FROM SOME_TABLE WHERE SOME_COL IN (v_passedArgs.split(','))

Is it possible to do something like that or maybe you have another idea? Thank you

score 0 · Answer 1 · edited May 23 '17 at 11:52

0

You can create a split function in the database and use it for splitting a string in SQL which has delimiters(',' in your example)

Is there a function to split a string in PL/SQL?

Refer the above link to create a split function and use it in your select statement

edited May 23 '17 at 11:52

Community

1
1

answered Jan 20 '17 at 09:47

Devi Shankar Mohanty

1
1

_Links to external resources are encouraged, but please add context around the link so your fellow users will have some idea what it is and why it’s there. Always quote the most relevant part of an important link, in case the target site is unreachable or goes permanently offline._ – Bugs Jan 20 '17 at 09:50

score 0 · Answer 2 · answered Jan 20 '17 at 10:19

You have to create your own function.

You can work with an Oracle PL/SQL collection; here is a piece of code to return such a collection, from an input string list (p_list) with a given separator (p_sep):

CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE
FUNCTION cto_table(p_sep in Varchar2, p_list IN VARCHAR2)
  RETURN t_my_list
AS
  l_string VARCHAR2(32767) := p_list || p_sep;
  l_sep_index PLS_INTEGER;
  l_index PLS_INTEGER := 1;
  l_tab t_my_list     := t_my_list();
BEGIN
  LOOP
    l_sep_index := INSTR(l_string, p_sep, l_index);
    EXIT
  WHEN l_sep_index = 0;
    l_tab.EXTEND;
    l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));
    l_index            := l_sep_index + 1;
  END LOOP;
  RETURN l_tab;
END cto_table;
/

Then how to use it with the TABLE keyword in your SELECT - the TABLE keyword converts the collection into a object usable inside Oracle SQL queries:

SELECT * FROM SOME_TABLE WHERE SOME_COL IN (
      select * from TABLE(cto_table(',', v_passedArgs))
)

PL-SQL stored procedure split string

2 Answers2

Linked