web analytics
Skip to primary content
Skip to secondary content

MemoSoup

Mix of the things I always forget

MemoSoup

Main menu

  • Home
  • About
  • Links

Tag Archives: dbms_metadata

Oracle: using DBMS_METADATA for getting table structure

Posted on 29/12/2009 by bigspoon
Reply

set long 5000

select dbms_metadata.get_ddl('TABLE','EMP','SCOTT')
from dual;

Some other values of the type field for dbms_metadata.get_ddl function:

  • INDEX
  • DB_LINK
  • PACKAGE
  • (See also “Oracle® Database PL/SQL Packages and Types Reference“)

    Posted in Oracle | Tagged dbms_metadata, table | Leave a reply

    Oracle: source text for the view, package and other objects

    Posted on 26/10/2009 by bigspoon
    1

    The best method is to use dbms_metadata package:


    select dbms_metadata.get_ddl(upper('&OBJ_TYPE'),upper('&OBJ_NAME'),upper('&OWNER'))
    from dual;

    Additional methods could be also used:
    Source text of the package

    select text
    from dba_source
    where upper(name) like upper('&which_object')
    order by line ;

    Use $ORACLE_HOME/bin/wrap utility to encrypt the package (there is no unwrap)
    (Well, actually, there is unwrap – just look in the search machine for the words unwrap10 or rewrap…)

    Source text of the views

    set long 5000
    col text for a80

    select text from dba_views where view_name = upper('&which_view');
    select query from DBA_mviews where mview_name = upper('&which_view');
    SELECT view_definition FROM v$fixed_view_definition WHERE view_name='&which_view';

    Source text of the synonym

    select
    TABLE_OWNER || '.' || TABLE_NAME
    || decode ( db_link , null , '' , '@' || db_link ) SYNONYM_OBJECT
    from dba_synonyms
    where SYNONYM_NAME = upper('&which_synonym');

    Source text of the trigger

    select
    'create or replace trigger "' || trigger_name || '"'
    || chr(10)|| decode( substr( trigger_type, 1, 1 ), 'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' )
    || chr(10) || triggering_event || chr(10) || 'ON "' || table_owner || '"."' || table_name
    || '"' || chr(10) || decode( instr( trigger_type, 'EACH ROW' ), 0, null, 'FOR EACH ROW' )
    || chr(10) ,
    trigger_body
    from dba_triggers
    where trigger_name = upper('&which_trigger') and owner=upper('&trigger_owner');

    Posted in Oracle | Tagged dbms_metadata, Oracle, source | 1 Reply

    Archives

    • March 2021
    • February 2016
    • February 2013
    • December 2012
    • May 2012
    • April 2012
    • March 2012
    • February 2012
    • January 2012
    • December 2011
    • November 2011
    • July 2011
    • April 2011
    • March 2011
    • February 2011
    • January 2011
    • December 2010
    • July 2010
    • June 2010
    • May 2010
    • April 2010
    • February 2010
    • January 2010
    • December 2009
    • November 2009
    • October 2009
    • September 2009
    • August 2009

    Meta

    • Log in
    Proudly powered by WordPress