Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

DW Simpson Global Actuarial & Analytcs Recruitment
Download our 2016 Actuarial Salary Survey
now with state-by-state salary information!


Reply
 
Thread Tools Display Modes
  #1  
Old 10-02-2017, 12:53 PM
Gareth Keenan's Avatar
Gareth Keenan Gareth Keenan is offline
Member
CAS
 
Join Date: Feb 2006
Location: by a pretty big bay
Studying for #9
Favorite beer: butter
Posts: 1,280
Default Ignore or exclude an oracle sql error?

Greetings!

I've run into an annoying situation. I can pull some data successfully in Oracle, as in i can see the top 50 rows or whatever, so my code does not error out and the code produces the results i want, so i feel like i'm doing something right.

Until i go to export. I'll right click-> save as a csv, and the export actually begins to run successfully for a few minutes. Until that is

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
06502.00000 - "PL/SQL: numeric or value error%s"

Is there anyway to have Oracle ignore certain errors? Or if there is a particular record causing an error to exclude it from the data set? Like, if only 5 out of my i dunno, 10k records is triggering the error, then i can get by with losing a few records for my dataset.

Thanks!

Gareth Keenan
Reply With Quote
  #2  
Old 10-02-2017, 05:41 PM
Incredible Hulctuary's Avatar
Incredible Hulctuary Incredible Hulctuary is offline
Member
Non-Actuary
 
Join Date: Jan 2002
Posts: 23,656
Default

What tool(s) are you using to import/export from Oracle?
__________________
A lot of people are afraid of heights; not me, I'm afraid of widths. - Steven Wright
Reply With Quote
  #3  
Old 10-02-2017, 05:53 PM
Incredible Hulctuary's Avatar
Incredible Hulctuary Incredible Hulctuary is offline
Member
Non-Actuary
 
Join Date: Jan 2002
Posts: 23,656
Default

My guess is some of the rows have XML which is too long for your purposes.
__________________
A lot of people are afraid of heights; not me, I'm afraid of widths. - Steven Wright
Reply With Quote
  #4  
Old 10-02-2017, 06:24 PM
Gareth Keenan's Avatar
Gareth Keenan Gareth Keenan is offline
Member
CAS
 
Join Date: Feb 2006
Location: by a pretty big bay
Studying for #9
Favorite beer: butter
Posts: 1,280
Default

I'm using Oracle Sql Developer to connect to data and pull data.

To export i don't think i'm using any fancy tools, i just right click on the data and select export. Then specify delimited by comma and choose the file name and pathway i'd like to export to.

It's true that some of the underlying data is of type clob but i'm using functions which pull from this field. The function is like EXTRACTVALUE(xmltype(a.data_field_name...

So it may be there is some issue with the data type, but it would be a completely impractical use of my time to go through every single record, that's why I'm trying to just exclude the records causing an error.

Because if there should be, say, 10k of records, and after excluding the errored records i then have 9,9980 records i count that as acceptable and move on with the work. But i can't know what that second # is until after i can get a count of only clean records.
Reply With Quote
  #5  
Old 10-02-2017, 07:27 PM
Incredible Hulctuary's Avatar
Incredible Hulctuary Incredible Hulctuary is offline
Member
Non-Actuary
 
Join Date: Jan 2002
Posts: 23,656
Default

Write a PL/SQL stored function that does the EXTRACTVALUE logic for you and traps the VALUE_ERROR in an exception block. Then use that function in your query instead of directly using EXTRACTVALUE. After the results are exported you can count how many rows have the error string.

Code:
CREATE OR REPLACE FUNCTION xtractval( ..... ) RETURN VARCHAR2 AS
BEGIN
  RETURN EXTRACTVALUE(......);
EXCEPTION
 WHEN VALUE_ERROR THEN
   RETURN '**error**';
END;
__________________
A lot of people are afraid of heights; not me, I'm afraid of widths. - Steven Wright
Reply With Quote
  #6  
Old 10-03-2017, 02:16 PM
Knoath Knoath is offline
CAS
 
Join Date: Oct 2015
Posts: 19
Default

As a quick workaround, if it's only a handful of rows that are failing because a field value starts with an alpha instead of numeric - you could add an extra clause to your SQL to exclude the rows that have that value starting with an alpha before trying to export. Alternatively, in the SQL you could sort by that column so that the alpha appears first. I'm guessing that the export is selecting the datatype by looking at the first row/s so by having the first row with an alpha will make it default to alphanumeric.
The same would apply if a later row had more decimal points, number of digits etc. than the first.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 10:58 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.15363 seconds with 11 queries