0

I've created an external table using the following definition

    CREATE TABLE EXT_TABLE (CID NUMBER, CNAME VARCHAR2(20), FEES NUMBER)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY TEST_DIR
ACCESS PARAMETERS
    (RECORDS DELIMITED BY NEWLINE
     FIELDS TERMINATED BY ','
     (
     CID INTEGER,
     CNAME CHAR(20),
     FEES INTEGER
     )
     )
     LOCATION ('DATA.TXT'))
     REJECT LIMIT UNLIMITED;

table has been created. But, when i try to select data from table, I don't find any records

  SQL> select * from ext_table;

no rows selected

I've made sure directory and table have sufficient privileges for the user. The data in text file;

1,JAVA,300
2,LINUX,400
3,ORACLE,400
4,EXCEL,500
Jey.Guru
  • 1
  • 5
  • Though the error code and messages are ambiguous with few other questions, I am unable to get the error fixed based on answers from them. – Jey.Guru Feb 03 '20 at 17:12

1 Answers1

0

RECORD is not the keyword you should use here.

it must be RECORDS.

Use this:

RECORDS DELIMITED BY NEWLINE

Cheers!!

Popeye
  • 35,427
  • 4
  • 10
  • 31
  • Hi @Tejash, I tried that, it gave a different error saying "number" was a bad identifier, so I changed the number to integer, then it said reject limit reached, so I went ahead and added reject limit unlimited. Now table is created and when i select data, it says no rows selected – Jey.Guru Feb 03 '20 at 19:59
  • modified question accordingly – Jey.Guru Feb 03 '20 at 20:09