Home » SQL & PL/SQL » SQL & PL/SQL » String Buffer Error
String Buffer Error [message #207429] Tue, 05 December 2006 09:10 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
I am trying to display the output of a column in a single line seperated by commas,I used this fucntion STRAGG
from asktom site
http://asktom.oracle.com/pls/ask/f?p=4950:8:14253337443651534221::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2196162600402

CREATE OR REPLACE
type         STRING_AGG_TYPE as object
    (
       total varchar2(4000),
       static function
            ODCIAggregateInitialize(sctx IN OUT string_agg_type )
           return number,
       member function
           ODCIAggregateIterate(self IN OUT string_agg_type ,
                                value IN varchar2 )
           return number,
      member function
           ODCIAggregateTerminate(self IN string_agg_type,
                                  returnValue OUT  varchar2,
                                  flags IN number)
           return number,
      member function
           ODCIAggregateMerge(self IN OUT string_agg_type,
                              ctx2 IN string_agg_type)
           return number   );
/

CREATE OR REPLACE
type body         STRING_AGG_TYPE
    is

    static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
    return number
    is
    begin
        sctx := string_agg_type( null );
        return ODCIConst.Success;
   end;

  member function ODCIAggregateIterate(self IN OUT string_agg_type,
                                        value IN varchar2 )
   return number
   is
   begin
       self.total := self.total || ',' || value;
       return ODCIConst.Success;
   end;

   member function ODCIAggregateTerminate(self IN string_agg_type,
                                          returnValue OUT varchar2,
                                          flags IN number)
   return number
   is
   begin
       returnValue := ltrim(self.total,',');
       return ODCIConst.Success;
   end;

   member function ODCIAggregateMerge(self IN OUT string_agg_type,
                                      ctx2 IN string_agg_type)
   return number
   is
   begin
       self.total := self.total || ctx2.total;
       return ODCIConst.Success;
   end;


   end;
/

CREATE OR REPLACE FUNCTION STRAGG(INPUT varchar )   RETURN varchar
       PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/


and the select statement is
 select std_id,stragg(lockbox_no) from lockbox 
group by std_id


I am getting the error
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "STRING_AGG_TYPE", line 41
ORA-06512: at line 1


Then thinking the string length is not enough i changed only 1 line in the code the variable length
 total varchar2(4000), 


still the same error,
for small rows this worked, and this particular select is
getting 3040 rows of lockbox_no, seems we have use blob type how can i do that here
or any idea what more changes is required in the code.


Thanks

[Updated on: Tue, 05 December 2006 09:12]

Report message to a moderator

Re: String Buffer Error [message #207481 is a reply to message #207429] Tue, 05 December 2006 13:56 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You can use up to 32767 for the string, but you will need to limit the returnValue to 4000:

ltrim(substr(self.total, 1, 4000),',')
Re: String Buffer Error [message #207484 is a reply to message #207481] Tue, 05 December 2006 14:06 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Todd for follow up,

I tried with varchar2(32000) and then
with CLOB ,
for some select where column data is less it worked and with CLOB it is giving
code]ORA-00932: inconsistent datatypes: expected - got CLOB[/code]
no idea what am i missing here .

Thanks

[Updated on: Tue, 05 December 2006 14:06]

Report message to a moderator

Re: String Buffer Error [message #208256 is a reply to message #207484] Fri, 08 December 2006 19:07 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
There's no real point in using a CLOB here since you'll only be able to return 4K anyway. What error did you get with VARCHAR2(32000)? If you are trying to flatten out a large number of rows, it's possible you're running out of room even with 32K.

If so, you would need to to either pre-test the LENGTH of the string before adding to make sure you don't go over 32K, or wrap the concatenation in an exception block.
Re: String Buffer Error [message #226025 is a reply to message #208256] Thu, 22 March 2007 05:58 Go to previous message
agostino_neto
Messages: 180
Registered: July 2005
Senior Member
Hi faiz_hyd,

I had the same error with a sql which looks like:
--------------------------
SELECT Stragg(Column_Name)
FROM dba_tab_Columns
WHERE xxx
--------------------------
In my case, the problem was due to the fact that more than one objects with the same name and even with different type existed in the database.
After adding more conditions on the where clause, the error disappeared.
Previous Topic: Problem executing procedure in a Package
Next Topic: Row not found for 0 sql
Goto Forum:
  


Current Time: Thu Dec 08 06:15:12 CST 2016

Total time taken to generate the page: 0.08602 seconds