The best method is to use dbms_metadata package:
SELECT dbms_metadata.get_ddl(UPPER('&OBJ_TYPE'),UPPER('&OBJ_NAME'),UPPER('&OWNER'))
FROM dual;
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 ;
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';
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');
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');
'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');
select text from OBJECT_SOURCE where owner = ‘SCHEMA_OWNER’ and name = ‘OBJECT_NAME’;
When we use this query, we are getting text in grid format, how to get the data in text format?