How to get whole text from DBA_VIEWS?

[NoName]

I usually use the wonderful program "PL/SQL Develpoer" from
AllAroundAutomation to magage Oracle (tables, views, packages, and so on).
I admit that I rarely use native instructions using SQL*PLUS.

So, I can easily get the source of a view in a couple of clicks, but I dont
know how to get full text from DBA_VIEWS.

That is, if I write the following:

select text from DBA_VIEWS where owner='myowner' and view_name='myview';

the result is the first 255 characters, and not the whole text.
Can someone please give me a clue, where to find the solution on reference
manuals?

Thank you

[Frank]

Post by NoName
I usually use the wonderful program "PL/SQL Develpoer" from
AllAroundAutomation to magage Oracle (tables, views, packages, and so on).
I admit that I rarely use native instructions using SQL*PLUS.
So, I can easily get the source of a view in a couple of clicks, but I dont
know how to get full text from DBA_VIEWS.
select text from DBA_VIEWS where owner='myowner' and view_name='myview';
the result is the first 255 characters, and not the whole text.
Can someone please give me a clue, where to find the solution on reference
manuals?
Thank you

Open an SQL Window, type the query, Execute (F8) and use
Next Page (Alt+PgDn) or Last Page (Alt+End).

[NoName]

Post by Frank
Open an SQL Window, type the query, Execute (F8) and use
Next Page (Alt+PgDn) or Last Page (Alt+End).

Hi Frank,
thank you for your reply,
your suggestion works fine using PL/SQL Developer (I usually right-click the
view name, and choose "view source"), but I wonder how to read the source of
a view without this wonderful tool, simply using the standard SQLplus
command...

Regards

[Frank]

Post by NoName

Post by Frank
Open an SQL Window, type the query, Execute (F8) and use
Next Page (Alt+PgDn) or Last Page (Alt+End).

Hi Frank,
thank you for your reply,
your suggestion works fine using PL/SQL Developer (I usually right-click the
view name, and choose "view source"), but I wonder how to read the source of
a view without this wonderful tool, simply using the standard SQLplus
command...
Regards

As TurkBear replied: set long 1000 (or 2000, or 4000 - depending
on how many characters you want) before you execute the query

[Turkbear]

Post by NoName
I usually use the wonderful program "PL/SQL Develpoer" from
AllAroundAutomation to magage Oracle (tables, views, packages, and so on).
I admit that I rarely use native instructions using SQL*PLUS.
So, I can easily get the source of a view in a couple of clicks, but I dont
know how to get full text from DBA_VIEWS.
select text from DBA_VIEWS where owner='myowner' and view_name='myview';
the result is the first 255 characters, and not the whole text.
Can someone please give me a clue, where to find the solution on reference
manuals?
Thank you

In SqlPLus:

set LONG 1000
select text from DBA_VIEWS where owner='myowner' and view_name='myview';


should do it.

If not , increase the number.

[NoName]

Post by Turkbear
set LONG 1000
select text from DBA_VIEWS where owner='myowner' and view_name='myview';

Ahh, I was missing the SET LONG command!!
So, since the DBA_VIEWS has a column with the text length, using SET LONG
with the value in that column...

Well, thank you, and happy Xmas holydays, to you and everybody here in this
newsgroup!

Best Regards

[Frank]

Post by NoName

Post by Turkbear
set LONG 1000
select text from DBA_VIEWS where owner='myowner' and view_name='myview';

Ahh, I was missing the SET LONG command!!
So, since the DBA_VIEWS has a column with the text length, using SET LONG
with the value in that column...
Well, thank you, and happy Xmas holydays, to you and everybody here in this
newsgroup!
Best Regards

Ehhh - no, actually the text is stored in a datatype LONG
column.

[Turkbear]

Post by Frank

Post by NoName

Post by Turkbear
set LONG 1000
select text from DBA_VIEWS where owner='myowner' and view_name='myview';

Ahh, I was missing the SET LONG command!!
So, since the DBA_VIEWS has a column with the text length, using SET LONG
with the value in that column...
Well, thank you, and happy Xmas holydays, to you and everybody here in this
newsgroup!
Best Regards

Ehhh - no, actually the text is stored in a datatype LONG
column.

You can use the technique NoName used:
============================================================================================
select text_length from all_views where view_name = 'QWHAP_VENDOR_NAME_V';

TEXT_LENGTH
-----------------------
160

SQL> set long 160
SQL> select text from all_views where view_name = 'QWHAP_VENDOR_NAME_V';

TEXT
--------------------------------------------------------------------------------
select "VENDOR_NBR","VNDR_1_NM","VNDR_2_NM","VNDR_3_NM","VNDR_LOCN_NBR","VNDR_NB
R","VNDR_STAT_CD","VNDR_STAT_DESC" from ***@remotedb WITH READ ONLY


SQL>
=================================================================================================

Just enough chars allocated to hold the text.