Store file in database tables

[dmjpro]

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.

[debasisdas]

you need to user the datatype BFILE for your purpose.

[dmjpro]

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.

[debasisdas]

try 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'));

the file to be stored must be in the specified directory / path

[debasisdas]

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!

[dmjpro]

try 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'));

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.

[debasisdas]

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.

[debasisdas]

may i know please what is the tool u are using.

[dmjpro]

may i know plese what is the tool u are using.

I am using pl/sql developer.

[debasisdas]

Column or attribute of type BFILE can not be displayed by using plsql developer or by SQL* PLUS.

u need to retrive it programitically.

[dmjpro]

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.