Home » SQL & PL/SQL » SQL & PL/SQL » dbms_output.put_line
dbms_output.put_line [message #376149] Tue, 16 December 2008 05:29 Go to next message
bhangale.parag
Messages: 11
Registered: December 2008
Location: culcutta
Junior Member
hi all,
dbms_output.put_line having max limit is 1000000 but my clob result is near about 500 MB so how can i manage it can you please give me any idea???????????????
Re: dbms_output.put_line [message #376158 is a reply to message #376149] Tue, 16 December 2008 05:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why would you ever want to use DBMS_OUTPUT to display that much data?

DBMS_OUTPUT is intended as a lightweight text output device, useful for debugging, and sometimes for output from utility programs like UT-Pl/SQL.
For any other purpose, there are better things to use.

I'd use either UTL_FILE if I wanted to write it to a file on disc, or a front end like Forms that could handle large text fields if I wanted to display it.

If you tell us what you're trying to do, then we may be able to proovide more detailed help.
Re: dbms_output.put_line [message #376160 is a reply to message #376158] Tue, 16 December 2008 05:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Why would you ever want to use DBMS_OUTPUT to display that much data?



Because according to his other thread it seems he is afaid of asking the DBA to grant him enough privileges to use UTL_FILE.

Hence he is trying to hammer in a nail with a toothbrush. Very Happy
Re: dbms_output.put_line [message #376200 is a reply to message #376149] Tue, 16 December 2008 08:15 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi ,

What is the OS type, Is it windows family or UNIX.

Thanks
Trivnedra
Re: dbms_output.put_line [message #376202 is a reply to message #376200] Tue, 16 December 2008 08:17 Go to previous messageGo to next message
bhangale.parag
Messages: 11
Registered: December 2008
Location: culcutta
Junior Member
hi,
Unix........

Thanks
Parag

[Updated on: Tue, 16 December 2008 08:23]

Report message to a moderator

Re: dbms_output.put_line [message #376210 is a reply to message #376200] Tue, 16 December 2008 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
trivendra wrote on Tue, 16 December 2008 15:15
Hi ,

What is the OS type, Is it windows family or UNIX.

Thanks
Trivnedra

In what this question will help in any solution to the problem?

Regards
Michel

Re: dbms_output.put_line [message #376211 is a reply to message #376149] Tue, 16 December 2008 08:38 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi,

As mentioned above UTL_FILE package has restriction.

I used some simple example of spooling the file on local machine.

This work fine for both the OS : UNIX and Windows Family.


a) Create a folder On you machine on some Location, for my machine it is

C:\TEMP\Trivendra\Example

b) Create a file with the some, like execute.sql

Copy and paste the following script in this file.

set echo off
set linesize 1000
set verify off
set feedback off
set heading off
set head off
set trim on
set trimspool on
set sqlprompt ''
spool C:\TEMP\Trivendra\Example\result.txt
select level from dual connect by level <=10;
spool off;


Here you can put your query which has CLOB column.


b) Create a batch file with some name, like run.bat

copy and paste this code in run.bat batch file.

sqlplus scott@skype @C:\TEMP\Trivendra\Example\execute.sql


c) Just Run the batch file, you will find your data in you spool location, and here it is Result.txt.

Thanks
Trivendra
Re: dbms_output.put_line [message #376212 is a reply to message #376211] Tue, 16 December 2008 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does this work for a CLOB of 500MB?

Regards
Michel


Re: dbms_output.put_line [message #376214 is a reply to message #376149] Tue, 16 December 2008 08:43 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
I have not tested for this much of data , but for 100~150MB i have tested on UNIX box.
Re: dbms_output.put_line [message #376216 is a reply to message #376214] Tue, 16 December 2008 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but for 100~150MB i have tested on UNIX box.

In one value? And with OP version?

Regards
Michel

[Updated on: Tue, 16 December 2008 08:47]

Report message to a moderator

Re: dbms_output.put_line [message #376221 is a reply to message #376149] Tue, 16 December 2008 08:53 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
OS : SunOS 5.10
File size 136 MB
Re: dbms_output.put_line [message #376222 is a reply to message #376221] Tue, 16 December 2008 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not file size, one value of one column of one row?

Regards
Michel
Re: dbms_output.put_line [message #376228 is a reply to message #376149] Tue, 16 December 2008 09:05 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi Michel,

This is just an small example for given problem. The given query will populate all rows in the file for single column.

Thanks
Trivenda

[Updated on: Tue, 16 December 2008 09:06]

Report message to a moderator

Re: dbms_output.put_line [message #376237 is a reply to message #376228] Tue, 16 December 2008 09:56 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No problem, I just wanted to know if you did it for such a signle value.

Regards
Michel
Previous Topic: calling another procedure in a stored procedure
Next Topic: ORA-01858
Goto Forum:
  


Current Time: Sun Dec 11 02:10:06 CST 2016

Total time taken to generate the page: 0.07864 seconds