running in to ORA-03113: end-of-file on communication channel error when trying to select from the external table

GP7
GP7 used Ask the Experts™
on
Hi

I am running into the following error while trying to do a select on the external table..

SQL> select CORP_PRODUCT_DW_ID from ext_test_lu_corp_product;
select CORP_PRODUCT_DW_ID from ext_test_lu_corp_product
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8380
Session ID: 496 Serial number: 191

Please help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
The bad news is an ORA-03113 is next to impossible to troubleshoot here.  That is one of Oracle's favorite generic errors.

Check your alert log for errors.  Also look in the diag directory for any new incidents.
HainKurtSr. System Analyst

Commented:
is it a loooooooooooooooooooong time consuming query?
GP7

Author

Commented:
Hi,

I figured out what was causing the error. i was actually loading data from flat files  into my external table. The table is being created, but, i am running in to this error when I select from that external table. (The table just has about 900 rows... so it is not even a long running query.)

The reason it was throwing this error was, I had a TIMESTAMP column. The used the format 'YYYY-MM-DD HH24:MI:SS.FF' (basically this was the format used in original flat file)

I set my nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF'
and nls_data_format='YYYY-MM-DD HH24:MI:SS'

but in the external table i was using
DW_ADD_DATE DATE 'YYYY-MM-DD HH24:MI:SS.FF'  while specifying the field DW_ADD_DATE

There could be 2 reasons
1. 'YYYY-MM-DD HH24:MI:SS.FF' format is incompatible with DATE
2. Something wrong with the nls_timestamp_format

Finally I just took out the .FF in the DW_ADD_DATE DATE 'YYYY-MM-DD HH24:MI:SS.FF' and it worked fine.

I still need to figure out
1. How setting of the nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF' and nls_data_format='YYYY-MM-DD HH24:MI:SS' work at the session and system level
2. what is the purpose/effect of setting those values when we are setting them again at the field level when i already set them at the session level.

I'll be very helpful if somebody can elucidate.

Thanks
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>1. 'YYYY-MM-DD HH24:MI:SS.FF' format is incompatible with DATE
Correct.  Fractional seconds are only available with timestamp data types.

>>what is the purpose/effect of setting those values

The format in the external table tells Oracle what the text data looks like.  Oracle still needs to convert it to a date internally.  Without using TO_DATE, Oracle looks to the nls_date_format for the formatting.

You can probably ignore the nls_date_format by adding the to_date conversion in the external table definition.  The following is for sqlLoader but should still work:

http://forums.oracle.com/forums/thread.jspa?threadID=170992
Top Expert 2008

Commented:
Using SUBSTRING function and concatenation it is possible
to truncate the FF part

TO_DATE(SUBSTR ('YYYY-MM-DD HH24:MI:SS.FF' ,1,21) || '''')
GP7

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial