I have an external Oracle database. I'm trying to export table into Oracle APEX database. The problem is that the timestamp format from external database is like this 21/01/17 23:34:50,000000000 (note , just after 50), while Oracle APEX allows only '23:34:50.000000000' otherwise it throws error:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "SYS.DBMS_SQL", line 1721.
Insert into XX_COMPANY (ID,ACCESS_CHOSEN,ADDRESS,CAPTION,CONTACT_PERSON,DELETE_DATE,EMAIL,NAME,NIP,PHONE,SAP_ID,UNIT,UPDATE_DATE) values ('210','1','ul. DXXXX 1 43-200 PXXXX','PS',null,null,null,'PSM','6380005291',null,'205278','NT02,NT07,NT55,NT66,NT87,NT88',to_timestamp('21/01/17 23:34:50,000000000','RR/MM/DD HH24:MI:SSXFF'));
which includes the conversion causing the error:
to_timestamp('21/01/17 23:34:50,000000000','RR/MM/DD HH24:MI:SSXFF')
The file consists of few thousands of INSERT statements. I'd rather not correct all of them by hand. Is there a way to specify to_timestamp() argument so that it will accept , instead of .?
Your format model includes the X in SSXFF, which is the local radix character.
You can override your session setting by modifying each to_timestamp() call with the optional third argument:
to_timestamp('21/01/17 23:34:50,000000000','RR/MM/DD HH24:MI:SSXFF', 'NLS_NUMERIC_CHARACTERS=,.'
Or you can change your session settings so you don't have to modify them individually:
alter session set NLS_NUMERIC_CHARACTERS=',.'
Remember to change it back afterwards if you need to query data with your usual settings in the same session. Notice the output in the last query in that fiddle also shows the comma as the radix.
Alternatively, change SSXFF to SS,FF, which would be a simple replace operation in your file. Or redo the 'export' with the session settings changed so the values are generated with a period not a comma.
If you're transferring data between databases there are other, usually better, ways than generating lots of insert statement and then executing them all. I'd suggest you look into Oracle data pump.