extract source from dba_views but lines are broken for long statement

[WY Yau]

I try to extract the source code of a compiled view at table dba_views. However, after I spool out to a text file, the long sentence was broken into different lines, and each line are 80 characters at most. I tried to set linesize, wrap, long but still get the same problem.

I know the column dba_views.text is a LONG type column. It shouldn't truncate the line even the statement is longer than 80 characters.

Highly Appreciated if anyone can help.

[Christian Gebhardt]

Hi
Try this

set long 1000 LONGCHUNKSIZE 1000

If you specify a width for a
LONG field
col format a500 tru
SQL*Plus uses the LONGCHUNKSIZE
or the specified width, whichever is smaller, as the column width.

Hope this will help
Chris

[WY Yau]

Thank you very much, Chris. It is amazing.

However, I also have the same problem when extract source from dba_source for stored procedure. It seems to me that your solution does not work on this case. I think the reason is the column DBA_SOURCE.TEXT is varchar2(4000) not LONG.

Do you have any idea?

[Christian Gebhardt]

Hi
You can specify the length for each field with command column (short form: col)

col text format a4000

means that every field with heading "text" is displayed with length 4000

WIth this command "column" you can also format number and date fields. Look at oracle dcumentations about SQL*PLUS

Chris

[Bill Thorsteinson]

You can also set the column to wordwrap rather than just wrap to produce a usable extract.

We use coding standards that limit the lines to 80 characters for readability purposes. It would also help in cases like this.