Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

extract source from dba_views but lines are broken for long statement

WY Yau
Occasional Visitor

extract source from dba_views but lines are broken for long statement

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.
4 REPLIES
Christian Gebhardt
Honored Contributor

Re: extract source from dba_views but lines are broken for long statement

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

Re: extract source from dba_views but lines are broken for long statement

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

Re: extract source from dba_views but lines are broken for long statement

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

Re: extract source from dba_views but lines are broken for long statement

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.