This discussion is archived
1 Reply Latest reply: Nov 21, 2012 3:50 AM by Joan Puig RSS

No more data to read from socket

Joan Puig Newbie
Currently Being Moderated
Hi experts,

I'm getting a *"No more data to read from socket"* exception when selecting from an external table I've just created. I think I've located the issue, but the solution should be avoided at any cost. Let me explain:

To replicate the results:

FILES

test1.csv:*
13,20110530,"2011-06-08-20.27.28.000000"




createExtTable01.sql:*
CREATE OR REPLACE DIRECTORY foo_data as '/home/foo/bar/Data';

CREATE TABLE "FOO"."EXT_TABLE01" (
"SOME_NUMBER" INTEGER,
"SOME_DATE" DATE,
          "SOME_TIMESTAMP" DATE)
organization external
(
TYPE ORACLE_LOADER
default directory foo_data
access parameters
( fields terminated by ','
(
          "SOME_NUMBER",
"SOME_DATE" date 'yyyymmdd',
          "SOME_TIMESTAMP" CHAR(30) date_format date mask "yyyy-mm-dd-hh24.mi.ss.ff"
)
)
location ('test1.csv')
)
PARALLEL;

select * from ext_table01;

drop table ext_table01 purge;

quit;




createExtTable02.sql:*
CREATE OR REPLACE DIRECTORY foo_data as '/home/foo/bar/Data';

CREATE TABLE "foo"."EXT_TABLE02" (
"SOME_NUMBER" INTEGER,
"SOME_DATE" DATE )
organization external
(
TYPE ORACLE_LOADER
default directory foo_data
access parameters
( fields terminated by ','
(
          "SOME_NUMBER",
"SOME_DATE" date 'yyyymmdd'
)
)
location ('test1.csv')
)
PARALLEL;


select * from ext_table02;

drop table ext_table02 purge;

quit;

--------

EXECUTION

$ sqlplus -s foo/bar@foobar @createExtTable02

Directory created.


Table created.


SOME_NUMBER SOME_DATE
---- ----
13 30-MAY-11


Table dropped.



$ sqlplus -s foo/bar@foobar @createExtTable01

Directory created.


Table created.

select * from ext_table01
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 18042
Session ID: 203 Serial number: 41103


ERROR:
ORA-03114: not connected to ORACLE

$

--------

My best guess is that the external table doesn't like the double quotes around the timestamp in the csv file. I've tried surrounding the format with single quotes:
'"SOME_TIMESTAMP" CHAR(30) date_format date mask '"yyyy-mm-dd-hh24.mi.ss.ff"'
In this case the query ends, but it gives an error saying that the literal doesn't match the format.

The easy solution might be removing the double quotation marks from the input data set, but I don't want to do that since it's the data I received from our client.

Any ideas?

Joan

P.S.: I'm using 11.2.0.3 with the latest PSU (JUL 2012?)

EDIT:
I've tried to remove the double quotation marks and I'm getting the same error. It must be something else, still related to the timestamp.

EDIT2:
I've removed the decimal part after the seconds and now it works properly, but I think this not an acceptable solution for the customer.

Edited by: JPuig on Nov 20, 2012 5:45 AM

Edited by: JPuig on Nov 20, 2012 5:50 AM
  • 1. Re: No more data to read from socket
    Joan Puig Newbie
    Currently Being Moderated
    Hi experts,

    Good news: I solved the problem myself by adding "records delimited by newline" and "optionally enclosed by '"' " (especially the second).

    While the problem is solved and the question answered, I don't think that a session ending without any error or warning should be considered "expected behavior".

    Greetings,

    Joan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points