Solved

how to solve below error ?

Posted on 2014-07-31
13
1,414 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
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
Hi Guy henge,
how i will do that can you please help me ?


Regards
Thomos
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:deve_thomos
Comment Utility
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 142

Expert Comment

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

Author Comment

by:deve_thomos
Comment Utility
Hi expert,
for me again i am getting same error.

please help me.
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
Comment Utility
Hello deve, what is your Oracle database version?
0
 

Author Comment

by:deve_thomos
Comment Utility
hello expert,

my oracle database is 11g
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
"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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>> 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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 shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now