Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

external file

Posted on 2011-05-10
12
Medium Priority
?
1,151 Views
Last Modified: 2012-05-11
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


0
Comment
Question by:nobleit
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35734550
Check for a blank lines in your script.  Sqlplus hates those.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35734570
- 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
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35734591
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:nobleit
ID: 35735265
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
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 2000 total points
ID: 35735364
- 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
 

Author Comment

by:nobleit
ID: 35737697
my goal is to get the table emp in an external location in csv
(the above script is successfully created..)

0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35737771
- 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
 

Author Comment

by:nobleit
ID: 35738072
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35738185
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35738735
- 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
 

Author Comment

by:nobleit
ID: 35743537
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35743588
- 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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question