Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Naga.

Asked: March 14, 2020 - 11:55 am UTC

Last updated: March 17, 2020 - 10:16 am UTC

Version: 11

Viewed 1000+ times

You Asked

Hai,

I need solution from you, I have a table with 7 lack records and when i re-create a table with CTAS, the rows upto 4k inserted and after that it is throwing a below error.

The error was...ORA-01841: (full) year must be between-4713 and 9999 and not 0....am not finding the right solution even i tried to with change date format to TO_DATE(TO_CHAR(...), But still it is throwing an error...i got other idea, like..will proceed with BULK with SAVE EXCEPTIONS after whatever error records stores then will re-create like...i need to try this, Is this will work out ? or can you give any solution...

In my 120 lines of SQL code we used for date converter to the US format with substr(timeconverter) .

Can i get a sol from you. please provide the possible way solutions.

and Connor said...

Check out DML error logging, which will let you load all the good rows, and capture all the bad rows in an error table.

Here is a video to walk you through it


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Done

A reader, March 16, 2020 - 12:10 pm UTC

Hi,

I have tried the above way also, but am getting through an error even...can u do with here a sample example.
Chris Saxon
March 17, 2020 - 10:15 am UTC

Can you provide a simple example showing exactly what you're doing and the error you're getting?

Did you investigate the rows in the error table?

A reader, March 17, 2020 - 5:58 am UTC 


Clearly the rows that you want to load are not in the correct format. Check some rows or do a select distinct on the column in the question.

That should help in troubleshooting

Cheers!

Use VALIDATE_CONVERSION in the Source Table

Arnab, March 17, 2020 - 7:50 am UTC

If you are using Oracle 12c and above (not sure which Oracle version you are using ) then you can take help of the function VALIDATE_CONVERSION to filter out only those records which can be converted to a date and then load those records into the target table. Also the ones which are invalid can also be identified using this same function and processed according to the error handling strategy as per your wish.

VALIDATE_CONVERSION Link:- https://livesql.oracle.com/apex/livesql/file/tutorial_EDVE861IMHO1W3Q654ES9EQQW.html
Chris Saxon
March 17, 2020 - 10:16 am UTC

They say 11g... but good idea for those on 12.2 or higher.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library