Wednesday, March 11, 2015

More on Get Source Code for Oracle Database Objects

The following are the three ways to get the source code for Oracle database objects such as views, materialized views, procedures, functions, synonyms, database links etc.
1. For views, we query user_views. This is described in Get the source code for Oracle database views and materialized view
2. For materialized view, we query user_mviews. This is also described in Get the source code for Oracle database views and materialized view.
3. For objects, including views and materialized views, we can use dbms_meta.get_ddl(). See Generate SQL Create Table/View Queries for Existing Tables/Views, SQL Scripts for Oracle Database Link and Hide PL/SQL Scripts For Function or Procedure For example, the following query returns the "create table" statement.

SQL> select dbms_metadata.get_ddl('TABLE', 'TBL_A') from dual;

DBMS_METADATA.GET_DDL('TABLE','TBL_A')
--------------------------------------------------------------------------------

  CREATE TABLE "PROD"."TBL_A"
   (    "ID" NUMBER,
        "VALUE" VARCHAR2(64),
        "VALUE2" VARCHAR2(32)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PROD"

No comments: