![]() |
|
|
FlashChat | Actuarial Discussion | Preliminary Exams | CAS/SOA Exams | Cyberchat | Around the World | Suggestions |
Salary Surveys |
Health Actuary Jobs |
Actuarial Recruitment |
Casualty Jobs |
![]() |
|
Thread Tools | Search this Thread | Display Modes |
#1
|
||||
|
||||
![]() 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 |
#2
|
||||
|
||||
![]() What tool(s) are you using to import/export from Oracle?
__________________
The forest was shrinking, but the trees kept voting for the axe because its handle was made from wood, and they thought it was one of them. |
#3
|
||||
|
||||
![]() My guess is some of the rows have XML which is too long for your purposes.
__________________
The forest was shrinking, but the trees kept voting for the axe because its handle was made from wood, and they thought it was one of them. |
#4
|
||||
|
||||
![]() 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. |
#5
|
||||
|
||||
![]() 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;
__________________
The forest was shrinking, but the trees kept voting for the axe because its handle was made from wood, and they thought it was one of them. |
#6
|
|||
|
|||
![]() 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. |
![]() |
Thread Tools | Search this Thread |
Display Modes | |
|
|