When using Toad to create a view in Oracle, how can I store the formatted script also?

Svein Bringsli picture Svein Bringsli · Jul 4, 2012 · Viewed 9.5k times · Source

This question may be Toad specific. I have no idea how Oracle stores views, so I'll explain what happens when I use Toad. If I get an answer that is Oracle specific, so much the better.

I have created a rather complex view. To make it clearer, I have formatted the code nicely, and entered some comments where needed. When I need to make changes to the view, I use Toad's "describe objects" window, where I can find a script to recreate the view. The only problem is that all my formatting is gone. Comments before the select keyword (but after "create view xxx as") will also disappear.

If I enter this script to create a view:

create or replace view TestViewFormatting as
-- Here I have a long comment explaining the role of the 
-- view and certain things to be aware of if changing it. 
-- Unfortunately this comment will disappear...
select 
  name,          --This comment will be kept
  accountnumber  --This also
from
  debtable
where
  name like 'S%';

Toad will display this when I describe it later:

DROP VIEW XXX.TESTVIEWFORMATTING;

/* Formatted on 04.07.2012 09:35:45 (QP5 v5.185.11230.41888) */
CREATE OR REPLACE FORCE VIEW XXX.TESTVIEWFORMATTING
(
   NAME,
   ACCOUNTNUMBER
)
AS
   select name,                                    --This comment will be kept
               accountnumber                                       --This also
     from debtable
    where name like 'S%';

Note that the first comment has disappeared, and that the format is totally different.

I suspect that Oracle doesn't store the code of the view, just some parsed version, and when Toad brings up the script, it reverses this parsed version and generates a script on the fly.

What will I have to do to make Toad/Oracle keep the original formatting?

(PS: I know I can change the settings for Toad's code formatter, but this is not what I want to do. Due to some questionable choices in my past, this particular view has several levels of inline views, and I need a very specific formatting to make it clear what happens)

Answer

Florin Ghita picture Florin Ghita · Jul 4, 2012
select text from user_views
where view_name = 'YOUR_VIEW_NAME';

I've tested with:

create view z_v_test as 
select
-- te

--st
* from 
dual;

and it keeps even the blank line.