asked on

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

slightwv (䄆 Netminder)

Check for a blank lines in your script. Sqlplus hates those.

OP_Zaharin

- 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"

mrjoltcola

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;

nobleit

ASKER

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

ASKER CERTIFIED SOLUTION

OP_Zaharin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nobleit

ASKER

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

OP_Zaharin

- 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

nobleit

ASKER

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...

slightwv (䄆 Netminder)

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
...

OP_Zaharin

- 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.

nobleit

ASKER

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

OP_Zaharin

- 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.