469,343 Members | 5,694 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,343 developers. It's quick & easy.

Store file in database tables

dmjpro
2,476 2GB
Can one explain me in details how to store a text file in a table.For example I have a text file named as aa.txt.I want to store it in a table.Or i can also create a table in order to store it.How can i store it in that table.Please explain it in details.
Jul 16 '07 #1
10 9809
debasisdas
8,127 Expert 4TB
you need to user the datatype BFILE for your purpose.
Jul 16 '07 #2
dmjpro
2,476 2GB
you need to user the datatype BFILE for your purpose.

Thanks for your reply.I understand i have to use BFILE.But can u tell me the entire procedure in details.
Jul 16 '07 #3
debasisdas
8,127 Expert 4TB
try the following steps

Expand|Select|Wrap|Line Numbers
  1. create directory DIR1 as 'd:\data'
  2.  
  3. create table filetable(id number(6),filename bfile)
  4.  
  5. insert into filetable values (1,bfilename('DIR1','filename'));
the file to be stored must be in the specified directory / path
Jul 16 '07 #4
debasisdas
8,127 Expert 4TB
The BFILENAME function does not validate that the user has READ privileges on the specified file or directory alias. It also does not perform a physical check to see if the directory or file actually exist. Instead, these checks are performed when access against the BFILE object is attempted.

There are several issues to keep in mind with directory aliases:

An alias is a database object, an "internal" name for an external, filesystem-based directory location.

You must create a directory alias before you can use it.

To create a directory alias, you will need the CREATE DIRECTORY or CREATE ANY DIRECTORY system privileges.

To access a directory alias, you must be the owner or be granted the READ privilege on that alias.

Directory aliases can be case-sensitive!
Jul 16 '07 #5
dmjpro
2,476 2GB
try the following steps

Expand|Select|Wrap|Line Numbers
  1. create directory DIR1 as 'd:\data'
  2.  
  3. create table filetable(id number(6),filename bfile)
  4.  
  5. insert into filetable values (1,bfilename('DIR1','filename'));
the file to be stored must be in the specified directory / path

Thank you sir.But still I found one problem.It is stored in the database.But when I using 'select * from filetable;' Then It showing value error under filename column.Then how can i see the contents of the file which is stored inside the column filename.
Jul 16 '07 #6
debasisdas
8,127 Expert 4TB
Suppose if u dont have the prvileges then connect to DBA account or ask your DBA to do the same for you and grant the privilege:

SQL> GRANT CREATE DIRECTORY TO user_name;

SQL> CREATE DIRECTORY dd AS 'd:\data';

if u want to grant previleges on the directory then.

SQL> GRANT READ ON DIRECTORY projects TO new_user;

ALL_DIRECTORIES is the data dictionary view for directories.

In the call to BFILENAME, the case used to set that directory alias must match the case originally used to define the directory alias.
Jul 16 '07 #7
debasisdas
8,127 Expert 4TB
may i know please what is the tool u are using.
Jul 16 '07 #8
dmjpro
2,476 2GB
may i know plese what is the tool u are using.

I am using pl/sql developer.
Jul 16 '07 #9
debasisdas
8,127 Expert 4TB
Column or attribute of type BFILE can not be displayed by using plsql developer or by SQL* PLUS.

u need to retrive it programitically.
Jul 16 '07 #10
dmjpro
2,476 2GB
Column or attribute of type BFILE can not be displayed by using plsql developer or by SQL* PLUS.

u need to retrive it programitically.

Please tell me the procedure to retrieve it programitically.
Jul 16 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Christopher Brandsdal | last post: by
reply views Thread by Mark | last post: by
6 posts views Thread by Rudy | last post: by
1 post views Thread by rdemyan via AccessMonster.com | last post: by
reply views Thread by harshad | last post: by
3 posts views Thread by M.-A. Lemburg | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.