DBMS_OUTPUT Character Size 3

[asth01]

How to increase the number of characters that DBMS_OUTPUT.PUT_LINE can display?

Currently when I am trying to display beyond 255, it gives me error.

Thanks

 

[sem]

Why do you need it? If you plan to use DBMS_OUTPUT for reporting I earnestly recommend you to switch to bind variables + print command construct.

Regards, Dima

http://seminihin.narod.ru

 

[asth01]

Could you provide some example...
that will replace DBMS_OUTPUT.PUT_LINE (var1) with PRINT..

 

[asth01]

I didn't get the answer...
Error in DBMS_OUTPUT.PUT_LINE is ....Exceeding 255 characters....what is the workaround?

 

[SantaMufasa]

Asth,

I recommend using Oracle's "utl_file" package, built specifically for flat file reading, writing, and appending. Here is a sample routine that shows how to open, write, and close a flat file:

declare
	FileOut	utl_file.file_type;
Begin
FileOut	:= utl_file.fopen('D:\dhunt\sqldba','Asth.txt','W');
for r in (select * from s_region)
		Loop
	utl_file.put_line(FileOut,r.id||': '||r.name);
end loop;
utl_file.fclose(FileOut);
end;
/

1: North America
2: South America
3: Africa / Middle East
4: Asia
5: Europe

With "utl_file", your lines can be as long as you need.

Let us know if this solves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:01 (19Jan04) GMT, 12:01 (19Jan04) Mountain Time)

 

[sem]

Asth01, the first example raises an error, the second works

declare
str varchar2(256);
begin
for i in 1..256 loop
str := str || '1';
end loop;
dbms_output.put_line(str);
end;
----------------------------
var str varchar2(256)
set lines 256

begin
for i in 1..256 loop
:str := :str || '1';
end loop;
end;

print str
--------------------------
Obviously you may change 256 by bigger number



Dave, as a DBA you can not even imagine that somebody may have no access to server FS Unfortunately for most ordinary developers this approach is completely unsuitable, because they have no such access and can not mount their local FS's via network.

Regards, Dima

http://seminihin.narod.ru

 

[asth01]

I tried it by SET LINES 256, but the procedure is not compiling.
Are you sure we can give this SET LIBE 256 inside a procedure?

Thanks

 

[sem]

Asth01 , if your last question is for me, the answer is not sure. Moreover, I'm sure in the opposite. I don't suggest to place it (as well as variable declaration) into PL/SQL code: these are SQL*PLUS specific commands, they should be called outside pl/sql though within the same script.

Regards, Dima

http://seminihin.narod.ru

 

[SirCharles]

You could pass the large string to a function that parses it into 256 char parts, in a while loop, or recursive function, and uses the dbms_output function to put out the string in pieces.

 

[asth01]

Reply to SantaMufasa,

Hi Mufasa,

In the line:
FileOut := utl_file.fopen('D:\dhunt\sqldba','Asth.txt','W');


What is this 'D:\dhunt\sqldba' and Asth.txt and why they are needed?

Thanks,

 

[SantaMufasa]

Asth,

The "utl_file.fopen" packaged function produces a RESULT called a "file handle". To produce the file handle, the function needs three incoming arguments:

1) the path of the flat file,
2) the name of the flat file, and
3) a 'R', 'W', or 'A' to specify whether you intend to 'Read' (an existing file), 'Write' (to a new file, or overwrite an existing file), or 'Append' (to an existing file).

We can then use the resulting file handle as an argument to other "utl_file" packaged procedures (such as "utl_file.put_line" and "utl_file.fclose" in my sample code, above).

Does this answer your good question?

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:02 (22Jan04) GMT, 14:02 (22Jan04) Mountain Time)

 

[asth01]

I tried to run this and got error:

CREATE OR REPLACE FUNCTION DISPLAY_MESSAGE2 (
OUTPUT_TEXT IN VARCHAR2
) RETURN NUMBER IS

-- DECLARE
FileOut UTL_FILE.FILE_TYPE;
BEGIN

FileOut := UTL_FILE.FOPEN('D:\','Asth.txt','W');
UTL_FILE.PUT_LINE(FileOut,output_text);
UTL_FILE.FCLOSE(FileOut);

RETURN LENGTH (OUTPUT_TEXT);

END display_message2;
/

Unhandled user Exception error at line... line 9 .. line 8....

Don't know why it's pointing to 2 lines...

Thanks

 

[BJCooperIT]

Has the UTL_FILE parameter been setup for use? Also, do you have read/write privileges in the UTL_FILE directory?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[asth01]

How can I check these...

 

[SantaMufasa]

Asth,

When using the &quot;UTL_FILE&quot; flat-file-processing package for output, as BJ so aptly reminds, the Oracle Server you are using must be aware 1) that it has &quot;permission&quot; to write flat-file output and 2) which path(s) to which it has write capablility. Since we use UTL_FILE so extensively on our servers, we give Oracle write access to whatever directory our developers choose. To give this permission, the DBA sets the following instance parameter in the init<SID>.ora parameter file:

UTL_FILE = *

The above means &quot;allow UTL_FILE-package write to ANY visible path.&quot; If you wish to limit the paths to which UTL_FILE can write, replace the &quot;*&quot; with explicit path name(s). (Separate path names with commas if there are multiple names.)

If you have access to the Oracle &quot;V$...&quot; tables, you can determine the current setting of UTL_FILE with my favourite parameter-checking code (which I store in a file named &quot;parms.sql&quot;:

col p heading "Parameter" format a41
col v heading "Value" format a50 word wrap
set verify off
accept parm prompt &quot;Enter a (partial) parameter name to display (or <enter> for all): 
select name p, value v from v$parameter
where name like lower('%&parm%')
order by name;

@parms
Enter a (partial) parameter name to display (or <enter> for all): utl

Parameter                                 Value
----------------------------------------- ---------------------------
utl_file_dir                              *

Let us know if this resolves your needs.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:28 (23Jan04) GMT, 09:28 (23Jan04) Mountain Time)