Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE & DBMS_LOB (10g , XP)
icon14.gif  UTL_FILE & DBMS_LOB [message #386860] Tue, 17 February 2009 03:19 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member


Is it possible to write a file using dbms_lob instead of UTL_FILE.if u have sample code please post it

Thanks in Advance
Re: UTL_FILE & DBMS_LOB [message #386874 is a reply to message #386860] Tue, 17 February 2009 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the details of dbms_lob in the documentation?

Regards
Michel
Re: UTL_FILE & DBMS_LOB [message #386903 is a reply to message #386874] Tue, 17 February 2009 04:10 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member


 create table test (td clob);

begin
for i in 1..100 loop 
if i=1 then
insert into test select AD_FLTEXTN||i  from pm.print_media where rownum=1;
else
update test set td=td||(select AD_FLTEXTN||i  from pm.print_media where rownum=1);
end if;
end loop;
commit;
end ;
 
declare
   kml UTL_FILE.FILE_TYPE;
   cursor cur is select * from test; 
begin  
   kml:=UTL_FILE.FOPEN('SDO_ROUTER_LOG_DIR','a.txt','A',32767);   
   for rec_test in cur loop
   UTL_FILE.put (kml, rec_test.td);
   end loop;
   utl_file.fclose(kml);   
  end;  
   

Here how can i write a clob field in a table using UTL_FILE ; in this code its giving error
Re: UTL_FILE & DBMS_LOB [message #386913 is a reply to message #386903] Tue, 17 February 2009 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see any error (something starting by ORA-...) in what you posted.
I don't see the relation with your first post/question.

Regards
Michel

[Updated on: Tue, 17 February 2009 04:47]

Report message to a moderator

Re: UTL_FILE & DBMS_LOB [message #386921 is a reply to message #386903] Tue, 17 February 2009 05:05 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member


this is the error code its because the data in td field of table test is clob and its byte length is greater than 32767.How to avoid it

ORA-6502 PL/SQL:numeric or value error
Re: UTL_FILE & DBMS_LOB [message #386923 is a reply to message #386921] Tue, 17 February 2009 05:12 Go to previous message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

I got it


http://www.oracle-base.com/articles/8i/ExportClob.php
Previous Topic: ETL in Oracle 10g (merged 4)
Next Topic: can i display query result in matrix view (merged 4)
Goto Forum:
  


Current Time: Mon Dec 05 04:56:45 CST 2016

Total time taken to generate the page: 0.08899 seconds