By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,993 Members | 1,984 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,993 IT Pros & Developers. It's quick & easy.

upload text file to a database

P: 5
hi all,
i am new to oracle i want to know how to upload a file to a database and read the content of the file
Jan 25 '08 #1
Share this Question
Share on Google+
3 Replies


debasisdas
Expert 5K+
P: 8,127
You have two options for the same

1. Use sql loader to load the data from flat file to database.
2. Use external table.
Jan 25 '08 #2

P: 93
Expand|Select|Wrap|Line Numbers
  1.  
  2. create table demo
  3.     ( id        int primary key,
  4.       theClob    clob
  5.     )
  6. /
  7.  
  8. create or replace directory my_files as 'C:\';
  9. /
  10.  
  11.  
Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE OR REPLACE procedure load_a_file( p_id in number,
  3.                         p_filename in varchar2 )
  4.  as
  5.      l_clob    clob;
  6.      l_bfile    bfile;
  7.  begin
  8.      insert into demo values ( p_id, empty_clob() )
  9.      returning theClob into l_clob;
  10.      l_bfile := bfilename( 'MY_FILES', p_filename );
  11.      dbms_lob.fileopen( l_bfile );
  12.      dbms_lob.loadfromfile( l_clob, l_bfile,
  13.                             dbms_lob.getlength( l_bfile ) );
  14.      dbms_lob.fileclose( l_bfile );
  15.  end;
  16. /
  17.  
  18. EXECUTE load_a_file(2,'YY.TXT');
  19. /
  20.  
  21.  
Jan 30 '08 #3

P: 93
Use Sql Loader
You Will Get This Programm Easily In Sql Volume Ii Certification Book.
Jan 30 '08 #4

Post your reply

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