Members   Search      Help    Register    Login    Home
Home » SQL & PL/SQL » SQL & PL/SQL » ORA-22813: operand value exceeds system limits(Oracle 10g)
ORA-22813: operand value exceeds system limits [message #474150] Fri, 03 September 2010 04:24 Go to next message
aarti323
Messages: 7
Registered: September 2010
Location: Pune
Junior Member
I have created the Type Object
CREATE OR REPLACE TYPE AMYLIN.type_all_cert as object
(
person_id char(20),
person_fname varchar2(100),
person_username varchar2(500),
person_lname varchar2(100),
manager_id char(20) ,
manager_fname varchar2(100),
manager_username varchar2(500),
manager_lname varchar2(100),
org_id varchar2(100),
org_name varchar2(100),
org_parent_id varchar2(100),
owner_id varchar2(20),
target_date date,
status varchar2(40),
status_desc varchar2(500),
acquired_on date,
cert_id nvarchar2(4000),
cert_name varchar2(3000),
cert_check char(1)
);
than i m using it in the stroed procedure, when i tried to used that procedure in report it give me
ORA-22813: operand value exceeds system limits error.
Please help me to find out the root cause of this error.

Report message to a moderator

Re: ORA-22813: operand value exceeds system limits [message #474151 is a reply to message #474150] Fri, 03 September 2010 04:27 Go to previous messageGo to next message
cookiemonster
Messages: 7480
Registered: September 2008
Location: Rainy Manchester
Senior Member
 
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.

Report message to a moderator

Re: ORA-22813: operand value exceeds system limits [message #474152 is a reply to message #474151] Fri, 03 September 2010 04:30 Go to previous messageGo to next message
aarti323
Messages: 7
Registered: September 2010
Location: Pune
Junior Member
how to find out the The size of the value system

Report message to a moderator

Re: ORA-22813: operand value exceeds system limits [message #474153 is a reply to message #474152] Fri, 03 September 2010 04:39 Go to previous messageGo to next message
aarti323
Messages: 7
Registered: September 2010
Location: Pune
Junior Member
after changing size of cert_id varchar2(3000) variable, im getting ORA-06502: PL/SQL: numeric or value error:character string buffer too small error

Report message to a moderator

Re: ORA-22813: operand value exceeds system limits [message #474154 is a reply to message #474153] Fri, 03 September 2010 04:42 Go to previous messageGo to next message
cookiemonster
Messages: 7480
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you're trying to assign data to it that's longer than 3000 characters.

Report message to a moderator

Re: ORA-22813: operand value exceeds system limits [message #474155 is a reply to message #474154] Fri, 03 September 2010 04:44 Go to previous messageGo to next message
aarti323
Messages: 7
Registered: September 2010
Location: Pune
Junior Member
i have to pass all certification id to the procedure, how to track this error

Report message to a moderator

Re: ORA-22813: operand value exceeds system limits [message #474156 is a reply to message #474155] Fri, 03 September 2010 04:48 Go to previous messageGo to next message
aarti323
Messages: 7
Registered: September 2010
Location: Pune
Junior Member
first i created the following object
CREATE OR REPLACE TYPE AMYLIN.type_all_cert as object
(
person_id char(20),
person_fname varchar2(100),
person_username varchar2(500),
person_lname varchar2(100),
manager_id char(20) ,
manager_fname varchar2(100),
manager_username varchar2(500),
manager_lname varchar2(100),
org_id varchar2(100),
org_name varchar2(100),
org_parent_id varchar2(100),
owner_id varchar2(20),
target_date date,
status varchar2(40),
status_desc varchar2(500),
acquired_on date,
cert_id nvarchar2(3000),
cert_name varchar2(3000),
cert_check char(1)
);

then

DROP TYPE AMYLIN.ORG_CERT;

CREATE OR REPLACE TYPE AMYLIN.org_cert is table of type_all_cert;


after thAt i m using it in following procedure:

CREATE OR REPLACE PROCEDURE AMYLIN.SCP_RPALLSUBORDOFORG_main_test
(
subordinate_status_resultset OUT scpkg_crlearningcursors.cur_cert_org,
organization_id IN CHAR,
xcertification_id IN VARCHAR2,
ReportDate IN DATE,
reporttype IN VARCHAR2,
statustype IN VARCHAR2,
javalocale IN VARCHAR2
)
AS
xxlocaleid CHAR (20);


org_cert_all org_cert:=org_cert();

cursor all_cert is SELECT distinct cert.cert_id
FROM
cmt_person per
inner join tpt_company org on per.company_id=org.id
left outer join pfv_pub_org_info orginfo on org.id =orginfo.node_id
left outer join cmt_person mgr on per.manager_id=mgr.id
inner JOIN
(SELECT
stud.owner_id ID,
TO_CHAR (stud.status) status,
s.description status_desc,
stud.target_date,
stud.acquired_on,
a.NAME,
a.ID cert_id
FROM (select * from tpt_ce_stud_certification stdcert
where TRUNC (stdcert.acquired_on) <= TRUNC (ReportDate) or stdcert.acquired_on is null ) stud
INNER JOIN (select * from tpt_ext_ce_certification where type=0 and STATUS =200) a
INNER JOIN tpt_id_ce_certification i
ON a.ID = i.ID
LEFT OUTER JOIN (select * from tpt_ext_ce_certification where type=0 and STATUS =200) p
ON a.parent_id = p.ID AND a.locale_id = p.locale_id
INNER JOIN fgt_detail det ON a.description = det.ID
ON i.ID = stud.certification_id
INNER JOIN fgv_sys_list_of_val s
ON s.NAME = stud.status
AND s.locale_id = a.locale_id
AND s.list_id = 'sysli000000000000158'
INNER JOIN tpv_pub_locale locale
ON a.locale_id = locale.ID
AND locale.ID = xxlocaleid
) cert
ON cert.ID = per.ID
WHERE
(orginfo.related_to=organization_id or (org.id=organization_id and orginfo.relation_type='I')) and
((per.terminated_on IS NOT NULL AND per.terminated_on > SYSDATE) OR (per.terminated_on IS NULL))
and DECODE (cert.id, NULL, '300','200') IN (statustype);

cursor emp_cert (xcert_id in char) is select
per.id per_id,
per.fname per_fname,
per.username per_username,
per.lname per_lname,
mgr.id mgr_id,
mgr.fname mgr_fname,
mgr.lname mgr_lname,
mgr.username mgr_username,
org.id org_id,
org.name2 org_name,
org.parent_id org_parent_id,
cert.id owner_id,
cert.target_date,
cert.status cert_status,
cert.status_desc,
cert.acquired_on,
xcert_id cert_id,
(select cert_name.name from tpt_ext_ce_certification cert_name where cert_name.id=xcert_id and cert_name.locale_id=xxlocaleid) cert_name,
nvl2(cert.id ,'Y','N')check_cert
FROM
cmt_person per
inner join tpt_company org on per.company_id=org.id
left outer join pfv_pub_org_info orginfo on org.id =orginfo.node_id
left outer join cmt_person mgr on per.manager_id=mgr.id
left outer JOIN
(SELECT stud.owner_id ID, TO_CHAR (stud.status) status,
s.description status_desc, stud.target_date,
stud.acquired_on, a.NAME, a.ID cert_id
FROM (select * from tpt_ce_stud_certification stdcert
where TRUNC (stdcert.acquired_on) <= TRUNC (ReportDate) or stdcert.acquired_on is null ) stud
INNER JOIN (select * from tpt_ext_ce_certification where type=0 and STATUS =200) a
INNER JOIN tpt_id_ce_certification i
ON a.ID = i.ID
LEFT OUTER JOIN (select * from tpt_ext_ce_certification where type=0 and STATUS =200) p
ON a.parent_id = p.ID AND a.locale_id = p.locale_id
INNER JOIN fgt_detail det ON a.description = det.ID
ON i.ID = stud.certification_id
INNER JOIN fgv_sys_list_of_val s
ON s.NAME = stud.status
AND s.locale_id = a.locale_id
AND s.list_id = 'sysli000000000000158'
INNER JOIN tpv_pub_locale locale
ON a.locale_id = locale.ID
AND locale.ID = xxlocaleid
WHERE a.id = xcert_id and TRUNC (stud.acquired_on) <= TRUNC (ReportDate)
and DECODE (stud.owner_id, NULL, '300','200') IN (statustype)
) cert ON cert.ID = per.ID
WHERE
(orginfo.related_to=organization_id or (org.id=organization_id and orginfo.relation_type='I')) and
((per.terminated_on IS NOT NULL AND per.terminated_on > SYSDATE) OR (per.terminated_on IS NULL));--and
--and DECODE (cert.owner_id, NULL, '300','200') IN (statustype);

--cursor NotNull_cert is SELECT distinct cert.cert_id
-- FROM cmt_person per
-- inner join tpt_company org on per.company_id=org.id
-- left outer join pfv_pub_org_info orginfo on org.id =orginfo.node_id
-- left outer join cmt_person mgr on per.manager_id=mgr.id
-- inner JOIN
-- (SELECT stud.owner_id ID, TO_CHAR (stud.status) status,
-- s.description status_desc, stud.target_date,
-- stud.acquired_on,a.NAME, a.ID cert_id
-- FROM (select * from tpt_ce_stud_certification stdcert
-- where TRUNC (stdcert.acquired_on) <= TRUNC (ReportDate) or stdcert.acquired_on is null ) stud
-- INNER JOIN (select * from tpt_ext_ce_certification where type=0 and STATUS =200) a
-- INNER JOIN tpt_id_ce_certification i
-- ON a.ID = i.ID
-- LEFT OUTER JOIN (select * from tpt_ext_ce_certification where type=0 and STATUS =200) p
-- ON a.parent_id = p.ID AND a.locale_id = p.locale_id
-- INNER JOIN fgt_detail det ON a.description = det.ID
-- ON i.ID = stud.certification_id
-- INNER JOIN fgv_sys_list_of_val s
-- ON s.NAME = stud.status
-- AND s.locale_id = a.locale_id
-- AND s.list_id = 'sysli000000000000158'
-- INNER JOIN tpv_pub_locale locale
-- ON a.locale_id = locale.ID
-- AND locale.ID = xxlocaleid
-- WHERE a.id = xcertification_id) cert
-- ON cert.ID = per.ID
-- WHERE (orginfo.related_to=organization_id or (org.id=organization_id and orginfo.relation_type='I')) and
-- ((per.terminated_on IS NOT NULL AND per.terminated_on > SYSDATE) OR (per.terminated_on IS NULL))--and
-- and DECODE (cert.id, NULL, '300','200') IN (statustype);


BEGIN

SELECT ID INTO xxlocaleid FROM fgt_locale WHERE java_locale = javalocale;


IF (xcertification_id IS NULL)
THEN
for c1 in all_cert
LOOP
FOR c2 in emp_cert(c1.cert_id)
LOOP
org_cert_all.extend;
org_cert_all(org_cert_all.last):=type_all_cert
(
c2.per_id,
c2.per_fname,
c2.per_username,
c2.per_lname,
c2.mgr_id,
c2.mgr_fname,
c2.mgr_username,
c2.mgr_lname,
c2.org_id,
c2.org_name,
c2.org_parent_id,
c2.owner_id,
c2.target_date,
c2.cert_status,
c2.status_desc,
c2.acquired_on,
c2.cert_id,
c2.cert_name,
c2.check_cert
);


END LOOP;

END LOOP;

OPEN subordinate_status_resultset FOR
Select * from (Select * from table(cast(org_cert_all as org_cert)));

elsIF (xcertification_id IS not NULL)
THEN
-- for c1 in NotNull_cert
-- LOOP
-- FOR c2 in emp_cert(c1.cert_id)
-- LOOP

-- org_cert_all.extend;
-- org_cert_all(org_cert_all.last):=type_all_cert
-- (
-- c2.per_id,
-- c2.per_fname,
-- c2.per_username,
-- c2.per_lname,
-- c2.mgr_id,
-- c2.mgr_fname,
-- c2.mgr_username,
-- c2.mgr_lname,
-- c2.org_id,
-- c2.org_name,
-- c2.org_parent_id,
-- c2.owner_id,
-- c2.target_date,
-- c2.cert_status,
-- c2.status_desc,
-- c2.acquired_on,
-- c2.cert_id,
-- c2.cert_name,
-- c2.check_cert
-- );


-- END LOOP;
--
-- END LOOP;
-- OPEN subordinate_status_resultset FOR
-- Select * from (Select * from table(cast(org_cert_all as org_cert)));
OPEN subordinate_status_resultset FOR
SELECT
per.id per_id,
per.fname per_fname,
per.username per_username,
per.lname per_lname,
mgr.id mgr_id,
mgr.fname mgr_fname,
mgr.lname mgr_lname,
mgr.username mgr_username,
org.id org_id,
org.name2 org_name,
org.parent_id org_parent_id,
cert.owner_id owner_id,
(select cert_name.name from tpt_ext_ce_certification cert_name where cert_name.id=xcertification_id and cert_name.locale_id=xxlocaleid) cert_name,
NVL (cert.id, (xcertification_id)) cert_id,
cert.ACQUIRED_ON,
cert.target_date,
cert.status_desc,
cert.stud_status cert_status,
'Y' checkcert
FROM
cmt_person per
left outer join tpt_company org on per.company_id=org.id
left outer join pfv_pub_org_info orginfo on org.id =orginfo.node_id
left outer join cmt_person mgr on per.manager_id=mgr.id
left outer JOIN
(
SELECT studcert.owner_id owner_id,
TO_CHAR (studcert.status) stud_status,
l.description status_desc,
studcert.target_date,
STUDcert.ACQUIRED_ON,
cert.name,
cert.id,
cert.locale_id
FROM
tpt_ce_stud_certification studcert
INNER JOIN (select * from tpt_ext_ce_certification where type=0) cert
INNER JOIN tpt_id_ce_certification i ON cert.ID = i.ID
LEFT OUTER JOIN (select * from tpt_ext_ce_certification where type=0) p
ON cert.parent_id = p.ID AND cert.locale_id = p.locale_id
INNER JOIN fgt_detail det ON cert.description = det.ID ON i.ID = studcert.certification_id
INNER JOIN fgt_ext_sys_list_of_val l ON l.name = studcert.status AND l.list_id = 'sysli000000000000158' and l.locale_id=xxlocaleid
WHERE
cert.id =certification_id
and TRUNC(studcert.assigned_on) >= TRUNC(ReportDate)
--and studcert.status=statustype
) cert ON cert.owner_id = per.id and cert.locale_id=xxlocaleid
--inner JOIN fgt_domain domain ON per.split = domain.id
WHERE
(orginfo.related_to=organization_id or(org.id=organization_id and orginfo.relation_type='I'))and
((per.terminated_on IS NOT NULL AND per.terminated_on > SYSDATE) OR (per.terminated_on IS NULL));-- and
--and DECODE (cert.owner_id, NULL, '300','200') IN (xxstatus_type);

ELSE

OPEN subordinate_status_resultset FOR
SELECT
per.id per_id,
per.fname per_fname,
per.username per_username,
per.lname per_lname,
mgr.id mgr_id,
mgr.fname mgr_fname,
mgr.lname mgr_lname,
mgr.username mgr_username,
org.id org_id,
org.name2 org_name,
org.parent_id org_parent_id,
cert.owner_id owner_id,
cert.name cert_name,
cert.id cert_id,
cert.ACQUIRED_ON,
cert.target_date,
cert.status_desc,
cert.stud_status cert_status,
'N'
FROM
cmt_person per
inner join tpt_company org on per.company_id=org.id
left outer join pfv_pub_org_info orginfo on org.id =orginfo.node_id
left outer join cmt_person mgr on per.manager_id=mgr.id
left outer JOIN
(
SELECT studcert.owner_id owner_id,
TO_CHAR (studcert.status) stud_status,
l.description status_desc,
studcert.target_date,
STUDcert.ACQUIRED_ON,
STUDCERT.ASSIGNED_ON,
cert.name,
cert.id,
cert.locale_id
FROM
tpt_ce_stud_certification studcert
INNER JOIN (select * from tpt_ext_ce_certification where type=0) cert
INNER JOIN tpt_id_ce_certification i ON cert.ID = i.ID
LEFT OUTER JOIN (select * from tpt_ext_ce_certification where type=0) p
ON cert.parent_id = p.ID AND cert.locale_id = p.locale_id
INNER JOIN fgt_detail det ON cert.description = det.ID ON i.ID = studcert.certification_id
INNER JOIN fgt_ext_sys_list_of_val l ON l.name = studcert.status AND l.list_id = 'sysli000000000000158' and l.locale_id=xxlocaleid
WHERE
Trunc(studcert.acquired_on) <= Trunc(ReportDate)
--and studcert.status=statustype
) cert ON cert.owner_id = per.id and cert.locale_id=xxlocaleid
-- inner JOIN fgt_domain domain ON per.split = domain.id
WHERE
1=0 and --or (org.id=organization_id and orginfo.relation_type='I')) and
((per.terminated_on IS NOT NULL AND per.terminated_on > SYSDATE) OR (per.terminated_on IS NULL))--and
and DECODE (cert.owner_id, NULL, '300','200') IN (statustype);
END IF;
END SCP_RPALLSUBORDOFORG_main_test;
/

Report message to a moderator

Re: ORA-22813: operand value exceeds system limits [message #474157 is a reply to message #474155] Fri, 03 September 2010 04:48 Go to previous messageGo to next message
cookiemonster
Messages: 7480
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well oracle will have given you the line number the error happened at unless you've got exception handlers that mask it.
So you need to debug the code. Try using dbms_output to the determine the size of the string you are assigning to that variable.

Report message to a moderator

Re: ORA-22813: operand value exceeds system limits [message #474160 is a reply to message #474157] Fri, 03 September 2010 04:58 Go to previous messageGo to next message
cookiemonster
Messages: 7480
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Can you please read the orafaq forum guide and follow it in future - espically the part about formatting your posts.
2) What's the datatype of cert.cert_id?
3) Why are you using an object at all? Just combine the two cursors into a single query and base the ref cursor on that.

Report message to a moderator

Re: ORA-22813: operand value exceeds system limits [message #474161 is a reply to message #474160] Fri, 03 September 2010 05:00 Go to previous messageGo to next message
aarti323
Messages: 7
Registered: September 2010
Location: Pune
Junior Member
datatype of cert.cert_id is varchar2

Report message to a moderator

Re: ORA-22813: operand value exceeds system limits [message #474162 is a reply to message #474161] Fri, 03 September 2010 05:01 Go to previous messageGo to next message
aarti323
Messages: 7
Registered: September 2010
Location: Pune
Junior Member
i have to display the hierarchy of organization in the report of particular certification so that i m using object

Report message to a moderator

Re: ORA-22813: operand value exceeds system limits [message #474173 is a reply to message #474162] Fri, 03 September 2010 06:51 Go to previous message
cookiemonster
Messages: 7480
Registered: September 2008
Location: Rainy Manchester
Senior Member
Still don't see why that requires an object.

Report message to a moderator

Previous Topic:ORA-01400:cannot insert NULL into columns
Next Topic:Exception handling
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Feb 04 01:37:08 CST 2012

Total time taken to generate the page: 8.80390 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.