Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Naga.

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

Answered by: Connor McDonald - Last updated: March 17, 2020 - 10:16 am UTC

Category: PL/SQL - Version: 11

Viewed 100+ 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 we 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


and you rated our response

  (3 ratings)

Reviews

Done

March 16, 2020 - 12:10 pm UTC

Reviewer: A reader

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

Followup  

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?

March 17, 2020 - 5:58 am UTC

Reviewer: A reader


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

March 17, 2020 - 7:50 am UTC

Reviewer: Arnab from Kolkata, India

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

Followup  

March 17, 2020 - 10:16 am UTC

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

More to Explore

PL/SQL

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