Home » SQL & PL/SQL » SQL & PL/SQL » Function Row to Column (Oracle, 8.1.7, Windows Server 2003)
Function Row to Column [message #339688] Fri, 08 August 2008 02:26 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
CREATE OR REPLACE FUNCTION MSS.rowconcat(q in VARCHAR2) RETURN VARCHAR2 IS
ret VARCHAR2(4000);
hold VARCHAR2(4000);
TYPE cur IS REF CURSOR;
v_cur cur;
BEGIN
OPEN v_cur FOR q;
LOOP
FETCH v_cur INTO hold;
EXIT WHEN v_cur%NOTFOUND;
IF ret IS NULL THEN
ret := hold;
ELSE
ret := ret || ',' || hold;
END IF;
END LOOP;
RETURN ret;
END;
/

I have the above funtion but when I run along with my sql it gives me

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "MSS.ROWCONCAT", line 14
ORA-06512: at line 1

Can anyone help me? My version of Oracle is 8.1.7 running under Win Ser 2003.

Re: Function Row to Column [message #339693 is a reply to message #339688] Fri, 08 August 2008 02:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I reckon you've blown the 4000 chr limit on your string size.
Add a line about the RET := RET||','||HOLD to use Dbms_Output to output the sizes of RET and HOLD, and see what happens.
Re: Function Row to Column [message #339697 is a reply to message #339693] Fri, 08 August 2008 02:45 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
hi, i tried to using 100 each for ret and hold but still the same error.
Re: Function Row to Column [message #339701 is a reply to message #339693] Fri, 08 August 2008 02:50 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi,

I use my sql with the following along with the rowconcat function.

SELECT customer_name, model, reg_no, reg_date, chs_no, eng_no, arrival_date
,rowconcat ('SELECT rono_jobdesc FROM mss_v_row_to_column') AS ros
FROM mss_v_row_to_column
GROUP BY customer_name, model, reg_no, reg_date, chs_no, eng_no, arrival_date;
Re: Function Row to Column [message #339702 is a reply to message #339697] Fri, 08 August 2008 02:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you've got hold of the wrong end of my explanation.

I suspect that the total aamount of data that you are fetching from the query, including delimiter characters, comes to more then 4000 bytes.
If you add the DBMS_OUTPUT statement as I described, it will provide a running total of the amount of space used, and confirm whether or not this is the situation.
Kindly help to solve this function (row to column) [message #340194 is a reply to message #339688] Mon, 11 August 2008 20:39 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
I have the following function but the output goes in a loop and displays error

ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 84
ORA-06512: at "SYS.DBMS_OUTPUT", line 58
ORA-06512: at "MSS.ROWCONCAT", line 15
ORA-06512: at line 1

Please see the attached text file output from DBMS_OUTPUT.

/* Formatted on 2008/08/12 09:30 (Formatter Plus v4.8.Cool */
CREATE OR REPLACE FUNCTION mss.rowconcat (q IN VARCHAR2)
RETURN VARCHAR2
AS
TYPE cur IS REF CURSOR;

ret VARCHAR2 (4000);
hold VARCHAR2 (4000);
v_cur cur;
BEGIN
OPEN v_cur FOR q;

LOOP
FETCH v_cur
INTO hold;

EXIT WHEN v_cur%NOTFOUND;
IF ret IS NULL THEN
ret := hold;
ELSE
DBMS_OUTPUT.put_line ('no of char ' || ret);
ret := ret || ',' || hold;
END IF;
END LOOP;

CLOSE v_cur;

RETURN ret;
END rowconcat;
/

My sql by using the above function is

SELECT model, reg_no, reg_date, chs_no, eng_no, arrival_date
,rowconcat('SELECT job_desc FROM mss_v_row_to_column') AS ros
FROM mss_v_row_to_column
GROUP BY model, reg_no, reg_date, chs_no, eng_no, arrival_date;

Can anyone help me to solve this?
Re: Function Row to Column [message #340195 is a reply to message #339688] Mon, 11 August 2008 20:55 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
Why is it so hard to keep lines less than 255 bytes?

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

Are you unwilling or incapable to follow the Posting Guidelines?

Since you chose to NOT follow Posting Guidelines, we choose to not assist you.
CREATE OR REPLACE FUNCTION mss.RowConCat
     (q  IN VARCHAR2)
RETURN VARCHAR2
AS
  TYPE Cur IS REF CURSOR;
  ret    VARCHAR2(4000);
  Hold   VARCHAR2(4000);
  v_Cur  CUR;
BEGIN
  OPEN v_Cur FOR q;
  
  LOOP
    FETCH v_Cur INTO Hold;
    
    EXIT WHEN v_Cur%NOTFOUND;
    
    IF ret IS NULL  THEN
      ret := Hold;
    ELSE
      dbms_Output.Put_Line('no of char '
                           ||ret);
      
      ret := ret
             ||','
             ||Hold;
    END IF;
  END LOOP;
  
  CLOSE v_Cur;
  
  RETURN ret;
END RowConCat;
/


You're On Your Own (YOYO)!

[Updated on: Mon, 11 August 2008 22:08] by Moderator

Report message to a moderator

Re: Function Row to Column [message #340221 is a reply to message #339693] Tue, 12 August 2008 00:50 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
JRowbottom wrote on Fri, 08 August 2008 09:30
I reckon you've blown the 4000 chr limit on your string size.
Add a line about the RET := RET||','||HOLD to use Dbms_Output to output the sizes of RET and HOLD, and see what happens.

Quote:
DBMS_OUTPUT.put_line ('no of char ' || ret);

This is NOT what JRowbottom suggested. Of course it errors out.
Output the SIZE of ret and hold, not ret itself
Previous Topic: two oldest values
Next Topic: Sql problem
Goto Forum:
  


Current Time: Sun Dec 04 04:44:09 CST 2016

Total time taken to generate the page: 0.15525 seconds