Solved

how to solve below error ?

Posted on 2014-07-31
13
1,546 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]
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 142

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
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.

 
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 142

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 142

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 37

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 76

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

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.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

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