accept OWNER char prompt 'OWNER: '
accept NAME char prompt 'ENTER THE NAME OF THE PROCEDURE: '
spool &&NAME..sql
set termout off
set linesize 1000
set trimspool on
set verify off
set feedback off
Set heading off
prompt set define off
select decode( type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1', '/'||chr(10),
null) ||
decode(line,1,'create or replace ', '' ) ||
text text
from dba_source
where name = upper('&&NAME') and owner='&&OWNER'
order by type, line;
/
select
'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME ||' to
'||GRANTEE||';'from dba_tab_privs where TABLE_NAME in
(upper('&&NAME')) and OWNER='&&OWNER'
/
select
'create or replace synonym '||OWNER||'.'||SYNONYM_NAME||' for
'||TABLE_OWNER||'.'||TABLE_NAME|| ';' from dba_synonyms where TABLE_NAME
in
(upper('&&NAME')) and TABLE_OWNER in '&&OWNER' and owner !='PUBLIC'
/
select
'create or replace public synonym '||SYNONYM_NAME||' for
'||TABLE_OWNER||'.'||TABLE_NAME|| ';' from dba_synonyms where TABLE_NAME
in
(upper('&&NAME')) and TABLE_OWNER in '&&OWNER' and owner ='PUBLIC'
/
prompt
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100
OU:exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
select dbms_metadata.get_ddl(object_type => 'PACKAGE',name => 'PKG_TESTE',schema => 'DENILSON') from dual;obs: a procedure
dbms_metadata.set_transform_param coloca a "/" no final do comando para criar o objeto.
Fonte:
http://dicasoracledba.blogspot.com.br/2009/10/get-ddl.html
