CODEWITHSUNDEEP
oracle-databaseplsql
Gleb
Gleb

Reputation: 1432

Return table from function. pl/sql

I'am new in oracle. I have a function on TSQL and I need to translate it to pl/sql. Function is separating incoming string with some rules. So I try this:

create or replace
FUNCTION FN_PROPSTOTABLE(stValues in varchar2) RETURN   POPSTBL AS
tbl PROPSTBL;
s varchar2(8000);
ind varchar2(100);
val varchar2(100);
p int;
tmp varchar2(8000);
BEGIN
  tmp:=stValues;
  while LENGTH(tmp)>0
  loop
    p:=instr(tmp, ':');
    IF p=0 THEN
      p:=LENGTH(tmp);
    END IF;
    s:=SUBSTR(tmp, 0, p);
    tmp:=SUBSTR(tmp, LENGTH(s)+1);
    s:=REPLACE(s, ':', '');
    ind:=substr(s,0, instr(s, '|')-1);
    val:=substr(s,instr(s, '|')+1);

   select b bulk collect into tbl 
     from (select props(ind, val) b from dual); 

   end loop;

  RETURN tbl;
END FN_PROPSTOTABLE;

Thats works fine, but it returns just last substring. Please help me.

Upvotes: 1

Views: 1347

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221220

How about making that a PIPELINED function (one of the most underused features in my opinion)?

CREATE OR REPLACE
FUNCTION FN_PROPSTOTABLE(stValues in varchar2) RETURN PROPSTBL 

-- Add the "PIPELINED" modifier here
PIPELINED AS
  tbl PROPSTBL;
  s   varchar2(8000);
  ind varchar2(100);
  val varchar2(100);
  p   int;
  tmp varchar2(8000);
BEGIN
  tmp := stValues;
  WHILE LENGTH(tmp) > 0
  LOOP
    p := INSTR(tmp, ':');

    IF p=0 THEN
      p := LENGTH(tmp);
    END IF;

    s   := SUBSTR(tmp, 0, p);
    tmp := SUBSTR(tmp, LENGTH(s)+1);
    s   := REPLACE(s, ':', '');
    ind := SUBSTR(s,0, instr(s, '|')-1);
    val := SUBSTR(s,instr(s, '|')+1);

    -- Emit resulting rows using "PIPE"
    PIPE ROW(props(ind, val));
  END LOOP;

  RETURN;
END FN_PROPSTOTABLE;

And then call it like this:

SELECT * FROM TABLE(fn_propstotable('1|a:2|b'))

The reason why your solution didn't work was because you kept overwriting your table type (instead of appending to it) every time you iterated in your loop.

Upvotes: 4

Related Questions