Home » RDBMS Server » Server Utilities » uploading text_file to oracle database
uploading text_file to oracle database [message #262460] Mon, 27 August 2007 02:06 Go to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
Hi all,
I am using SQL+ editor.
I want to upload a text file from my desktop and put into a column of the table.
Is it possible ? If yes then whats the querry for it?
Thanks and Regards
Sunil Gaurav
Re: uploading text_file to oracle database [message #262471 is a reply to message #262460] Mon, 27 August 2007 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at SQL*Loader utility.

Regards
Michel
icon7.gif  Re: uploading text_file to oracle database [message #262479 is a reply to message #262471] Mon, 27 August 2007 03:15 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
Thanks Michel.
Regards
Sunil Gaurav
Re: uploading text_file to oracle database [message #262480 is a reply to message #262460] Mon, 27 August 2007 03:26 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Use BFile data type and follow the following steps

CREATE DIRECTORY DIR1 AS 'd:\data'

CREATE TABLE filetable(id NUMBER(6),filename bfile)

INSERT INTO filetable VALUES (1,BFILENAME('DIR1','filename'));
Re: uploading text_file to oracle database [message #262491 is a reply to message #262480] Mon, 27 August 2007 04:01 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
But I m not able to see the content of the file here.
CREATE DIRECTORY DIR1 AS 'd:\data';
CREATE TABLE filetable(id NUMBER(6),filename bfile);
INSERT INTO filetable VALUES (1,BFILENAME('DIR1','sunil.dat'));
select * from filetable;

output i am getting is...

ID FILENAME
---------------------- --------------------------
1
Re: uploading text_file to oracle database [message #262502 is a reply to message #262491] Mon, 27 August 2007 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not load the file into the table, it just "references" the file into the table.

Regards
Michel
Re: uploading text_file to oracle database [message #262506 is a reply to message #262460] Mon, 27 August 2007 05:41 Go to previous messageGo to next message
p_rasad
Messages: 1
Registered: February 2007
Location: MUMBAI
Junior Member

Also you can use utl pcks to upload data, then you must have the format of txt file.

Regards,

Prasad Patil.
Re: uploading text_file to oracle database [message #262510 is a reply to message #262506] Mon, 27 August 2007 05:47 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
I need the querry for it. I will be very grateful iff you supply
the querry to me.
Thanks
Sunil Gaurav
Re: uploading text_file to oracle database [message #262511 is a reply to message #262510] Mon, 27 August 2007 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Describe in details what you want.
First, describe the target table.

Regards
Michel
Re: uploading text_file to oracle database [message #262516 is a reply to message #262511] Mon, 27 August 2007 05:59 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
Hi Michel,
Thanks for giving attention. My target table is emp.
I want to insert into the column's of emp through the text file.
My file location is D:\sunil.txt .
Thanks and Regards
Sunil Gaurav
Re: uploading text_file to oracle database [message #262518 is a reply to message #262516] Mon, 27 August 2007 06:16 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
description of emp table is:
desc scott.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)
8 rows selected

[Updated on: Mon, 27 August 2007 07:32] by Moderator

Report message to a moderator

Re: uploading text_file to oracle database [message #262533 is a reply to message #262518] Mon, 27 August 2007 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Where is the file in this description?

Regards
Michel
Re: uploading text_file to oracle database [message #262540 is a reply to message #262533] Mon, 27 August 2007 07:47 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
file contains values to be inserted in the emp table:
1562,'Sunil','hr',101,sysdate,17000,120,40

file is saved as sunil.txt in D drive of my harddisc.
its path is D:\sunil.txt.
Regards
Sunil Gaurav
Re: uploading text_file to oracle database [message #262542 is a reply to message #262540] Mon, 27 August 2007 07:53 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Go through below link

http://orafaq.com/faqloadr.htm

and upload your data.
Re: uploading text_file to oracle database [message #262550 is a reply to message #262542] Mon, 27 August 2007 08:10 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
Getting error with this code:

load data
infile 'd:\sunil.csv'
into table scott.emp
fields terminated by "," optionally enclosed by '"'		  
( EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO);

The error informations are:...
Error starting at line 1 in command:
load data
Error report:
Unknown Command

Error starting at line 2 in command:
infile 'd:\sunil.csv'
Error report:
Unknown Command

Error starting at line 3 in command:
into table scott.emp
Error report:
Unknown Command

Error starting at line 4 in command:
fields terminated by "," optionally enclosed by '"'		  
Error report:
Unknown Command

Error starting at line 5 in command:
( EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
Error at Command Line:1 Column:2
Error report:
SQL Error: ORA-00928: missing SELECT keyword


Regards
Sunil Gaurav
Re: uploading text_file to oracle database [message #262555 is a reply to message #262550] Mon, 27 August 2007 08:33 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

its path is D:\sunil.txt.

your filename extention is .txt not .csv or can you post here your sqlldr command.

Regards
Taj
Re: uploading text_file to oracle database [message #262557 is a reply to message #262550] Mon, 27 August 2007 08:35 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not SQL*Plus script this is SQL*Loader control file.
Refer to the document I pointed for the syntax of calling this tool.

Regards
Michel
Previous Topic: Can we Add table data via Import Command using oracle database 8.1.5
Next Topic: sqlldr errors
Goto Forum:
  


Current Time: Wed Dec 07 04:59:11 CST 2016

Total time taken to generate the page: 0.07830 seconds