quinta-feira, 15 de outubro de 2009

GET DDL

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.

Nenhum comentário:

Postar um comentário

Docker Remove ALL Containers and Images

Caution: This command destroy ALL images and Containers. Recovery only possible if you have backed up properly. - Delete all containers ...