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

Question

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)

As far as I can tell Oracle removes any comment before the actual select. So you won't be able to preserve that. The rest of the source is retained as sent by the SQL client. I don't know TOAD though, so I have no idea how you can configure it to display the original code — , Jul 04 '12 at 08:19
@Florin: Yes, that's what I do now, but that's easy to fix. The real problem is the formatting. — Svein Bringsli, Jul 04 '12 at 10:35
@SveinBringsli The only way to keep the formatting I know is keeping a copy of the view is some version control system and forcing everyone to use it while modifying the view. — Marek Grzenkowicz, Aug 30 '13 at 07:07

Florin Ghita · Accepted Answer · 2012-07-05T07:14:39.737

5

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.

edited Jul 05 '12 at 07:14

answered Jul 04 '12 at 13:05

Florin Ghita

17,525
6
57
76

I had to change "name" to "view_name". I don't know if this has to do with Oracle versions, or if it was was a typo. Anyway, it worked as hoped. Thanks :-) – Svein Bringsli Jul 05 '12 at 06:26

score 2 · Answer 2 · answered Jul 05 '12 at 07:36

2

Another way is to use DBMS_METADATA:

select dbms_metadata.get_ddl('VIEW', 'YOUR_VIEW_NAME', user) from dual

This works not only for views, but also for (nearly) all kind of database objects (tables, triggers, functions, ...).

answered Jul 05 '12 at 07:36

Frank Schmitt

30,195
12
73
107

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

2 Answers2