Re: Create a user defined function

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 Received on Mon Mar 13 2006 - 04:00:28 CST