How To Transport The Excel file to The Oracle 10g

By Ravi

This following information will help you :

IF you are using TOAD then 
1) create a table with the same column of excel data.
2) from Tools  tab u can find import option where u can specify your excel path and table name .
 
From SQL Plus
 
If you do not have access to create directory then ask your DBA he will create directory and share that path to you so u can place ur source file.
 
1) Place the excel sheet in your server directory
 
conn / as sysdba

CREATE OR REPLACE DIRECTORY ext AS 'c:\external';

GRANT READ ON DIRECTORY ext TO your user;
GRANT WRITE ON DIRECTORY ext TO your user;
 
2) Create External table to export the excel data to database.  After creation it will store data external, if want that data then store in permanent table as
 
create table permanent table
as
select * from external table.
 
CREATE TABLE ext_tab (
empno  CHAR(4),
ename  CHAR(20),
job    CHAR(20),
deptno CHAR(2))
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY ext
    ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    BADFILE 'bad_%a_%p.bad'   --------------> optional
    LOGFILE 'log_%a_%p.log'   --------------> optional
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (empno, ename, job, deptno))
    LOCATION ('demo1.dat')  -----> location of your excel sheet

  )
PARALLEL
REJECT LIMIT 0
NOMONITORING;

SELECT * FROM ext_tab;

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.