Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1

    Unanswered: Why it appears the duplicated elements at the end of string!

    Hello all,

    I got an issue to have the duplicated elements when convert the data column to row. I really appreciate if any PL/SQL expertise to help me on
    PL/SQL function as following:

    CREATE OR REPLACE FUNCTION FN_CONVERT_COLUMNtoROW(P_BOOKID IN VARCHAR2) RETURN VARCHAR2
    IS
    BOOKID_HOLD VARCHAR2(100) :='' ;
    BOOKID VARCHAR2(4000) :='' ;

    CURSOR BOOKIDCURSOR IS

    SELECT UNIQUE BOOKID
    FROM BOOK
    WHERE BOOK.BOOKID = P_BOOKID;

    BEGIN
    OPEN BOOKIDCURSOR;
    LOOP
    FETCH BOOKIDCURSOR INTO BOOKID_HOLD;
    BOOKID:= BOOKID|| BOOKID_HOLD || ', ';
    EXIT WHEN BOOKIDCURSOR%NOTFOUND;
    END LOOP;

    RETURN BOOKID;

    END FN_CONVERT_COLUMNtoROW;

    ================================================== ====
    When I run the inside query with the real value for P_BOOKID as bellowed:

    SELECT UNIQUE BOOKID
    FROM BOOK
    WHERE BOOK.BOOKID = P_BOOKID;

    The results came out no duplicated elements.

    If I ran the FN_CONVERT_COLUMNtoROW function, the results produced the duplicated elements at the end. Thanks in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Begin
    open bookidcursor;
    loop
    fetch bookidcursor into bookid_hold;
    exit when bookidcursor%notfound;
    bookid:= bookid|| bookid_hold || ', ';
    end loop;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1
    It worked very well. Thank you so much for your help quckly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •