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

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

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. 

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

I want to discuss the very last comment posted by me and above the irrelevant followup.

Please read my post and evaluate it.

Please provide the desired output for your last post?

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.

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

seems to be a bad link:

"but you'll definitely want to read:

</code> http://download-east.oracle.com/docs/cd/F49540_01/DOC/java.815/a64685/oraext4.htm#1003223 <code>

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

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 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

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

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.

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.

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.

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
 
 

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!!

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?

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.

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;
/

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.

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

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

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

Thank you so much Tom. That was resolved my issue.

/*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
 

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 

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? 

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



 

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>

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
 

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>

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

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)?

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 above solution for loading a file into a table with CLOB column work for loading an XML file into a table with XMLType column?

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.