how to get the ddl of a view [message #549952] |
Wed, 04 April 2012 20:46 |
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 #549978 is a reply to message #549969] |
Thu, 05 April 2012 02:46 |
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 |
|
Michel Cadot
Messages: 68732 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 |
cookiemonster
Messages: 13963 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 |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 05 April 2012 09:12The 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 #550001 is a reply to message #549997] |
Thu, 05 April 2012 03:57 |
cookiemonster
Messages: 13963 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 #550040 is a reply to message #550006] |
Thu, 05 April 2012 08:05 |
cookiemonster
Messages: 13963 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 #550126 is a reply to message #550044] |
Fri, 06 April 2012 03:48 |
|
Michel Cadot
Messages: 68732 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
|