#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    1
    Rep Power
    0

    Create External Table


    Hi, im trying to create a external table with a file but i cant make it work, the error i get is ORA-00933: SQL command not properly ended

    i dont know whats grown because i dont get any other error, hope some one can tell me what im doing wrong thanks
    here is my sentences

    Code:
    CONNECT  /  AS SYSDBA
    -- Set up directories and grant access to hr 
    CREATE OR REPLACE DIRECTORY admin_dat_dir
        AS '/flatfiles/data'; 
    CREATE OR REPLACE DIRECTORY admin_log_dir 
        AS '/flatfiles/log'; 
    CREATE OR REPLACE DIRECTORY admin_bad_dir 
        AS '/flatfiles/bad'; 
    GRANT READ ON DIRECTORY admin_dat_dir TO FASHION 
    GRANT WRITE ON DIRECTORY admin_log_dir TO FASHION 
    GRANT WRITE ON DIRECTORY admin_bad_dir TO FASHION
    -- hr connects. Provide the user password (hr) when prompted.
    CONNECT FASHION
    -- create the external table
    CREATE TABLE EXTERNA (
      DEPARTAMENTO_ZONA varchar2(30),
      REGION_DESCRIPCION varchar2(30),
      DEPARTAMENTO_DESCRIPCION VARCHAR2(30),
      SUCURSAL_DIRECCION VARCHAR2(60),
      CLIENTE_NOMBRE VARCHAR2(30),
      CLIENTE_APELLIDO VARCHAR2(30),
      CLIENTE_DIRECCION VARCHAR2(60),
      CLIENTE_TELEFONO INT,
      CLIENTE_TIPO VARCHAR2(60),
      EMPLEADO_NOMBRE VARCHAR2(30),
      EMPLEADO_DIRECCION VARCHAR2(60),
      EMPLEADO_TELEFONO INT,
      EMPLEADO_TIPO VARCHAR2(30),
      VENTA_TIPO_PAGO VARCHAR2(60),
      VENTA_TIPO_VENTA VARCHAR2(60),
      VENTA_IVA NUMBER(9,4), 
      VENTA_FECHA  DATE,
      PRODUCTO_NOMBRE VARCHAR2(50),
      PRODUCTO_MARCA VARCHAR2(30), 
      PRODUCTO_COLOR VARCHAR2(30),
      PRODUCTO_TALLA VARCHAR2(10),
      PRODUCTO_PRECIO NUMBER(19,4), 
      CATEGORIA_NOMBRE VARCHAR2(50),
      CATEGORIA_GENERO VARCHAR2(50)
    )
    ORGANIZATION EXTERNAL
    (
    
       TYPE ORACLE_LOADER 
           DEFAULT DIRECTORY admin_dat_dir 
           ACCESS PARAMETERS 
           (
           
      records delimited by newline
      badfile admin_bad_dir:'empxt%a_%p.bad' 
      logfile admin_log_dir:'empxt%a_%p.log'
      fields terminated by ',' 
      missing field values are null
      ( DEPARTAMENTO_ZONA, REGION_DESCRIPCION, DEPARTAMENTO_DESCRIPCION, SUCURSAL_DIRECCION, CLIENTE_NOMBRE, CLIENTE_APELLIDO, CLIENTE_DIRECCION, 
        CLIENTE_TELEFONO, CLIENTE_TIPO, EMPLEADO_NOMBRE, EMPLEADO_DIRECCION, EMPLEADO_TELEFONO, EMPLEADO_TIPO, VENTA_TIPO_PAGO, VENTA_TIPO_VENTA, 
        VENTA_IVA, VENTA_FECHA, PRODUCTO_NOMBRE, PRODUCTO_MARCA, PRODUCTO_COLOR, PRODUCTO_TALLA, PRODUCTO_PRECIO, CATEGORIA_NOMBRE, CATEGORIA_GENERO
      )
    )
    LOCATION ('C:\Users\Lester\Documents\datos.dat')
    )
    ;
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388
    Originally Posted by lestermatrix
    Hi, im trying to create a external table with a file but i cant make it work, the error i get is ORA-00933: SQL command not properly ended

    i don't know whats grown . . . E t c . . .
    Mold?

    1) Do not leave empty (blank) lines within your code.

    2) You have incorrect syntax for the "LOCATION()" clause.


IMN logo majestic logo threadwatch logo seochat tools logo