2

I am following a course and I got this script below that I should execute:

ORGANIZATION EXTERNAL (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY extdir
      ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
      SKIP 1
      BADFILE extdir:'LOAD_CANDYBAR_HISTORICAL_DATA.bad'
      LOGFILE extdir:'LOAD_CANDYBAR_HISTORICAL_DATA.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY '"'
      MISSING FIELD VALUES ARE NULL
      (
                RESPONDENT_ID,
                RESPONDENT_NAME,
                RESPONDENT_ADDR,
                RESPONDENT_CITY,
                RESPONDENT_STATE,
                RESPONDENT_ZIPCODE,
                RESPONDENT_PHONE_NUM,
                RESPONDENT_GENDER,
                RESPONDENT_DOB DATE(10) "YYYY-MM-DD",
                CANDYBAR_ID,
                CANDYBAR_NAME,
                CANDYBAR_MFR_ID,
                CANDYBAR_MFR_NAME,
                CANDYBAR_WEIGHT_OZ,
                SURVEY_DATE DATE(10) "YYYY-MM-DD",
                SURVEY_YEAR,
                TASTE_RATING,
                APPEARANCE_RATING,
                TEXTURE_RATING,
                OVERALL_RATING,
                LIKELIHOOD_PURCHASE,
                NBR_BARS_CONSUMED
      )
     )
     LOCATION ('CANDYBAR_HISTORICAL_DATA_2004.csv',
            'CANDYBAR_HISTORICAL_DATA_2005.csv',
            'CANDYBAR_HISTORICAL_DATA_2006.csv',
               'CANDYBAR_HISTORICAL_DATA_2007.csv',
               'CANDYBAR_HISTORICAL_DATA_2008.csv',
               'CANDYBAR_HISTORICAL_DATA_2009.csv',
               'CANDYBAR_HISTORICAL_DATA_2010.csv',
               'CANDYBAR_HISTORICAL_DATA_2011.csv',
               'CANDYBAR_HISTORICAL_DATA_2012.csv',
               'CANDYBAR_HISTORICAL_DATA_2013.csv')
    );

In addition, I've added:

CREATE OR REPLACE DIRECTORY extdir AS '/Users/path_to/CANDYBAR_HISTORICAL_DATA/';

and inside of my CANDYBAR_HISTORICAL_DATA are all the csv.

Moreover I am having the following error:

ORA-00928: missing SELECT keyword
00928. 00000 -  "missing SELECT keyword"
*Cause:    
*Action:
Error at Line: 15 Column: 4

and I don't really understand why :(

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Catarina Nogueira
  • 1,024
  • 2
  • 12
  • 28
  • 2
    Organization external is part of `create table` statement. It doesn't make any sense as a standalone text (*what* is organized externally?) and doesn't present in the documentation as a standalone statement – astentx May 15 '22 at 17:20
  • 2
    The only way I can think you could get error is if you did `CREATE TABLE your_table () AS ` - the stray `AS` would make it expect `SELECT`. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=0881c02784165d50d4a82bd23689b350) with your code, a `CREATE` with `AS`, and one without. It would help if you included the entire statement in your question; but it looks like a typo. – Alex Poole May 15 '22 at 18:22
  • got it! I assume the script was missing what you said then. I've added CREATE TABLE (...) and the error disappeared, thank you! Moreover now I am having: "file CANDYBAR_HISTORICAL_DATA_2004.csv in EXTDIR not found" but I am sure the path I gave to exdir is right... Trying to understand and when I manage I will post a complete answer :) – Catarina Nogueira May 16 '22 at 20:18

0 Answers0