Ignore or exclude an oracle sql error?

[Gareth Keenan]

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

[Incredible Hulctuary]

What tool(s) are you using to import/export from Oracle?

[Incredible Hulctuary]

My guess is some of the rows have XML which is too long for your purposes.

[Gareth Keenan]

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.

[Incredible Hulctuary]

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.

CREATE OR REPLACE FUNCTION xtractval( ..... ) RETURN VARCHAR2 AS
BEGIN
  RETURN EXTRACTVALUE(......);
EXCEPTION
 WHEN VALUE_ERROR THEN
   RETURN '**error**';
END;

[Knoath]

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.