-
Creating External Table DDL - Oracle 10g
Code:
CREATE TABLE oldemp ( fname char(25), lname char(25) )
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY emp_dir
ACCESS PARAMETERS
(RECORDS DELIMINATED BT NEWLINE
NOBADFILE
NOLOGFILE
FIELDS TERMINATED BY ',' (fname POSITION (1:20) CHAR, lname POSITION (22:41) CHAR)
)
LOCATION ('emp.dat') )
PARALLEL 5
REJECT LIMIT 200;
1. Can you please explain this part:
Code:
ACCESS PARAMETERS
(RECORDS DELIMINATED BT NEWLINE
NOBADFILE
NOLOGFILE
FIELDS TERMINATED BY ',' (fname POSITION (1:20) CHAR, lname POSITION (22:41) CHAR)
)
2. Can you please explain what PARALLEL 5 is? and Why do we need it?
I will google anyway. Your help would not harm anyway
-
Originally Posted by peace2009
I will google anyway.
That sounds like a plan.
Google it. Read Oracle documentation -it's free! - then come back with your doubts.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
That sounds like a plan.
Google it. Read Oracle documentation -it's free! - then come back with your doubts.
I got the answers. However, i'm facing another problem. I did
Code:
create or replace directory emp_dir
as 'F:\emp_dir';
The folder is created in F:\....It did not show up even though the transaction succeeded...I have all grants i need.
I'm running Win Vista x32...
After the above query, i did:
Code:
drop table emp_data;
CREATE TABLE emp_data
(first_name VARCHAR2(20)
,last_name VARCHAR2(20)
, email VARCHAR2(30)
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY emp_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
NOBADFILE
NOLOGFILE
FIELDS
( first_name POSITION ( 1:20) CHAR
, last_name POSITION (22:41) CHAR
, email POSITION (43:72) CHAR )
)
LOCATION ('emp.dat') ) ;
The table creation succeeded, but when i do select * from emp_data;
Error starting at line 1 in command:
select * from emp_data
Error report:
SQL Error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file emp.dat in EMP_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
Any help will be appreciated....Thanks
-
SQL> SELECT *
FROM dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS EMP_DIR
F:\emp_dir
SYS SUBDIR
D:\oracle\product\10.2.0\db_1\demo\schema\order_entry\/2002/Sep
SYS XMLDIR
D:\oracle\product\10.2.0\db_1\demo\schema\order_entry\
dba_directories records shows that i got emp_dir i just created, but i can't see it in my F: drive
-
Grant read permission to the directory you created. Then check
-
like this: grant read on directory emp_dir to user
Last edited by abhi_oracle; 07-26-2009 at 01:47 PM.
-
Originally Posted by abhi_oracle
like this: grant read on directory emp_dir to user
It did not help....I even did
grant read, write on directory emp_dir to scott;
Didn't help too
-
What did you mean by "dba_directories records shows that i got emp_dir i just created, but i can't see it in my F: drive" .. you gotta create that directory yourself ... go to F drive and create a physical folder with that name
-
Originally Posted by abhi_oracle
What did you mean by "dba_directories records shows that i got emp_dir i just created, but i can't see it in my F: drive" .. you gotta create that directory yourself ... go to F drive and create a physical folder with that name
Good. It worked. However, if i'm a remote user and i have been granted create directory privilege, how can i manage creating the folder and file manually! I might not have access to the PC or Unix Server Drive to create the directory by myself. Any idea?
Last edited by peace2009; 07-26-2009 at 02:50 PM.
-
Originally Posted by peace2009
if i'm a remote user and i have been granted create directory privilege, how can i manage creating the folder and file manually! I might not have access to the PC or Unix Server Drive to create the directory by myself.
That would mean you are not a DBA, create directory priv was granted in error to you and, you should ask a DBA for help.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|