Solved

how to solve below error ?

Posted on 2014-07-31
13
1,786 Views
Last Modified: 2014-08-07
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
Comment
Question by:deve_thomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40231475
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
 

Author Comment

by:deve_thomos
ID: 40231478
Hi Guy henge,
how i will do that can you please help me ?


Regards
Thomos
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40231500
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 32

Expert Comment

by:awking00
ID: 40231520
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
 

Author Comment

by:deve_thomos
ID: 40231554
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40231561
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
 

Author Comment

by:deve_thomos
ID: 40231651
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40231767
interesting, as this worked on my oracle 11 database ...
I will need to test again
0
 

Author Comment

by:deve_thomos
ID: 40231940
Hi expert,
for me again i am getting same error.

please help me.
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40232318
Hello deve, what is your Oracle database version?
0
 

Author Comment

by:deve_thomos
ID: 40232389
hello expert,

my oracle database is 11g
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40232491
"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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40232829
>> 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question