Convert a piece of SQL into an Oracle function

Question

My table "COMMA_SEPERATED" looks something like this

ID  NAME CITY
--- ---- -----------------------------
1   RAJ  CHENNAI, HYDERABAD, JABALPUR
2   SAM  BHOPAL,PUNE

I want to separate each City as a new record so my SQL is: (working fine)

SELECT id, TRIM(CITY_NEW)
FROM COMMA_SEPERATED, xmltable
(
    'if (contains($X,",")) then ora:tokenize($X,"\,") else $X'
    passing city AS X
    columns CITY_NEW varchar2(4000) path '.'
);

I want to convert this piece of SQL into a function so that i can simply call the function like this

SELECT id, split_function(city) FROM COMMA_SEPERATED

Output:

1   CHENNAI
1   HYDERABAD
1   JABALPUR
2   BHOPAL
2   PUNE

Can anyone help on how to do that? I am very new to PL/SQL.

Answer 1

Apart what @Alex showed, you can also create an object and get the object returned via function. See below:

--Created an object to hold your result columns
create or replace type Obj IS OBJECT (id number, city varchar2(20));
/
--Table of object
create or replace type var_obj is table of Obj;

/
--Function with return as with Object type.
create or replace function splt_fnct
return var_obj
as
var var_obj:=var_obj();

begin
Select obj(col,col1)
bulk collect into var
from (
Select  distinct  col , regexp_substr(col1,'[^,]+',1,level) col1
from tbl
connect by regexp_substr(col1,'[^,]+',1,level) is not null
order by 1);

return var;
end;

/
--Selecting result   

 select *  from table(splt_fnct);

Edit: I was trying with @Alex solution and got some error as shown below:

create or replace function splt_fnct(input_strng varchar2)
return var_obj
as
var var_obj:=var_obj();

begin
Select obj(col,col1)
bulk collect into var
from (
select tbl.col, t.rslt --<--This column name should the same as used in colmns clause in the below query. Its giving error "invalid column". How to handle this case.
FROM tbl, xmltable
(
    'if (contains($X,",")) then ora:tokenize($X,"\,") else $X'
    passing col1 AS X
    columns input_strng varchar2(4000) path '.'
) t
);
return var;
end;

/

Correction as per @Alex suggestion:

create or replace function splt_fnct(input_strng varchar2)
return var_obj
as
var var_obj:=var_obj();

begin

select obj(tbl.col, t.rslt) 
bulk collect into var
FROM tbl, xmltable
(
    'if (contains($X,",")) then ora:tokenize($X,"\,") else $X'
    passing input_strng AS X
    columns rslt  varchar2(4000) path '.'
) t;

return var;
end;

/

Answer 2

The query you're trying to get to:

SELECT id, split_function(city) FROM COMMA_SEPERATED

won't work, because you're trying to return multiple rows for each source row. You have to make it a bit more complicated than that unfortunately.

If the goal is to hide the splitting mechanism then the closest I can think of is to create a function which returns a collection of strings, which could be pipelined:

create or replace function split_function (p_string varchar2)
return sys.odcivarchar2list pipelined as
begin
  for r in (
    select result
    from xmltable (
      'if (contains($X,",")) then ora:tokenize($X,"\,") else $X'
      passing p_string as x
      columns result varchar2(4000) path '.'
    )
  )
  loop
    pipe row (trim(r.result));
  end loop;
end split_function;
/

Your proposed call would then give you one row per ID with a collection:

select id, split_function(city) from comma_seperated;

        ID SPLIT_FUNCTION(CITY)
---------- -----------------------------------------------------------------
         1 ODCIVARCHAR2LIST('CHENNAI', 'HYDERABAD', 'JABALPUR')
         2 ODCIVARCHAR2LIST('BHOPAL', 'PUNE')

which isn't quite what you want; but you can use a table collection expression and cross-join to convert into multiple rows instead:

select cs.id, t.column_value as city
from comma_seperated cs
cross join table(split_function(cs.city)) t;

        ID CITY                          
---------- ------------------------------
         1 CHENNAI                       
         1 HYDERABAD                     
         1 JABALPUR                      
         2 BHOPAL                        
         2 PUNE                          

db<>fiddle demo.

That isn't as simple as you hoped for, but is arguably still better than cross-joining to the xmltable(), particularly if you want to reuse that splitting logic/function in multiple places, as well as hide the details of how the split is done - which would let you change the mechanism easily if you wanted to, e.g. to use a more common regular expression to do the splitting.