Beware of the case sensitive in the directory creation
Ringo Li, April 16, 2002 - 4:01 am UTC
Tom,
You should have warn us re the upper case (by default) on directory created.
Not until I check out your book (page 410), I will lost on what happened.
Anyhow, your book give me great explaination.
Thanks
error
Eugene, January 01, 2004 - 6:11 pm UTC
Hi Tom,
I have a project where I have to store documents into the d/base. We have one 9.2 d/base that I use.
I was able to load two files: one .doc and one .txt, but when I try to view them, both of them come out garbage-looking.
I wonder if you can help me to pinpoint what I may have done wrong.
Here are the steps that I take:
===============================
create table ep_doc_demo
(id int primary key,
theClob clob);
create or replace directory DOC_DIR AS 'c:\temp\doc_dir';
Placed two files in that directory: doc1.doc and doc2.txt
CREATE OR REPLACE PROCEDURE Load_Doc_P (p_id IN NUMBER, p_filename IN VARCHAR2)
AS
l_clob CLOB;
l_bfile BFILE;
BEGIN
INSERT INTO EP_DOC_DEMO
VALUES(p_id,EMPTY_CLOB())
returning theClob INTO l_clob;
l_bfile := BFILENAME('DOC_DIR',p_filename);
dbms_lob.fileopen(l_bfile);
dbms_lob.loadfromfile(l_clob, l_bfile,
dbms_lob.getlength(l_bfile) );
dbms_lob.fileclose(l_bfile);
END Load_Doc_P;
/
select *
from ep_doc_demo;
ID THECLOB
-- ----------------------------------------------
1 ¿¿¿¿
2 ¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿
Why is there a garbage in them?
Thanks for your help
January 01, 2004 - 7:09 pm UTC
well, I would expect the ".doc" to be garbage -- it is a proprietary, binary file format. It is not TXT.
as for the text, well, show us what is in there -- try something like a script;
host echo Hello World > /tmp/data.txt
then load using your stored procedure
then query using sql
and cut and paste the results.
A similar situation
Muhammad Ahmad Imran, January 02, 2004 - 5:10 am UTC
Hi Tom,
I want to insert the data in the database which are currently in the txt files and look like this(an example):
empno: 7369
ename: smith
jobid: clerk
manag: 7902
hired: 17-dec-80
salar: 800
commi:
depno: 20
empno: 7499
ename: allen
jobid: salesman
manag: 7698
hired: 20-feb-81
salar: 1600
commi: 300
depno: 30
empno: 7521
ename: ward
jobid: salesman
manag: 7698
hired: 22-feb-81
salar: 1250
commi: 500
depno: 30
empno: 7934
ename: millar
jobid: clerk
manag: 7782
hired: 23-jan-82
salar: 1300
commi:
depno: 10
And then I want to use "Select * from Emp_tab" to query this.(The results should be simiar to normal emp table)
Some times ago when I have the similar situation then i use this technique
This is the sample data then
1. 2. All praise is due to ALLAH alone, Lord of all the worlds.
1. 3. The Gracious, the Merciful.
1. 4. Master of the Day of Judgment.
1. 5. THEE alone do we worship and THEE alone do we implore for help.
1 6. Guide us in the straight path,
1 7. The path of those on whom THOU hast bestowed THY favours, those who have not incurred THY displeasure and those who have not gone astray.
And I use database as:
SQL> create table ch1(cno number,ano number,
ayyat varchar2(100))
9 organization external
10 (type oracle_loader
11 default directory md2
12 access parameters
13 (fields terminated by '.'
14 )
15 location ('1.txt')
16 )
17 /
Table created.
SQL> select * from ch1;
And then easily get the required results but this time I really need your help.
January 02, 2004 - 9:34 am UTC
create table t ( field varchar2(30), val varchar(30) )
organization external
(type oracle_loader
default directory tmp
access parameters
(fields terminated by ':'
)
location ('test.txt')
)
/
select * from t;
select r,
max(decode(field,'empno', val )) empno,
max(decode(field,'ename', val )) ename,
max(decode(field,'jobid', val )) jobid,
max(decode(field,'manag', val )) manag,
max(decode(field,'hired', val )) hired,
max(decode(field,'salar', val )) salar,
max(decode(field,'commi', val )) commi,
max(decode(field,'depno', val )) depno
from (
select trunc(rownum/8-0.1) r,
trim( both from field ) field,
trim( both from val ) val
from t
)
group by r
/
To the last review:
Muhammad Ahmad Imran, January 11, 2004 - 9:24 pm UTC
I want a database for this type of data....
I put here the actual data file but bear in mind that this is only a part of the whole file(And I have hundreds of such files) so please some dynamic code writen by you is really helpful for me(If you not seen the data in proper format kindly copy to some text file and then examine it)
BA: 20023146007
ET: Myiasis. A problem which gets under the skin.
FT: Fliegenmadenbefall - ein Problem, das unter die Haut geht.
AU: Hertzberg, H.
SO: Forum Kleinwiederkauer/Petits Ruminants, 2002, No.9, pp.15-22
AB: The most important cause of skin myiasis in sheep in
Switzerland is Lucilia sericata. Its development, the lesions
it causes and symptoms are described and illustrated. Control
measures suggested are: prevention by tail docking and spraying
with insecticides or insect growth regulators; therapy; and
biological control.
DE: biological control; insect growth regulators; insecticides;
myiasis; pest control; skin diseases
OD: Lucilia sericata; sheep
GL: Switzerland
UP: Ovis; Bovidae; ruminants; Artiodactyla; mammals; vertebrates;
Chordata; animals; ungulates; Western Europe; Europe; Developed
Countries; EFTA; OECD Countries; Lucilia; Calliphoridae;
Diptera; insects; arthropods; invertebrates
PU: Forum Kleinwiederkauer (Forum Petits Ruminants), Niederonz,
Switzerland
CC: LL823;
HH405;
HH100
LA: German; French
DT: Journal article
SC: Biological Control; Medical and Veterinary Entomology;
Veterinary Medicine
BA: 20023123730
ET: Synanthropy of the Calliphoridae (Diptera) from Valdivia,
Chile.
FT: Sinantropia de los Calliphoridae (Diptera) de Valdivia, Chile.
AU: Figueroa-Roa, L.; Linhares, A. X.
AA: Instituto de Parasitologia, Universidad Austral de Chile,
Valdivia, Chile.
SO: Neotropical Entomology, 2002, Vol.31, No.2, pp.233-239, 40 ref.
AB: The objective of this study was to determine synathropic index
of adult Calliphoridae (Diptera) collected in the city of
Valdivia, Chile (39 deg 48 S; 73 deg 15' W). For this, samples
were taken monthly between September 1996 and August 1997 in
three different localities representing urban, rural and
natural environments. Rat carcass, fish, and chicken viscera
were used as baits. Four blowfly species were collected:
Calliphora vicina, the dominant species in all three baits and
sites of capture, followed by Lucilia sericata, both species
being eusynanthropic. The other two species, Compsomyiops
fulvicrura and Sarconesia magellanica, were collected in
smaller numbers and were characterized respectively as
hemysinanthropic and asynanthropic. In addition to the
synanthropic index, the monthly abundance and the efficiency of
the baits as attractants to the flies as well as their medical
and sanitary importance were also determined and discussed.
DE: ecology; synanthropy
OD: Calliphora vicina; Calliphoridae; Lucilia sericata
GL: Chile
ID: Compsomyiops fulvicrura; Sarconesia; Sarconesia magellanica
UP: Calliphora; Calliphoridae; Diptera; insects; arthropods;
invertebrates; animals; South America; America; Developing
Countries; Threshold Countries; Latin America; Lucilia
PU: Sociedade Entomologica do Brasil (SEB), Londrina, Brazil
IS: 1519-566x
CC: YY000;
ZZ332
LA: Spanish; English
DT: Journal article
SC: Medical and Veterinary Entomology
BA: 20023173425
ET: Molecular approaches to the study of myiasis-causing larvae.
AU: Otranto, D.; Stevens, J. R.
AA: Faculty of Veterinary Medicine, University of Bari, P.O. Box 7,
70010, Valenzano, Bari, Italy.
SO: International Journal for Parasitology, 2002, Vol.32, No.11,
pp.1345-1360, many ref.
AB: Among arthropod diseases affecting animals, larval infections -
myiases - of domestic and wild animals have been considered
important since ancient times. Besides the significant economic
losses to livestock worldwide, myiasis-causing larvae have
attracted the attention of scientists because some parasitize
humans and are of interest in forensic entomology. In the past
two decades, the biology, epidemiology, immunology,
immunodiagnosis and control methods of myiasis-causing larvae
have been focused on and more recently the number of molecular
studies have also begun to increase. The 'new technologies'
(i.e. molecular biology) are being used to study taxonomy,
phylogenesis, molecular identification, diagnosis (recombinant
antigens) and vaccination strategies. In particular, more in
depth molecular studies have now been performed on
Sarcophagidae, Calliphoridae and flies of the Oestridae sister
group. This review discusses the most topical issues and recent
studies on myiasis-causing larvae using molecular approaches.
In the first part, PCR-based techniques and the genes that have
already been analysed, or are potentially useful for the
molecular phylogenesis and identification of myiasis-causing
larvae, are described. The second section deals with the more
recent advances concerning taxonomy, phylogenetics, population
studies, molecular identification, diagnosis and vaccination.
DE: animal diseases; diagnosis; ectoparasitoses; genes;
identification; phylogenetics; polymerase chain reaction;
reviews; taxonomy; vaccination
OD: Calliphoridae; Oestridae; Sarcophagidae
UP: Diptera; insects; arthropods; invertebrates; animals
PU: Elsevier Science Ltd, Oxford, UK
IS: 0020-7519
CC: LL822;
WW700;
YY300;
YY700;
ZZ360;
ZZ900
LA: English
DT: Journal article
SC: Veterinary Medicine; Medical and Veterinary Entomology
January 12, 2004 - 1:10 pm UTC
happy early aprils fools to you too :)
Reminder
Muhammad Ahmad Imran, January 12, 2004 - 10:02 pm UTC
I want to discuss the very last comment posted by me and above the irrelevant followup.
Please read my post and evaluate it.
January 13, 2004 - 1:17 am UTC
i evaluated it -- I'm not going to write your system. give me a break.
Output?
Sikandar Hayat, January 12, 2004 - 10:24 pm UTC
Please provide the desired output for your last post?
Dear Tom, "Remove/Ignore guys like Muhammad Ahmad Imran"
A reader, January 13, 2004 - 11:04 pm UTC
He does not know your technical ability. You sould have someone(to help you out from oracle) to keep this GREAT forum very clean.
Muhammad, Please read Oracle Concept manual and Expert one-on-one Oracle by Tom Kyte.
For the "A Reader"
M Ahmad Imran, January 15, 2004 - 10:05 pm UTC
I never mind for the Tom's follow up whatever he post but who are you to call me out from this forum. Do you understand what is a forum "A Reader".
********Sorry Mr. Reader if you don't like this*******
Sorry Tom, your query is still not what I required so i post the sample data. If you don't like it I never send a post agian. I know what's your postion but doesn't know that there are some people who thinks that Tom is THEIR.
Dear Sir Sikander, I personally mail you to solve my problem
bad link?
Harrison Picot, January 16, 2004 - 1:17 pm UTC
External Table based on CLOB inside database
Dave, January 22, 2004 - 3:39 am UTC
Tom -
Is there a way to base an external table on a row of a table with a CLOB column?
The name "external table" suggests not. If not, I'm guessing I'd have to write the file to the file system, then use the external table with that.
Any other suggestions?
If it is not clear, the desired function is this...
"Generic function to upload file into CLOB" -> "External table based on CLOB used to load data into relational table." (The file structure would be assumed.)
Thanks,
Dave
January 22, 2004 - 7:06 am UTC
ops$tkyte@ORA9IR2> create table t ( id int, x clob );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values (
2 1,
3 'this is line one
4 this is line two
5 this is line three
6 ' );
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
ID X
---------- --------------------------------------------------------------------------------
1 this is line one
this is line two
this is line three
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace type row_type as table of varchar2(4000)
2 /
Type created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function clob_to_table( p_cursor in sys_refcursor ) return row_type
2 pipelined
3 as
4 l_clob clob;
5 l_last number := 1;
6 l_current number;
7 begin
8 loop
9 fetch p_cursor into l_clob;
10 exit when p_cursor%notfound;
11
12 for l_line in 1 .. 50000
13 loop
14 l_current := dbms_lob.instr( l_clob, chr(10), l_last, 1 );
15 exit when (nvl(l_current,0) = 0);
16 pipe row( dbms_lob.substr( l_clob, l_current-l_last+1, l_last ) );
17 l_last := l_current+1;
18 end loop;
19 end loop;
20 close p_cursor;
21 return;
22 end;
23 /
Function created.
ops$tkyte@ORA9IR2> column column_value format a40
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select rownum, column_value
2 from TABLE( clob_to_table( cursor( select x from t where id = 1 ) ) )
3 /
ROWNUM COLUMN_VALUE
---------- ----------------------------------------
1 this is line one
2 this is line two
3 this is line three
Thanks
Dave, January 22, 2004 - 12:09 pm UTC
Thanks Tom. Believe it or not, I actually started to come to this conclusion, but your example confirms with me that this is the best way.
Thanks again,
Dave
Follow up
Dave, January 22, 2004 - 10:32 pm UTC
I realized my original question was not correct (and somewhat foolish to consider using an external table.) I am actually generically uploading a text file but it is going into a BLOB column NOT a CLOB column.
But based on Tom's reply and poking around other postings on askTom, I came up with the code I needed. It is not production code, but in case anyone finds it useful, it seems to be working fine...
CREATE TYPE ROW_TYPE AS TABLE OF VARCHAR2(4000);
CREATE OR REPLACE FUNCTION BLOB_TO_VARCHAR2( p_blob in blob ) return row_type
pipelined
as
l_str1 varchar2(4000);
l_str2 varchar2(4000);
l_leftover varchar2(2000);
l_chunksize number := 2000;
l_offset number := 1;
l_linebreak varchar2(2) := chr(13)||chr(10);
l_length number;
begin
l_length := dbms_lob.getlength(p_blob);
while l_offset < l_length loop
l_str1 := l_leftover || utl_raw.cast_to_varchar2(dbms_lob.substr(p_blob, l_chunksize, l_offset));
l_leftover := null;
l_str2 := l_str1;
while l_str2 is not null loop
if instr(l_str2, l_linebreak) <= 0 then
l_leftover := l_str2;
l_str2 := null;
else
pipe row ( substr(l_str2, 1, instr(l_str2, l_linebreak)-1) );
l_str2 := substr(l_str2, instr(l_str2, l_linebreak)+2);
end if;
end loop;
l_offset := l_offset + l_chunksize;
end loop;
if l_leftover is not null then
pipe row ( l_leftover );
end if;
return;
end;
/
select * from table(select blob_to_varchar2(blob_col) from blob_table where pk = 1);
Textfile insert and retrieval
Djecal, February 06, 2004 - 10:26 am UTC
Hi Tom,
I followed your steps in insertind textfile in the database, but for some reason, when I execute the procedure, I received the following error message:
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at "MASTERDB.LOAD_FILE", line 11
ORA-06512: at line 1
Here is the entire code:
create or replace procedure load_file (p_id in number,
p_filename in varchar2)
as
l_resp clob;
l_bfile bfile;
begin
insert into narrat_tb values (p_id, empty_clob() )
returning resp into l_resp;
l_bfile := bfilename ( my_resp,p_filename);
dbms_lob.fileopen (l_bfile);
dbms_lob.loadfromfile (l_resp,l_bfile, dbms_lob.getlength(l_bfile));
dbms_lob.fileclose( l_bfile );
end;
Before the procedure, I have created a table and a directory . I copied a text file into the location referenced by the directory on the same machine as the Oracle database. Please tell me what I'm doing wrong for the procedure not to find the directory.
February 07, 2004 - 1:18 pm UTC
for some reason? or for the reason that either the directory or file does not actually exist :)
that is what it is telling you.
You have not given us a complete example, so I cannot explain further other than to say "you didn't do what you said"
Make sure the case of the directory object is correct... if you
create directory my_files as ...
then make sure you used 'MY_FILES' in uppercase. Also, make sure the directory is in fact ON THE SERVER. Also make sure the oracle account can actually read that directory. And likewise for the file.
Textfile insert and retrieval
Djecal, February 06, 2004 - 10:30 am UTC
Hi Tom,
I followed your steps in inserting textfile in the database, but for some reason, when I execute the procedure, I receive the following error message:
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at "MASTERDB.LOAD_FILE", line 11
ORA-06512: at line 1
Here is the entire code:
create or replace procedure load_file (p_id in number,
p_filename in varchar2)
as
l_resp clob;
l_bfile bfile;
begin
insert into narrat_tb values (p_id, empty_clob() )
returning resp into l_resp;
l_bfile := bfilename ( my_resp,p_filename);
dbms_lob.fileopen (l_bfile);
dbms_lob.loadfromfile (l_resp,l_bfile, dbms_lob.getlength(l_bfile));
dbms_lob.fileclose( l_bfile );
end;
Before the procedure, I have created a table and a directory . I copied a text file into the location referenced by the directory on the same machine as the Oracle database. Please tell me what I'm doing wrong for the procedure not to find the directory.
clob insert and retrieval
djecal, February 10, 2004 - 11:02 am UTC
Thanks Tom,
I decreate table narrat_tb (
id int primary key,
resp clob)
tablespace users;
create or replace directory
MY_FILES as 'd:\narrative\textfiles\resp';
create public sysnonym MY_FILES for MY_FILES;
create or replace procedure load_file (p_id in number,
p_filename in varchar2)
as
l_resp clob;
l_bfile bfile;
begin
insert into narrat_tb values (p_id, empty_clob() )
returning resp into l_resp;
l_bfile := bfilename ( 'MY_FILES',p_filename);
dbms_lob.fileopen (l_bfile);
dbms_lob.loadfromfile (l_resp,l_bfile, dbms_lob.getlength(l_bfile));
dbms_lob.fileclose( l_bfile );
end;
***** Then, I went to the physical drive D and created the following path
D:\narrative\textfiles\resp to which I copied a file called resp04.txt
Finally, when I execute the procedure using the following statement
exec load_file (1,'resp04.txt');
I received the following error message:
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at "MASTERDB.LOAD_FILE", line 11
ORA-06512: at line 1
cided to provide you with the entire code and the error messages.
February 10, 2004 - 3:44 pm UTC
sorry - but once again, the only thing I can say is
a) d: is not on the server itself or
b) the lowly privileged account that is 'oracle' does not have access to this path or
c) the file really does not exist
Here is your script, cut and pasted, showing this works (when run ON THE SERVER so the file actually exists ON THE DATABASE SERVER)
ops$tkyte@ORA9IR2> create table narrat_tb (
2 id int primary key,
3 resp clob)
4 /
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace directory MY_FILES as '/tmp/';
Directory created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create public sysnonym MY_FILES for MY_FILES;
create public sysnonym MY_FILES for MY_FILES
*
ERROR at line 1:
ORA-00901: invalid CREATE command
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure load_file
2 ( p_id in number,
3 p_filename in varchar2)
4 as
5 l_resp clob;
6 l_bfile bfile;
7 begin
8 insert into narrat_tb values (p_id, empty_clob() )
9 returning resp into l_resp;
10 l_bfile := bfilename ( 'MY_FILES',p_filename);
11 dbms_lob.fileopen (l_bfile);
12 dbms_lob.loadfromfile (l_resp,l_bfile, dbms_lob.getlength(l_bfile));
13 dbms_lob.fileclose( l_bfile );
14 end;
15 /
Procedure created.
ops$tkyte@ORA9IR2> host rm -f /tmp/resp04.txt
ops$tkyte@ORA9IR2> exec load_file (1,'resp04.txt');
BEGIN load_file (1,'resp04.txt'); END;
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at "OPS$TKYTE.LOAD_FILE", line 11
ORA-06512: at line 1
ops$tkyte@ORA9IR2> host echo hello world > /tmp/resp04.txt
ops$tkyte@ORA9IR2> exec load_file (1,'resp04.txt');
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select * from narrat_tb;
ID RESP
---------- --------------------------------------------------------------------------------
1 hello world
clodb data type insert and retrieval
A reader, February 10, 2004 - 11:08 am UTC
Sorry, here is the entiere script:
create table narrat_tb (
id int primary key,
resp clob)
tablespace users;
create or replace directory
MY_FILES as 'd:\narrative\textfiles\resp';
create public sysnonym MY_FILES for MY_FILES;
create or replace procedure load_file (p_id in number,
p_filename in varchar2)
as
l_resp clob;
l_bfile bfile;
begin
insert into narrat_tb values (p_id, empty_clob() )
returning resp into l_resp;
l_bfile := bfilename ( 'MY_FILES',p_filename);
dbms_lob.fileopen (l_bfile);
dbms_lob.loadfromfile (l_resp,l_bfile, dbms_lob.getlength(l_bfile));
dbms_lob.fileclose( l_bfile );
end;
***** Then, I went to the physical drive D and created the following path
D:\narrative\textfiles\resp to which I copied a file called resp04.txt
Finally, when I execute the procedure using the following statement
exec load_file (1,'resp04.txt');
I received the following error message:
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at "MASTERDB.LOAD_FILE", line 11
ORA-06512: at line 1
I am still having problems with the execution. Thanks!!
load text files
A reader, March 15, 2004 - 1:46 pm UTC
Hi
I have around 100 shell scripts which I would like to store in a table to centralize administration. I was wondering if the procedure is the only way or is there anyway with SQL*Loader to load 100 text files to the database?
March 15, 2004 - 2:51 pm UTC
¿¿¿¿¿¿¿¿¿ after load
Gj, July 02, 2004 - 3:16 am UTC
Did anyone fathom out why this comes up in the clob after loading? Searching for ¿¿¿¿¿¿¿¿ is real pig as most search engines through it out as garbage.
I get it even after a simple echo string >/tmp/txt.txt. At first I thought it was permissions, but no luck.
July 02, 2004 - 9:24 am UTC
well, those characters, whatever they are, are not readable here.
but.... perhaps you have unicode files? they start with bytes of data.
Solution to getting ¿¿¿¿¿¿ CLOB loads
Gj, July 02, 2004 - 3:34 am UTC
The character conversions do not happen automatically! They do with sqlldr. Anyway, I used this very basic framework and it works fine for loading simple text files.
Use the dbms_lob.loadclobfromfile.
create or replace
procedure load_a_file( p_id in number,
p_filename in varchar2 )
as
l_clob clob;
l_bfile bfile;
-- ------ Added ----------
dest_offset INTEGER := 1;
src_offset INTEGER := 1;
src_csid NUMBER := NLS_CHARSET_ID('US7ASCII');
lang_context INTEGER := dbms_lob.default_lang_ctx;
warning INTEGER;
-- ------ Added ----------
begin
insert into demo values ( p_id, empty_clob() )
returning theClob into l_clob;
l_bfile := bfilename( 'MY_FILES', p_filename );
dbms_lob.fileopen( l_bfile );
-- dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength( l_bfile ), );
-- Changed to ----
dbms_lob.loadclobfromfile(l_clob, l_bfile, dbms_lob.getlength( l_bfile ),
dest_offset, src_offset, src_csid, lang_context, warning);
-- Changed to ----
dbms_lob.fileclose( l_bfile );
end;
/
A reader, July 16, 2004 - 3:09 pm UTC
Hi,
This article talks about uploading files from server to database. I have a situation where in I have the clients (using the browser) and they need to upload files. How can I achieve that.
July 16, 2004 - 10:55 pm UTC
you would have to mention briefly at least - what is in the middle tier and what technologies you are using.
mod_plsql with iAS -- trivial, see the documentation for file upload..
Kevin, June 28, 2006 - 1:56 pm UTC
Hi Tom,
Can you tell me why my loop doesn't work right please?
DECLARE
l_clob clob;
lob_len number;
substr_start number :=1;
instr_lf number;
instr_start_string number :=1;
instr_occurener number :=1;
end_string number :=1;
begin
select dlog_log_id,the_clob
into :P33_DLOG_LOG_ID, l_clob
from clob_table
where dlog_log_id=:P33_DLOG_LOG_ID;
lob_len := dbms_lob.getlength(l_clob);
instr_lf := dbms_lob.instr(l_clob, chr(10), instr_start_string, instr_occurener);
loop
htp.prn('<table><p>');
htp.prn(instr_lf);
htp.prn(dbms_lob.substr(l_clob,instr_lf , substr_start));
instr_occurener := instr_occurener + 1;
instr_lf := dbms_lob.instr(l_clob, chr(10), instr_start_string, instr_occurener);
end_string := end_string + instr_lf;
exit when end_string > lob_len;
htp.prn('</p></table>');
end loop;
END;
This is just a piece of my output from above code:
19Reprot Day: Friday
20Reprot Day: Friday
90Reprot Day: Friday SQL*Plus: Release 10.1.0.3.0 - Production on Fri Jun 23 09:23:26 2006
91Reprot Day: Friday SQL*Plus: Release 10.1.0.3.0 - Production on Fri Jun 23 09:23:26 2006
147Reprot Day: Friday SQL*Plus: Release 10.1.0.3.0 - Production on Fri Jun 23 09:23:26 2006 Copyright (c) 1982, 2004, Oracle. All rights reserved.
148Reprot Day: Friday SQL*Plus: Release 10.1.0.3.0 - Production on Fri Jun 23 09:23:26 2006 Copyright (c) 1982, 2004, Oracle. All rights reserved.
149Reprot Day: Friday SQL*Plus: Release 10.1.0.3.0 - Production on Fri Jun 23 09:23:26 2006 Copyright (c) 1982, 2004, Oracle. All rights reserved.
163Reprot Day: Friday SQL*Plus: Release 10.1.0.3.0 - Production on Fri Jun 23 09:23:26 2006 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to:
Thank you
June 28, 2006 - 5:00 pm UTC
not knowing entirely what your goal is, what your data is (eg: NO EXAMPLE CREATE+INSERT provided...)
I'm not really willing to say...
but I can say - you don't need to print "line by line", HTML doesn't care about the line feeds, just print 32 chunks.
Kevin, June 28, 2006 - 5:38 pm UTC
Hi Tom,
The file i will print out, that has size are 44497 bytes. So, it can not print in report in HTMLDB. Since it just limited 32K . That's why, I have to use line feed to print line by line.
here is a piece of my original file:
Reprot Day: Friday
SQL*Plus: Release 10.1.0.3.0 - Production on Fri Jun 23 09:23:26 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning and Data Mining options
#############################################################################
# blowup.sql-Segments that can not obtain their next extent.
#############################################################################
#############################################################################
# tnsga.sql - SGA statistics.
#############################################################################
This is the code i used to print out, but it not right.
DECLARE
l_clob clob;
lob_len number;
substr_start number :=1;
instr_lf number;
instr_lf1 number;
instr_start_string number :=1;
instr_occurener number :=1;
instr_occurener2 number :=1;
end_string number :=1;
substr_char number;
begin
select dlog_log_id,the_clob
into :P33_DLOG_LOG_ID, l_clob
from clob_table
where dlog_log_id=:P33_DLOG_LOG_ID;
lob_len := dbms_lob.getlength(l_clob);
instr_lf := dbms_lob.instr(l_clob, chr(10), instr_start_string, instr_occurener);
htp.prn(dbms_lob.substr(l_clob, instr_lf, substr_start));
loop
instr_occurener := instr_occurener + 1;
instr_occurener2 := instr_occurener2 + 2;
instr_lf := dbms_lob.instr(l_clob, chr(10), instr_start_string, instr_occurener);
instr_lf1 := dbms_lob.instr(l_clob, chr(10), instr_start_string, instr_occurener2);
substr_char := instr_lf1 - instr_lf;
htp.prn('<p>');
htp.prn(dbms_lob.substr(l_clob,substr_char , instr_lf));
htp.prn('</p>');
instr_occurener := instr_occurener + 1;
end_string := end_string + instr_lf;
exit when end_string > lob_len;
end loop;
END;
this is the output from that code above:
Reprot Day: Friday
SQL*Plus: Release 10.1.0.3.0 - Production on Fri Jun 23 09:23:26 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
#############################################################################
#############################################################################
# tnsga.sql - SGA statistics.
blowup.sql
Please let me know which step i'm missing;
Thank you so much
June 28, 2006 - 6:39 pm UTC
use 32000 character chunks, like I demonstrated elsewhere for you regarding this exact question.
Kevin, June 29, 2006 - 9:12 am UTC
I've applied that code and it work wonderful, but all the text just display in one line. And this is a little piece of output:
Reprot Day: Friday SQL*Plus: Release 10.1.0.3.0 - Production on Fri Jun 23 09:23:26 2006 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production With the Partitioning and Data Mining options ############################################################################# # blowup.sql - Segments that can not obtain their next extent. ############################################################################# ############################################################################# # tnsga.sql - SGA statistics.
ORIGINAL OUTPUT IS:
Reprot Day: Friday
SQL*Plus: Release 10.1.0.3.0 - Production on Fri Jun 23 09:23:26 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production With the Partitioning and Data Mining options
###########################################################################
# blowup.sql-Segments that can not obtain their next extent.
###########################################################################
###########################################################################
# tnsga.sql - SGA statistics
###########################################################################
That's why i try to apply the LF to break the output like original. Can you please tell me how to do that?
Thanks
June 29, 2006 - 9:29 am UTC
that wouldn't do a thing (parsing around line feeds)
but:
htp.p( '<pre>' );
loop
htp.prn the stuff
end loop
htp.p( '</pre>' );
would - since the linefeeds that are in fact there will be "obeyed" by the HTML process.
Kevin, June 29, 2006 - 1:01 pm UTC
Thank you so much Tom. That was resolved my issue.
Tom I have problems whit blobs ...
Susana Morales, June 29, 2006 - 7:14 pm UTC
/*1. load text files INTO DATABASE. probably the easiest way IS TO CREATE a
stored PROCEDURE such AS:*/
CREATE TABLE demo
id INT PRIMARY KEY,
theClob CLOB
)
--2. CREATE DIRECTORY
CREATE OR REPLACE DIRECTORY
MY_FILES AS 'c:\';
---3. Create porcedure
CREATE OR REPLACE
PROCEDURE load_a_file( p_id IN NUMBER,
p_filename IN VARCHAR2 )
AS
l_clob CLOB;
l_bfile BFILE;
BEGIN
INSERT INTO demo VALUES ( p_id, EMPTY_CLOB() )
returning theClob INTO l_clob;
l_bfile := BFILENAME( 'MY_FILES', p_filename );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_clob, l_bfile,
dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
END;
---4. Execute file
SQL> EXEC load_a_file( 1, 'angel.bmp' )
BEGIN load_a_file( 1, 'angel.bmp' ); END;
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at "BOLINF.LOAD_A_FILE", line 10
ORA-06512: at line 1
June 29, 2006 - 7:56 pm UTC
c:\ANGEL.BMP
a) does exist
b) is readable by the oracle server
c) is ON THE SERVER, not your workstation
right?
Dear Tom I hav problems with BLOBS...Continuation
Susana Morales, June 30, 2006 - 5:46 pm UTC
I changed the directory to this path (this is my server )to '/interface/infpresi/DPRESI/incoming'
$
$ ls -ltr A*
-rw-r--r-- 1 infpresi infpresi 604830 Jun 27 18:44 ANGEL.BMP
$ pwd
/interface/infpresi/DPRESI/incoming
$ ls -ltr A*
-rw-r--r-- 1 infpresi infpresi 604830 Jun 27 18:44 ANGEL.BMP
$
I`m working on the man
CREATE OR REPLACE
PROCEDURE load_a_file( p_id IN NUMBER,
p_filename IN VARCHAR2 )
AS
l_clob CLOB;
l_bfile BFILE;
BEGIN
INSERT INTO demo VALUES ( p_id, EMPTY_CLOB() )
returning theClob INTO l_clob;
l_bfile := BFILENAME( '/interface/infpresi/DPRESI/incoming', 'ANGEL.BMP' );
DBMS_OUTPUT.put_line ('Abriendo archivo ');
dbms_lob.fileopen( l_bfile );
DBMS_OUTPUT.put_line ('Cargando archivo ');
dbms_lob.loadfromfile( l_clob, l_bfile,
dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
END;
SQL> /
Procedure created.
SQL> EXEC load_a_file( 1, ANGEL.BMP' )
Se realizo la inserción
Abriendo archivo
BEGIN load_a_file( 1, 'ANGEL.BMP' ); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at "BOLINF.LOAD_A_FILE", line 12
ORA-06512: at line 1
July 01, 2006 - 7:44 am UTC
you use directory objects, not paths.
create or replace directory MY_DIR as '/.....'
... bfilename( 'MY_DIR', 'file.name' );
I don't get the same error you do when I run this, I get rather:
ops$tkyte@ORA10GR2> exec load_a_file( 1, 'angel.bmp' )
Abriendo archivo
BEGIN load_a_file( 1, 'angel.bmp' ); END;
*
ERROR at line 1:
ORA-00972: identifier is too long
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "OPS$TKYTE.LOAD_A_FILE", line 11
ORA-06512: at line 1
that is complaining about the directory object. And when I:
ops$tkyte@ORA10GR2> create or replace directory my_dir as '/tmp';
Directory created.
ops$tkyte@ORA10GR2> !echo hello > /tmp/ANGEL.BMP
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> CREATE OR REPLACE
2 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_DIR', 'ANGEL.BMP' );
11 DBMS_OUTPUT.put_line ('Abriendo archivo ');
12 dbms_lob.fileopen( l_bfile );
13 DBMS_OUTPUT.put_line ('Cargando archivo ');
14 dbms_lob.loadfromfile( l_clob, l_bfile,
15 dbms_lob.getlength( l_bfile ) );
16 dbms_lob.fileclose( l_bfile );
17 END;
18 /
Procedure created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec load_a_file( 1, 'angel.bmp' )
Abriendo archivo
Cargando archivo
PL/SQL procedure successfully completed.
It works dandy.
Now, it is strange to load a BMP (binary file) into a CLOB - did you really mean to do that?
Dear Tom I have problems loading a BLOB ...Continuation
Susana Morales, July 03, 2006 - 12:00 pm UTC
I don´t have permissions to write or make a directory directy in /
$ cp ANGEL.BMP /interface
cp: cannot create regular file `/interface/A
So... I get the file in
In this step I put the image into the server in the next path is that correct ?
$ cd /interface/infpresi/DPRESI
$ ls -ltr A*
-rw-r--r-- 1 infpresi infpresi 604830 Jul 3 10:28 ANGEL.BMP
I going to change CLOB by BLOB in order to load a .jpg, BMP because the tarjet of my project is to load an image into de Data Base to manipulate it.
I change mydir to
CREATE OR REPLACE DIRECTORY
MY_FILES AS '/interface/infpresi/DPRESI/';
I run the procedure
SQL> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE
2 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_DIR', 'ANGEL.BMP' );
11 DBMS_OUTPUT.put_line ('Abriendo archivo ');
12 dbms_lob.fileopen( l_bfile );
13 DBMS_OUTPUT.put_line ('Cargando archivo ');
14 dbms_lob.loadfromfile( l_clob, l_bfile,
15 dbms_lob.getlength( l_bfile ) );
16 dbms_lob.fileclose( l_bfile );
17* END;
SQL> /
Procedure created.
SQL>
SQL> EXEC load_a_file( 1, 'angel.bmp' )
Abriendo archivo
BEGIN load_a_file( 1, 'angel.bmp' ); END;
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at "BOLINF.LOAD_A_FILE", line 11
ORA-06512: at line 1
July 07, 2006 - 7:16 pm UTC
a) is that directory ON THE SERVER
b) does the ORACLE ACCOUNT running on that server have the ability to navigate that directory.
funny characters in select from clob loaded from file in 10g, but not 9i
Barbara Boehmer, July 05, 2006 - 6:57 pm UTC
Tom,
Please tell me why the select from a clob loaded from a file in 10g produces funny characters, while the same code in 9i on the same machine does not. I have provided a copy and paste of both runs below. Am I missing something or is this a bug? I am running on Windows XP. Please let me know if you need any additional information.
Thank you,
Barbara
SCOTT@10gXE> SELECT banner FROM v$version
2 /
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SCOTT@10gXE> CREATE TABLE your_table
2 (pk NUMBER,
3 clob_field CLOB)
4 /
Table created.
SCOTT@10gXE> INSERT INTO your_table VALUES (1, EMPTY_CLOB())
2 /
1 row created.
SCOTT@10gXE> COMMIT
2 /
Commit complete.
SCOTT@10gXE> CREATE OR REPLACE DIRECTORY my_dir2 AS 'D:\oracle2'
2 /
Directory created.
SCOTT@10gXE> HOST ECHO Hello World > D:\oracle2\testing.txt
SCOTT@10gXE> DECLARE
2 v_clob CLOB;
3 v_bfile BFILE := BFILENAME ('MY_DIR2', 'testing.txt');
4 BEGIN
5 SELECT clob_field
6 INTO v_clob
7 FROM your_table
8 WHERE pk = 1
9 FOR UPDATE;
10 DBMS_LOB.FILEOPEN (v_bfile);
11 DBMS_LOB.LOADFROMFILE (v_clob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
12 DBMS_LOB.FILECLOSE (v_bfile);
13 END;
14 /
PL/SQL procedure successfully completed.
SCOTT@10gXE> SELECT * FROM your_table
2 /
PK CLOB_FIELD
---------- --------------------------------------------------------------------------------
1 ¿¿¿¿¿¿¿
SCOTT@10gXE>
----------------------------------------------------------
scott@ORA92> SELECT banner FROM v$version
2 /
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
scott@ORA92> CREATE TABLE your_table
2 (pk NUMBER,
3 clob_field CLOB)
4 /
Table created.
scott@ORA92> INSERT INTO your_table VALUES (1, EMPTY_CLOB())
2 /
1 row created.
scott@ORA92> COMMIT
2 /
Commit complete.
scott@ORA92> CREATE OR REPLACE DIRECTORY my_dir2 AS 'D:\oracle2'
2 /
Directory created.
scott@ORA92> HOST ECHO Hello World > D:\oracle2\testing.txt
scott@ORA92> DECLARE
2 v_clob CLOB;
3 v_bfile BFILE := BFILENAME ('MY_DIR2', 'testing.txt');
4 BEGIN
5 SELECT clob_field
6 INTO v_clob
7 FROM your_table
8 WHERE pk = 1
9 FOR UPDATE;
10 DBMS_LOB.FILEOPEN (v_bfile);
11 DBMS_LOB.LOADFROMFILE (v_clob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
12 DBMS_LOB.FILECLOSE (v_bfile);
13 END;
14 /
PL/SQL procedure successfully completed.
scott@ORA92> SELECT * FROM your_table
2 /
PK CLOB_FIELD
---------- --------------------------------------------------------------------------------
1 Hello World
scott@ORA92>
July 08, 2006 - 8:47 am UTC
compare your character sets - nls_database_parameters
and - is that file a unicode file.
BLOB
Susana Morales, July 06, 2006 - 2:31 pm UTC
T´s Tom it worked
SQL> select * from demo;
ID THECLOB
--------- --------------------------------------------------------------------------------
1 esta es una pruebita
2 esta es una pruebita
3 esta es una test de 1
4 esta es una test de 2
5 BM:
6 ÿØÿà
7 ÿØÿà
7 rows selected.
You were righ when you wrote BLOB intance CLOB because I need to load a .jpg not a .txt
I run the next proceduere
CREATE TABLE IMAGEBLOBTABLE
(id NUMBER PRIMARY KEY,
imgblob BLOB);
CREATE TABLE IMGTABLE
(id NUMBER PRIMARY KEY,
img ORDSYS.ORDImage);
CREATE DIRECTORY PICTURES AS '/tmp';
GRANT READ ON DIRECTORY PICTURES TO ORDSYS;
GRANT READ ON DIRECTORY PICTURES TO ORDPLUGINS;
CREATE OR REPLACE PROCEDURE load_image_blob(in_id NUMBER,
in_file VARCHAR2) IS
the_blob BLOB;
ctx RAW(64) := NULL;
BEGIN
-- Open the empty BLOB for updating
INSERT INTO IMAGEBLOBTABLE (id, imgblob) VALUES (in_id,EMPTY_BLOB());
SELECT imgblob INTO the_blob FROM IMAGEBLOBTABLE
WHERE id = in_id FOR UPDATE;
-- import the data
ORDSYS.ORDImage.importFrom(ctx,
the_blob,
'FILE',
'PICTURES',
in_file);
UPDATE IMAGEBLOBTABLE SET imgblob = the_blob WHERE id = in_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
Dbms_Output.Put_Line('Operation failed');
Dbms_Output.Put_Line(SQLERRM);
END;
/
SET SERVEROUTPUT ON
EXEC Load_Image_Blob(1,'angel.bmp');
INSERT INTO IMGTABLE (id, img) VALUES (1, ORDSYS.ORDimage.init());
CREATE OR REPLACE PROCEDURE Update_Proc IS
obj ORDSYS.ORDImage;
ctx RAW(64) :=NULL;
BEGIN
-- load the data into the ORDSYS.ORDIMAGE Blob column
UPDATE IMGTABLE T SET T.img.source.localData = (SELECT imgblob FROM
IMAGEBLOBTABLE S WHERE S.id = 1) WHERE T.id=1;
COMMIT;
-- select the row to set the properties and the update time
SELECT img INTO obj FROM IMGTABLE WHERE id = 1 FOR UPDATE;
obj.setProperties();
obj.setUpdateTime(SYSDATE);
-- update the row
UPDATE IMGTABLE SET img = obj WHERE id =2 ;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
Dbms_Output.Put_Line('Operation failed');
Dbms_Output.Put_Line(SQLERRM);
END;
/
EXEC Update_Proc;
When I select this.
SELECT
* FROM IMAGEBLOBTABLE;
1
2
10
11
Only showns the id doesn´t show the other row.
SELECT a.img.getContentLength(),
a.img.getMimeType()
FROM IMGTABLE a
WHERE id = 1;
604830 image/bmp
Shows the size of the image.
Please could you help me with this?. Because in the table
IMAGEBLOBTABLE I onlu can see the ID not the image, and I´m tring to explote that information from Forms and It doest´t work, ORA-00932: inconsistent datatypes: expected got and I did the form with teh wizard.
Thank by your help Tom
July 08, 2006 - 10:20 am UTC
not that I don't believe you --- but..
sorry - I don't believe that select * is doing that.
character set was the problem; method by Gj from UK works; how to change character set?
Barbara Boehmer, July 08, 2006 - 7:02 pm UTC
Tom,
Is there an easy way to change the character set of the database?
As you suspected the character sets were different. The 9i nls_characterset that works is WE8MSWIN1252 and the 10g nls_characterset is AL32UTF8. I was unable to change the 10g character set to match that in 9i by using ALTER DATABASE CHARACTER SET WE8MSWIN1252 since WE8MSWIN1252 is not a superset of AL32UTF8. I did a full export, thinking I would uninstall, reinstall with the new character set, then import. Is there an easier way to change the character set? Then, I changed my mind, and used the method suggested by Gj from UK, as shown below, and it solves this problem. Too bad the conversion isn't automatic. I am undecided about whether I want to change the character set or just handle the conversions explicitly.
So, is there an easy way to change the character set?
Thanks,
Barbara
SYS@10gXE> SELECT banner FROM v$version
2 /
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SYS@10gXE> CREATE TABLE your_table
2 (pk NUMBER,
3 clob_field CLOB)
4 /
Table created.
SYS@10gXE> INSERT INTO your_table VALUES (1, EMPTY_CLOB())
2 /
1 row created.
SYS@10gXE> COMMIT
2 /
Commit complete.
SYS@10gXE> CREATE OR REPLACE DIRECTORY my_dir2 AS 'D:\oracle2'
2 /
Directory created.
SYS@10gXE> HOST ECHO Hello World > D:\oracle2\testing.txt
SYS@10gXE> DECLARE
2 v_clob CLOB;
3 v_bfile BFILE := BFILENAME ('MY_DIR2', 'testing.txt');
4 dest_offset INTEGER := 1;
5 src_offset INTEGER := 1;
6 src_csid NUMBER := NLS_CHARSET_ID ('AL32UTF8');
7 lang_context INTEGER := dbms_lob.default_lang_ctx;
8 warning INTEGER;
9 BEGIN
10 SELECT clob_field
11 INTO v_clob
12 FROM your_table
13 WHERE pk = 1
14 FOR UPDATE;
15 DBMS_LOB.FILEOPEN (v_bfile);
16 DBMS_LOB.LOADCLOBFROMFILE
17 (v_clob,
18 v_bfile,
19 DBMS_LOB.GETLENGTH (v_bfile),
20 dest_offset,
21 src_offset,
22 src_csid,
23 lang_context,
24 warning);
25 DBMS_LOB.FILECLOSE (v_bfile);
26 END;
27 /
PL/SQL procedure successfully completed.
SYS@10gXE> SELECT * FROM your_table
2 /
PK CLOB_FIELD
---------- --------------------------------------------------------------------------------
1 Hello World
SYS@10gXE>
July 08, 2006 - 9:26 pm UTC
if they are are sub/supersets of eachother - a rebuild (and hence, this CONVERSION) is called for.
VIEW A BLOB FROM FORMS
Guss, July 10, 2006 - 12:14 pm UTC
I ron this procedure, now I try to explot the BLOB into a form, I made a form with wizard and it shows this error ORA-00932: inconsistent datatypes: expected got.
Is there any way to do this?
Thanks so much
CREATE TABLE IMAGES_SUS
(
IMAGE_NAME VARCHAR2(30) PRIMARY KEY,
MIME_TYPE VARCHAR2(30),
CONTENT BLOB
)
CREATE OR REPLACE PACKAGE BODY Image_Sus AS
PROCEDURE load(filename VARCHAR2) AS
f_lob BFILE;
b_lob BLOB;
image_name VARCHAR2(30);
mime_type VARCHAR2(30);
dot_pos NUMBER;
BEGIN
dot_pos := INSTR(filename,'.');
image_name := filename; --SUBSTR(filename,1,dot_pos-1);
mime_type := 'image/'||SUBSTR( filename,dot_pos+1,LENGTH(filename) );
INSERT INTO IMAGES_SUS VALUES(image_name,mime_type,EMPTY_BLOB() )
RETURN content INTO b_lob;
f_lob := BFILENAME('SOURCE_DIR',filename);
dbms_lob.fileopen(f_lob,dbms_lob.file_readonly);
dbms_lob.loadfromfile(b_lob,f_lob,dbms_lob.getlength(f_lob) );
dbms_lob.fileclose(f_lob);
COMMIT;
END;
/*=====*/
PROCEDURE get(name VARCHAR2) AS
vblob BLOB;
mime_type VARCHAR2(30);
BEGIN
SELECT content,mime_type
INTO vblob,mime_type
FROM IMAGES
WHERE image_name=name;
owa_util.mime_header(mime_type, FALSE);
owa_util.http_header_close;
wpg_docload.download_file(vblob);
EXCEPTION WHEN OTHERS THEN
htp.p(SQLCODE || SQLERRM);
END;
/*=====*/
END;
/
CREATE DIRECTORY source_dir AS '/tmp';
EXEC Image_susa.load('angel.bmp');
SELECT * FROM IMAGES_SUS
splash.jpg image/jpg
July 10, 2006 - 12:42 pm UTC
please use otn.oracle.com -> discussion forums -> developer forums.
I haven't touched forms since March of 1995.
urf8 to utf16
A reader, July 21, 2009 - 5:12 am UTC
Dear Tom,
we are trying to lead data from a file which is genertaed from ocr system, the character set of our database is utf16 (ar8mswin1256), and the charachterset of the text file is unicode utf8, i used the written procedure above
create or replace
procedure load_a_file( p_id in number,
p_filename in varchar2 )
as
l_clob clob;
l_bfile bfile;
-- ------ Added ----------
dest_offset INTEGER := 1;
src_offset INTEGER := 1;
src_csid NUMBER := NLS_CHARSET_ID('UTF8');
lang_context INTEGER := dbms_lob.default_lang_ctx;
warning INTEGER;
-- ------ Added ----------
begin
insert into demo values ( p_id, empty_clob() )
returning resp into l_clob;
l_bfile := bfilename( 'TMP1', p_filename );
dbms_lob.fileopen( l_bfile );
-- dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength( l_bfile ), );
-- Changed to ----
dbms_lob.loadclobfromfile(l_clob, l_bfile, dbms_lob.getlength( l_bfile ),
dest_offset, src_offset, src_csid, lang_context, warning);
-- Changed to ----
dbms_lob.fileclose( l_bfile );
end;
but still the loaded data i have is rubbish, so how to load data from a utf8 text file to our database (ar8mswin1256 - utf16)?
Browse Button
Oracle_Student, February 18, 2010 - 11:47 pm UTC
Dear,
I required that I have a Push Button at my Form instead I write there whole the path manually why not i browse that file and automatically that load inside an image box.
Kindly send me the code for that. I sure have that but miss place.
would the same solution work for XMLType?
A reader, July 01, 2013 - 4:15 pm UTC
Would the above solution for loading a file into a table with CLOB column work for loading an XML file into a table with XMLType column?
Sean, March 19, 2015 - 6:46 pm UTC
Tom,
We are using a similar procedure based on your load_a_file example with multiple sessions and were getting errors related to max files opened, so we increased session_max_open_files to 50 from 10 and that seemed to solve the issue.
Is there a view which we can use to find out how many files are opened by different sessions?
Thanks.