Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2019
  • Last Modified:

how to solve below error ?

Hello Expert,
below i have query. but i am getting this error.

how i will solve this one? want to see maximum 4000 characters because i am inserting the data to a column size is 4000.
SELECT   wm_concat(e_name)|| t_name )
from test1  t,  test2  R where t.tid = r.tID

Open in new window


ORA-22813: operand value exceeds system limits
22813. 00000 -  "operand value exceeds system limits"
*Cause:    Object or Collection value was too large. The size of the value
           might have exceeded 30k in a SORT context, or the size might be
           too big for available memory.
*Action:   Choose another value and retry the operation

Open in new window

0
deve_thomos
Asked:
deve_thomos
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
first of all, you should use LISTAGG function (documented and supported) instead of the wm_concat function

to avoid that you go across the 4K size limit of the varchar2 data type, you need to first limit the number of values before you apply listagg (wm_concat), eventually using a subquery with the windowing function doing a sum( lenght(e_name)) over (order by xxx) to return only up to 4K characters indeed;
0
 
deve_thomosAuthor Commented:
Hi Guy henge,
how i will do that can you please help me ?


Regards
Thomos
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
something like this should do:

select listagg(e_name) within group (order by rn asc) || t_name
SELECT  e_name,  t_name 
  , sum(length(e_name)) over (order by <some ordering>) rl
  , row_number() over (order by <some ordering>) rn
from test1  t
join test2 R on t.tid = r.tID
)
where rl + length(t_name) <= 4000

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
awking00Commented:
Without qualifiers, I can't tell which name belongs to which table, but what do the following queries produce?
select max(length(e_name)) from <table_with_e_name>
select max(length(t_name)) from <table_with_t_name>
0
 
deve_thomosAuthor Commented:
Hi expert,


select listagg(e_name) within group (order by rn asc) || t_name
SELECT  e_name,  t_name 
  , sum(LENGTH(e_name)) OVER (ORDER BY tid) rl
  , row_number() over (order by tid) rn
from test1  t
join test2 R on t.tid = r.tID
)
WHERE rl + LENGTH(t_name) <= 4000

Open in new window


it showing error
ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"

Open in new window

note: see if data is exceeding morethan 4000 , i need see the data  upto 4000  and rest of can be truncate.

e_name is coming from test_1
t_name is coming from test2
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry:
select listagg(e_name) within group (order by rn asc) || t_name
FROM (
SELECT  e_name,  t_name 
  , sum(LENGTH(e_name)) OVER (ORDER BY tid) rl
  , row_number() over (order by tid) rn
from test1  t
join test2 R on t.tid = r.tID
)
WHERE rl + LENGTH(t_name) <= 4000
                                          

Open in new window

0
 
deve_thomosAuthor Commented:
Hello Expert,
below
i have table structucture..
CREATE TABLE test1(e_name VARCHAR2(4000),tid NUMBER(1));

CREATE TABLE test2(t_name VARCHAR2(4000),tid NUMBER(1));

Open in new window



select listagg(e_name) within group (order by rn asc) || t_name
FROM (
SELECT  t.e_name,  r.t_name 
  , sum(LENGTH(t.e_name)) OVER (ORDER BY t.tid) rl
  , row_number() over (order by t.tid) rn
from test1  t
join test2 R on t.tid = r.tID
)
WHERE rl + LENGTH(t_name) <= 4000;

Open in new window




error
ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"
*Cause:    
*Action:
Error at Line: 1 Column: 58
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
interesting, as this worked on my oracle 11 database ...
I will need to test again
0
 
deve_thomosAuthor Commented:
Hi expert,
for me again i am getting same error.

please help me.
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Hello deve, what is your Oracle database version?
0
 
deve_thomosAuthor Commented:
hello expert,

my oracle database is 11g
0
 
Mark GeerlingsDatabase AdministratorCommented:
"11g" is not a precise Oracle database version number, since Oracle released both 11.1 and 11.2 versions.

What does this query return in your system?

select * from v$version;

In one of our (11gR2) systems, that query returns this:

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
0
 
slightwv (䄆 Netminder) Commented:
>> want to see maximum 4000 characters because i am inserting the data to a column size is 4000.

So losing/truncating data is valid for you?  I would rethink this approach.  You typically don't want to drop data in a database.

Also, can you please provide sample data for your tables and expected results.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now