Just my stuff

Categories

If you need to concatenate varchars:

CREATE OR REPLACE FUNCTION Fnc_Concat_List (cur SYS_REFCURSOR, separator VARCHAR2) RETURN VARCHAR2 IS
    ret VARCHAR2(32000);
    tmp VARCHAR2(4000);
BEGIN
    LOOP
    FETCH cur INTO tmp;
        EXIT WHEN cur%NOTFOUND;
        ret := ret || separator || tmp;
    END LOOP;

    ret := SUBSTR(ret, LENGTH(separator) + 1);
    RETURN ret;
END;
/

Example:

CREATE TABLE hallo (ID number(10) PRIMARY KEY, NAME varchar2(255));
INSERT INTO hallo (ID, NAME) VALUES (1, 'a');
INSERT INTO hallo (ID, NAME) VALUES (2, 'b');
INSERT INTO hallo (ID, NAME) VALUES (3, 'c');
COMMIT;

SELECT fnc_concat_list(CURSOR(SELECT name from hallo), ', ') from dual;

Oracle pipelined function

  
CREATE TABLE hallo (ID number(10) PRIMARY KEY, NAME varchar2(255));
INSERT INTO hallo (ID, NAME) VALUES (1, 'a');
INSERT INTO hallo (ID, NAME) VALUES (2, 'b');
INSERT INTO hallo (ID, NAME) VALUES (3, 'c');
COMMIT;

CREATE OR REPLACE PACKAGE hoi IS
    TYPE hallo_lines IS TABLE OF hallo%ROWTYPE;
    FUNCTION h RETURN hallo_lines pipelined;
END;

CREATE OR REPLACE PACKAGE BODY hoi IS

    FUNCTION h RETURN hallo_lines pipelined IS
        r_hallo         hallo%ROWTYPE;
    BEGIN

        FOR i IN (SELECT ID, NAME FROM hallo)
        LOOP
            r_hallo := i;
            -- r_hallo.ID := i.ID;
            -- r_hallo.NAME := i.NAME;

            PIPE ROW(r_hallo);
        END LOOP;
    END;
END;

select * from table(hoi.h);

Just my stuff

diffuse Search

Posts

Categories

links

Oracle concat function

Oracle pipelined function

include '*'