external file

I was trying to create an external file..
goal is to create a csv file with datas of emp file out side..
I have empreport.csv in this path --C:\oracle_ext\mydata----blank
This earlier I created newdir
using create or replace directory .......

This is the errror message I am getting.....

SQL> conn scott/tiger
Connected.
SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM      
---------- ---------- --------- ---------- --------- ---------- ----------      
    DEPTNO                                                                      
----------                                                                      
      7369 SMITH      CLERK           7902 17-DEC-80        800                
        20                                                                      
                                                                               
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300      
        30                                                                      
                                                                               
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500      
        30                                                                      
                                                                               

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM      
---------- ---------- --------- ---------- --------- ---------- ----------      
    DEPTNO                                                                      
----------                                                                      
      7566 JONES      MANAGER         7839 02-APR-81       2975                
        20                                                                      
                                                                               
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400      
        30                                                                      
                                                                               
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                
        30                                                                      
                                                                               

SQL> create table emp3 as
  2  (select empno,
  3  ename,
  4  job,
  5  mgr,
  6  hiredate,
  7  sal,
  8  comm,
  9  deptno from emp)
 10  
SQL>         organization external
SP2-0734: unknown command beginning "organizati..." - rest of line ignored.
SQL>        ( default directory newdir
  2          access parameters
  3          ( records delimited by newline
  4            fields terminated by ','
  5          )
  6          location ('empreport.csv')
  7      );
       ( default directory newdir
         *
ERROR at line 1:
ORA-00928: missing SELECT keyword


nobleitAsked:
Who is Participating?
 
OP_ZaharinConnect With a Mentor Commented:
- you can't create an External Table using "create table as select" syntax. you need to specify the column and datatype as a normal table creation.
- i don't have scott schema with me so the following datatype is just assumption for eg:

create table emp3
    (
    empno number(6,0),
    ename varchar2(20),
    job varchar2(10),
    mgr number(6,0),
    hiredate date,
    sal number(8,2),
    comm number(2,2),
    deptno number(4,0)
    )
   organization external
   ( default directory newdir
   access parameters
   ( records delimited by newline
   fields terminated by ','
   )
   location ('empreport.csv')
   );
0
 
slightwv (䄆 Netminder) Commented:
Check for a blank lines in your script.  Sqlplus hates those.
0
 
OP_ZaharinCommented:
- looking at your "SQL> create table emp3 as" line, it got truncated after line 10. that is why it giving the error. i suggest you to create a sql script file and put the whole script in that file. then run the sql script in SQL Plus using that file.
- or make sure no blank lines in between line "deptno from emp)" and  "organization external"
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
mrjoltcolaCommented:
After you fix the syntax error, make sure you also granted privs on that directory to scott.

grant read, write on directory newdir to scott;
0
 
nobleitAuthor Commented:
I double checked with the privs and directory..

still shows some error..

SQL> create table emp3 as
  2  (select empno,
  3  ename,
  4  job,
  5  mgr,
  6  hiredate,
  7  sal,
  8  comm,
  9  deptno from emp)
 10  organization external
 11  ( default directory newdir
 12  access parameters
 13  ( records delimited by newline
 14  fields terminated by ','
 15  )
 16  location ('empreport.csv')
 17  );
organization external
*
ERROR at line 10:
ORA-00933: SQL command not properly ended
0
 
nobleitAuthor Commented:
my goal is to get the table emp in an external location in csv
(the above script is successfully created..)

0
 
OP_ZaharinCommented:
- there are few steps that you need to ensure is in place for external table to success. even though the script successfully executed.

- i suggest you to follow the following step by step external table procedure which similar to what you are trying to do:

http://www.orafaq.com/node/848
0
 
nobleitAuthor Commented:
please advice..
in that link the csv file we create manually with no data .

and we create table for that external table....
now we insert values for the table in the oracle database...
and if we commit we can see the corresspong values in the external table we created earlier..
please advice my logic is correct or not...
0
 
slightwv (䄆 Netminder) Commented:
Not correct.  They are read only.

Always check the docs:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/tables013.htm#ADMIN12896

Oracle Database allows you read-only access to data in external tables. External tables are defined as tables that do not reside in the database
...
0
 
OP_ZaharinCommented:
- nobleit, looks like i have to corrected my statement. yes - you can create an External Table using "create table as select" syntax but as follows. i have tested this and it works:

create table emp4
    organization external
    ( type oracle_datapump
      default directory newdir
      location ('emp.dmp')
   ) as select * from emp;

Open in new window


SELECT * FROM emp4;

Open in new window


- and from the document that slightwv share, "Prior to Oracle Database 10g, external tables were read-only. However, as of Oracle Database 10g, external tables can also be written to." this is new, so i've overlook this. so written it to a file is possible, but it only works using the ORACLE_DATAPUMP driver therefore it only writes to a .dmp file. so your objective to write it to csv file will not work.
0
 
nobleitAuthor Commented:
Dear Zaharin:

I am sorry if did something wrong here..

SQL> create table emp4
  2  (
  3  empno number(6,0),
  4  ename varchar2(20),
  5  job varchar2(10),
  6  mgr number(6,0),
  7  hiredate date,
  8  sal number(8,2),
  9  comm number(2,2),
 10  deptno number(4,0)
 11  )
 12  organization external
 13  (type oracle_datapump
 14  default directory newdir
 15  access parameters
 16  (
 17  records delimited by newline
 18  fields terminated by ','
 19  location ('emp.dmp')
 20  location('empreport.csv')
 21  )
 22  as select * from emp;
as select * from emp
*
ERROR at line 22:
ORA-30648: missing LOCATION keyword


0
 
OP_ZaharinCommented:
- its ok nobleit, we both learn new things everyday :)

ok lets do this:
- my question would be, what is your objective? you cannot do both write and read in a single syntax as what you do in your script above.
i- to write data to a file as external table? i have explain that you cannot write to other flat file csv/dat/txt. only write to a datapump dmp formatted file is allowed.
OR
ii- to read an exisitng csv file with data?

- if you answer is (i), then do this:
create table emp4
   organization external
    ( type oracle_datapump
      default directory newdir
      location ('emp.dmp')
   as select * from emp;

- if (ii), my answer in ID35735364 would work for query to a csv file.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.