Re: Create a user defined function

Alexander Mueller schrieb:

> Laurenz Albe schrieb:
>
> > Alexander Mueller <millerax_at_hotmail.com> wrote:
> > > I need to create a user defined function that i can embed into
> > > SQL-statements, that returns a string. the objective is to get a 'flat
> > > list' of all employees that are associated to a certain order.
> > > I never wrote a SP or user defined function so i can only show you the
> > > pieces of code that i need to put together:
> >
> > [...]
> >
> > > My Questions:
> >
> > [...]
> >
> > My question: why don't you do it in PL/SQL?
>

Happy, i could solve it on my own:

Mfg,
Alex

CREATE OR REPLACE FUNCTION "UF_EMPLIST" (
  id in int,
  delim in VARCHAR2 default ';'
)

return VARCHAR2
is

/* declare select for cursor */
cursor empCursor is
  SELECT
    TYPISTNAME EMP FROM <some tables>
  WHERE
    <some conditions>
  GROUP BY <columnlist>;

  str_return VARCHAR2(32767);
  str_val VARCHAR2(255);

begin

  /* open empCursor */
  open empCursor;

  loop

    /* copy row to string-var */
    fetch empCursor into str_val;

    exit when empCursor%NOTFOUND;

    /* very unlikely but watch max string len */     if ( (LENGTH (str_return) + LENGTH (str_val)) > 32767) then

      Close empCursor;
      return (str_return);

    end if;

    /* add delim if there already is an entry and new value is not empty */

    str_val := RTRIM(LTRIM(str_val));
    if (LENGTH (str_val) > 0) then

      if (LENGTH (str_return) > 0) then
        str_return := str_return || delim;
      end if;

      str_return := str_return || str_val;

     end if;

  end loop;

  Close empCursor;
  return (str_return);

end UF_EMPLIST_ORDER; Received on Fri Mar 10 2006 - 05:35:14 CST