Laurenz Albe wrote:
> DA Morgan <damorgan_at_psoug.org> wrote:
>>> 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;
>> Solved it perhaps but with horribly inefficient code. There is
>> almost no place in Oracle (9i or above) where a cursor loop is
>> a good idea.
>>
>> Take a look at array processing instead. You will find examples
>> in Morgan's Library at www.psoug.org. Click on Bulk Binding.
>
> Maybe performance is not an issue here.
>
> Laurenz Albe
Maybe latches are. Who can say.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Mon Mar 13 2006 - 10:24:45 CST
