Home » SQL & PL/SQL » SQL & PL/SQL » error in sql script
error in sql script [message #190391] Wed, 30 August 2006 08:21 Go to next message
Messages: 282
Registered: March 2005
Senior Member

All, I have the following script (database is 10gr1)

tac.id campaign_id,
u.id user_id,
upper (aua.val) company,
upper (aua2.val) city,
UPPER (aua3.val) country,
amt.description media_type,
acat.name category,
(SELECT stragg (extractValue (XMLTYPE (tbl_award_entries.xml_data), '/award_credits/credit[@id = 28853505 or @id = 28853499 or @id = 28281753 or @id = 29736107 or @id = 29736108 or @id = 29736109 or @id = 28853500 or @id = 28853501 or @id = 28853502 or @id = 28853503 or @id = 28853504 or @id = 29736104 or @id = 29736105 or @id = 29736106]')) from tbl_award_entries where tbl_award_entries.campaignid = tac.id) as entry_title
INNER JOIN tbl_award_campaign tac ON tac.userid = u.id AND tac.awardid = 28261764
INNER JOIN aw_year_usraddress_val aua ON u.id = aua.userid AND aua.aw_year_usraddress_def_id = (SELECT aud.id FROM aw_year_usraddress_deF aud WHERE aud.aw_year_id = 28261764 AND aud.ref_name = 'company')
INNER JOIN aw_year_usraddress_val aua2 ON u.id = aua2.userid AND aua2.aw_year_usraddress_def_id = (SELECT aud2.id FROM aw_year_usraddress_def aud2 WHERE aud2.aw_year_id = 28261764 AND aud2.ref_name = 'city')
INNER JOIN aw_year_usraddress_val aua3 ON u.id = aua3.userid AND aua3.aw_year_usraddress_def_id = (SELECT aud3.id FROM aw_year_usraddress_def aud3 WHERE aud3.aw_year_id = 28261764 AND aud3.ref_name = 'country')
INNER JOIN tbl_award_entries tae ON tae.campaignid = tac.id
INNER JOIN archive_metadata am ON tae.fileid = am.id AND campaignseq = 1
INNER JOIN aw_media_type amt ON tac.aw_media_type_id = amt.id
INNER JOIN aw_category acat ON tac.award_category_id = acat.id
tae.campaignseq = 1 AND
tac.withdrawn = 'f' AND
tac.deleted = 'f' AND LOWER (aua.val) LIKE '%click%' ORDER BY company ASC, city ASC, acat.description ASC, tac.entrynumber ASC

the script gives the following error

Warning: ocifetchstatement() [function.ocifetchstatement]: OCIFetchStatement: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYS.XMLTYPE", line 248 ORA-06512: at line 1

As far as I can work out, the error is caused by the xml_data field either not having the relevant piece of XML or having a null value in that column. Is there any way that I can suppress this error or is there another way to perform what I am trying to achieve that will not trigger the error in the first place?


Re: error in sql script [message #191312 is a reply to message #190391] Tue, 05 September 2006 11:48 Go to previous message
Messages: 173
Registered: January 2006
Senior Member
What is the datatype of column tbl_award_entries.xml_data? If it is XMLTYPE then remove the cast XMLTYPE(), else verify that the data can be converted successfully to well-formed XML.

How many records is the extractValue expected to match? From the query it seems more than one - if so, that would cause a problem as extractValue return a single node only. It would be better to replace this with extract(<data>, <xpath expression/text()>).

I also hope that the hardcoded ids are not intended for production-ready code!
Previous Topic: Reg: Table Space error...
Next Topic: How to write the records in the file using UTL_FILE package
Goto Forum:

Current Time: Tue Jan 24 17:19:27 CST 2017

Total time taken to generate the page: 0.08277 seconds