oracle select statement returns “no rows selected”

Question

I created an external table using a .tbl file like this:

CREATE TABLE orders_load  ( O_ORDERKEY       INTEGER ,
                           O_CUSTKEY        INTEGER ,
                           O_ORDERSTATUS    CHAR(1) ,
                           O_TOTALPRICE     DECIMAL(15,2),
                           O_ORDERDATE      DATE,
                           O_ORDERPRIORITY  CHAR(15),  
                           O_CLERK          CHAR(15), 
                           O_SHIPPRIORITY   INTEGER,
                           O_COMMENT        VARCHAR(79))
organization external
(default directory user_directory
access parameters
(fields terminated by '|')
location('orders.tbl')
);

Then, to select all rows from that table, I used:

select * from users_load;

But I got this error:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached

Then with this command below, the error above seems that is fixed:

alter table users_load reject limit unlimited;

Now, when I execute this command

select * from users_load;

I don't get any error, but the result I get is this: "no rows selected".

(the orders.tbl file has more than 10.000.000 rows)

The format of each row is:

34155110|847435|O|190135.67|1998-04-21|4-NOT SPECIFIED|Clerk#000005134|0|posits wake quickly according to the car|

In the log file it appear this: ORA-01861 Literal does not match format string for column O_ORDERDATE.

Cross post: http://dba.stackexchange.com/q/131361/1822 – Mar 05 '16 at 12:30 — , Mar 05 '16 at 12:30

score 3 · Accepted Answer · 2016-03-05T16:15:09.553

You need to specify the format of the DATE column.

Something like this (can't test it right now)

CREATE TABLE orders_load  ( O_ORDERKEY       INTEGER ,
                           O_CUSTKEY        INTEGER ,
                           O_ORDERSTATUS    CHAR(1) ,
                           O_TOTALPRICE     DECIMAL(15,2),
                           O_ORDERDATE      DATE,
                           O_ORDERPRIORITY  CHAR(15),  
                           O_CLERK          CHAR(15), 
                           O_SHIPPRIORITY   INTEGER,
                           O_COMMENT        VARCHAR(79))
organization external 
(
  default directory user_directory
  access parameters
  (
    fields terminated by '|'
    (
       O_ORDERKEY,
       O_CUSTKEY,
       O_ORDERSTATUS,
       O_TOTALPRICE,
       O_ORDERDATE      DATE "YYYY-MM-DD",  -- this needs to be in the format of your input file
       O_ORDERPRIORITY,  
       O_CLERK, 
       O_SHIPPRIORITY,
       O_COMMENT
    )   
  )
  location('orders.tbl')
);

In my experience it's only necessary to define the format for DATE columns, for all others (especially numbers) Oracle will figure it out without problems.

@a_horse_with_no_name - you might want to point out that the specific use of the ansi DATE function you presented only works if the format mask used actually matches the format of the data in the file. It might seem obvious, but I've seen a lot of people on OTN that miss that. — EdStevens, Mar 05 '16 at 14:13

oracle select statement returns “no rows selected”

1 Answers1