Home » RDBMS Server » Server Administration » how to get the ddl of a view (10.2.0.1)
how to get the ddl of a view [message #549952] Wed, 04 April 2012 20:46 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
I can desc the view,but i can not get the ddl of the view,how can i do?

SQL> desc oss03.VW_OSS_PM_MONTH_3_201112;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------

 RECDATE                                   NOT NULL NUMBER(8)
 USERNUMBER                                NOT NULL VARCHAR2(32)
 MODULEID                                  NOT NULL NUMBER(8)
 CHANNELID                                 NOT NULL NUMBER(8)
 OPERID                                    NOT NULL NUMBER(10)
 CONTENTID                                 NOT NULL NUMBER(10)
 SERVICEID                                 NOT NULL NUMBER(10)
 OPERTYPE                                  NOT NULL NUMBER(10)
 PROVCODE                                  NOT NULL NUMBER(5)
 AREACODE                                  NOT NULL NUMBER(5)
 SERVICEITEM                               NOT NULL VARCHAR2(20)
 ORDERTYPE                                 NOT NULL NUMBER(8)
 CARDTYPE                                  NOT NULL NUMBER(8)
 BINDTYPEID                                NOT NULL NUMBER(10)
 SEQNO                                              NUMBER(10)
 TOTALCOUNT                                NOT NULL NUMBER(10)
 EXTCOUNT1                                          NUMBER(10)
 ORIGINID                                  NOT NULL NUMBER(10)
 
 
SQL> Select Dbms_Metadata.Get_Ddl('VIEW','VW_OSS_PM_MONTH_3_201112','OSS03') From dual;
ERROR:
ORA-31603: object "VW_OSS_PM_MONTH_3_201112" of type VIEW not found in schema
"OSS03"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2806
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

Re: how to get the ddl of a view [message #549953 is a reply to message #549952] Wed, 04 April 2012 20:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
  1* select text from user_views where view_name = 'AUTO'
18:50:22 SQL> /

TEXT
--------------------------------------------------------------------------------
select "NAME","PASSENGERS","PRICE" from car

Re: how to get the ddl of a view [message #549954 is a reply to message #549953] Wed, 04 April 2012 20:58 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks BlackSwan,
I want to get the ddl of create view,your way is get the sql of a view.
Re: how to get the ddl of a view [message #549955 is a reply to message #549954] Wed, 04 April 2012 21:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I want to get the ddl of create view,
what is the difference between the above & below
>your way is get the sql of a view.
Re: how to get the ddl of a view [message #549957 is a reply to message #549955] Wed, 04 April 2012 22:17 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks BlackSwan,
Grant the role select_catalog_role can solve the issue.
grant select_catalog_role to myuser;
Re: how to get the ddl of a view [message #549969 is a reply to message #549957] Thu, 05 April 2012 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless to grant this role, you can query all_views.

Regards
Michel
Re: how to get the ddl of a view [message #549978 is a reply to message #549969] Thu, 05 April 2012 02:46 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks Michel and BlackSwan,
What is the really reason about the flowing eror:

SQL> Select Dbms_Metadata.Get_Ddl('VIEW','VW_OSS_PM_MONTH_3_201112','OSS03') From dual;
ERROR:
ORA-31603: object "VW_OSS_PM_MONTH_3_201112" of type VIEW not found in schema
"OSS03"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2806
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
Re: how to get the ddl of a view [message #549985 is a reply to message #549978] Thu, 05 April 2012 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answer is in the package documentation.
Each package comes with a documentation.
Each package documentation comes with a "Security Model" section.
Each person who wants to use a package should read the package documentation BEFORE.

Regards
Michel

[Updated on: Thu, 05 April 2012 03:12]

Report message to a moderator

Re: how to get the ddl of a view [message #549986 is a reply to message #549978] Thu, 05 April 2012 03:17 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're not the owner. You need select_catalog_role to get other users objects.
And you can't use all_views if you want it done properly:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create or replace force view v_test (col1, col2) as
  2  select 1 a, 2 b from dual;

View created.

SQL> desc v_test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               NUMBER

SQL> set long 1000
SQL> SELECT text FROM all_views WHERE view_name = 'V_TEST';

TEXT
--------------------------------------------------------------------------------
select 1 a, 2 b from dual

SQL> Select Dbms_Metadata.Get_Ddl('VIEW','V_TEST','BOB') From dual;

DBMS_METADATA.GET_DDL('VIEW','V_TEST','BOB')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "BOB"."V_TEST" ("COL1", "COL2") AS
  select 1 a, 2 b from dual



SQL> 
Re: how to get the ddl of a view [message #549987 is a reply to message #549985] Thu, 05 April 2012 03:18 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Thu, 05 April 2012 09:12
The answer is in the package documentation.
Each package comes with a documentation.
Each package documentation comes with a "Security Model" section.
Each person who wants to use a package should read the package documentation BEFORE.

And it says you need a role you said not to grant.
Re: how to get the ddl of a view [message #549992 is a reply to message #549987] Thu, 05 April 2012 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You do not need it to get the text from all_views.
SQL> create or replace view v as select * from t;

View created.

SQL> grant select on v to test;

Grant succeeded.

SQL> connect test/test
Connected.
TEST> select text from all_views where owner='MICHEL' and view_name='V';
TEXT
--------------------------------------------------------------------------------------------
select "ID","FLAG" from t

1 row selected.

This is what I said.

Regards
Michel
Re: how to get the ddl of a view [message #549994 is a reply to message #549992] Thu, 05 April 2012 03:47 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And I just demonstrated that all_views can't always give you everything you need to recreate the view.
Re: how to get the ddl of a view [message #549996 is a reply to message #549992] Thu, 05 April 2012 03:50 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi Michel,
you just only get the sql of the view V,how can you get ddl of the view V,just as Cookiemonster.
Re: how to get the ddl of a view [message #549997 is a reply to message #549994] Thu, 05 April 2012 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It gives the text, the columns are in all_tab_columns (and optional constraints in all_constraints), so you have all what you want without giving SELECT_CATALOG_ROLE.

Regards
Michel
Re: how to get the ddl of a view [message #550001 is a reply to message #549997] Thu, 05 April 2012 03:57 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And the force option is stored where?

The correct way to do this is to have the view script in source control, then you don't need to read it out of the DB.
Failing that you should log in as the correct user and use dbms_metadata.

I don't see all_views as being a viable alternative.
Re: how to get the ddl of a view [message #550006 is a reply to message #550001] Thu, 05 April 2012 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Force option is nowhere, it is automatically added by dbms_metadata; for my previous example:
SQL> select dbms_metadata.get_ddl('VIEW','V') from dual;
DBMS_METADATA.GET_DDL('VIEW','V')
----------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "MICHEL"."V" ("ID", "FLAG") AS
  select "ID","FLAG" from t

FORCE is there even if it was not in my CREATE VIEW statement.

My point was just a general security concern: give the least privileges to any account.

Regards
Michel

Re: how to get the ddl of a view [message #550040 is a reply to message #550006] Thu, 05 April 2012 08:05 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I realised your security concern, because I know your general opions on most things oracle.
The OP probably didn't as you didn't explain - well you said it was usless, which is obviously untrue.
If the documentation says use X and you say don't use X, it's generally a good idea to explain why to avoid confusion.
Re: how to get the ddl of a view [message #550044 is a reply to message #550040] Thu, 05 April 2012 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
well you said it was usless, which is obviously untrue


You're right, I was too inaccurate, I should say "it is useless to give a so powerful role to achieve the requirement to get the CREATE VIEW DDL".
I will try to keep it in mind for my next answers.

Regards
Michel
Re: how to get the ddl of a view [message #550126 is a reply to message #550044] Fri, 06 April 2012 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just for fun or any usage, here's a function that returns the view DDL without no more privilege than the one you currently have:
create or replace function get_view_ddl (
  p_view  all_views.view_name%type,
  p_owner all_views.owner%type default USER
  )
return clob
authid current_user
is
  l_ddl    clob;
  l_text   clob;
  l_cursor integer      := dbms_sql.open_cursor;
  l_query  varchar2(200) := 
           'select text from all_views where owner=:o and view_name=:v';
  l_lg     pls_integer   := 32767;
  l_pos    pls_integer   := 0;
  l_rc     pls_integer;
  l_retlg  pls_integer;
  l_sep    varchar2(1)   := ' ';
  l_cons   all_constraints.constraint_name%type;

begin

  -- Initialize DDL string
  l_ddl := 'CREATE OR REPLACE FORCE VIEW "'||p_owner||'"."'||p_view||'" (
';

  -- Add column names
  for rec in (
    select column_name from all_tab_columns
    where owner = p_owner and table_name = p_view
  ) loop
    l_ddl := l_ddl || '  ' || l_sep || '"' || rec.column_name || '"
';
    l_sep := ',';
  end loop;
  l_ddl := l_ddl || '  ) AS 
';

  -- Add text
  dbms_sql.parse (l_cursor, l_query, dbms_sql.native);
  dbms_sql.bind_variable (l_cursor, 'o', p_owner);
  dbms_sql.bind_variable (l_cursor, 'v', p_view);
  dbms_sql.define_column_long (l_cursor, 1);
  l_rc := dbms_sql.execute (l_cursor);
  if dbms_sql.fetch_rows (l_cursor) > 0 then
    loop
      dbms_sql.column_value_long (l_cursor, 1, l_lg, l_pos, l_text, l_retlg);
      l_pos := l_pos + l_retlg;
      exit when l_retlg = 0;
      l_ddl := l_ddl || l_text;
    end loop;
  end if;
  dbms_sql.close_cursor (l_cursor);

  -- Add optional constraint name
  begin
    select constraint_name into l_cons from all_constraints
    where owner=p_owner and table_name=p_view and constraint_type='V';
    if l_cons not like 'SYS_C%' then
      l_ddl := l_ddl || ' constraint '||l_cons;
    end if;
  exception when no_data_found then null;
  end;

  -- End
  l_ddl := l_ddl || '
/';
  return l_ddl;

exception 
  when others then 
    -- DEBUG
    dbms_output.put (dbms_utility.format_error_stack);
    dbms_output.put (dbms_utility.format_error_backtrace);
    -- End of DEBUG
    if dbms_sql.is_open (l_cursor) then
      dbms_sql.close_cursor (l_cursor);
    end if;
    raise;

end get_view_ddl;
/
show error

And with cookiemonster's example:
SQL> create or replace force view v_test (col1, col2) as
  2  select 1 a, 2 b from dual;

View created.

SQL> select get_view_ddl('V_TEST') from dual;
GET_VIEW_DDL('V_TEST')
----------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "MICHEL"."V_TEST" (
   "COL1"
  ,"COL2"
  ) AS
select 1 a, 2 b from dual
/

1 row selected.

Restriction: only relationnal view not object or xmltype one.

Regards
Michel

[Updated on: Fri, 06 April 2012 03:50]

Report message to a moderator

Re: how to get the ddl of a view [message #550210 is a reply to message #550126] Fri, 06 April 2012 22:27 Go to previous message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks!
Previous Topic: ASM: Dropping a newly created disk group
Next Topic: Error message in oracle scheduler
Goto Forum:
  


Current Time: Thu Mar 28 09:02:54 CDT 2024