Menu de navegação

[airoospParticipante]

Boa tarde,

Pesquisando na internet sobre como buscar o código DDL de um objeto conforme o schema informado, encontrei o script abaixo mas não funciona.
Ao chamar a função, a mesma retorna as informações, mas o campo clob retorna vazio. Já analisei o script para ver se tinha algum erro e não encontrei.

CREATE TYPE ddl_ty AS OBJECT
(
object_name VARCHAR2(30),
object_type VARCHAR2(30),
orig_schema VARCHAR2(30),
orig_ddl CLOB
);

CREATE TYPE ddl_ty_tb AS TABLE OF ddl_ty;

CREATE OR REPLACE FUNCTION get_object_ddl_3(input_values
SYS_REFCURSOR) RETURN ddl_ty_tb PIPELINED IS

PRAGMA AUTONOMOUS_TRANSACTION;

— variables to be passed in by sys_refcursor */
object_name VARCHAR2(30);
object_type VARCHAR2(30);
orig_schema VARCHAR2(30);

— setup output record of TYPE table ddl_ty
out_rec ddl_ty := ddl_ty(NULL,NULL,NULL,NULL);

/* setup handles to be used for setup and fetching metadata
information handles are used to keep track of the different objects
(DDL) we will be referencing in the PL/SQL code */

hOpenOrig NUMBER;
hModifyOrig NUMBER;
hTransDDL NUMBER;
dmsf PLS_INTEGER;
Orig_ddl CLOB;
ret NUMBER;

BEGIN /* Strip off
Attributes not concerned with in DDL. If you are concerned with
TABLESPACE, STORAGE, or SEGMENT information just comment out these few lines. */

dmsf := dbms_metadata.session_transform;
dbms_metadata.set_transform_param(dmsf, ‘TABLESPACE’, FALSE);
dbms_metadata.set_transform_param(dmsf, ‘STORAGE’, FALSE);
dbms_metadata.set_transform_param(dmsf, ‘SEGMENT_ATTRIBUTES’,FALSE);
dbms_metadata.set_transform_param(dmsf, ‘PRETTY’, TRUE);
dbms_metadata.set_transform_param(dmsf, ‘SQLTERMINATOR’, TRUE);

— Loop through each of the rows passed in by the reference cursor
LOOP
/* Fetch the input cursor into PL/SQL variables */
FETCH input_values INTO object_name, orig_schema, object_type;
EXIT WHEN input_values%NOTFOUND;

hOpenOrig := dbms_metadata.open(object_type);
dbms_metadata.set_filter(hOpenOrig,’NAME’,object_name);
dbms_metadata.set_filter(hOpenOrig,’SCHEMA’,orig_schema);

hModifyOrig := dbms_metadata.add_transform(hOpenOrig,’MODIFY’);
dbms_metadata.set_remap_param(hModifyOrig,’REMAP_SCHEMA’,orig_schema,null);

— This states to created DDL instead of XML to be compared
hTransDDL := dbms_metadata.add_transform(hOpenOrig ,’DDL’);

Orig_ddl := dbms_metadata.fetch_clob(hOpenOrig);

out_rec.object_name := object_name;
out_rec.object_type := object_type;
out_rec.orig_schema := orig_schema;
out_rec.orig_ddl := Orig_ddl;
PIPE ROW(out_rec);

— Cleanup and release the handles
dbms_metadata.close(hOpenOrig);

END LOOP;

RETURN;

END get_object_ddl_3;

Fazendo o teste:

SELECT * FROM
TABLE(get_object_ddl_3(CURSOR (SELECT object_name, owner, object_type
FROM dba_objects
WHERE owner = ‘COMPRAS’
AND object_type IN
(‘VIEW’,
‘TABLE’,
‘TYPE’,
‘PACKAGE’,
‘PROCEDURE’,
‘FUNCTION’,
‘SEQUENCE’))));

O Retorno do teste, é:

OBJECT_NAME, OBJECT_TYPE, ORIG_SCHEMA, ORIG_DDL
PRC_CAD_EMP PROCEDURE COMPRAS
FNC_CNPJ FUNCTION COMPRAS
ENVIAR_EMAIL PROCEDURE COMPRAS

Se alguém puder ajudar, agradeço.

Obrigado.

Airton

[José Laurindo ChiappaModerador]

Blz ? Então, pelo jeito é algum bug desse código mas SINCERAMENTE NÃO VEJO necessidade ALGUMA de escrever um PL/SQL complexo pra isso, ainda mais considerando que a package DBMS_METADATA não foi Programada pra isso – nem perid tempo debuganbdo isso não…
Veja o código abaixo, onde consigo o objetivo em questão de obter os DDLs de Todos os objetos de um schema num script sqlplus :

C:UsersForms>type get_schema_ddl.sql
set long 100000
set head off
set echo off
set pagesize 0
set verify off
set feedback off
spool schema.out

select dbms_metadata.get_ddl(object_type, object_name, owner)
from
(
--Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
select
owner,
--Java object names may need to be converted with DBMS_JAVA.LONGNAME.
--That code is not included since many database don't have Java installed.
object_name,
decode(object_type,
'DATABASE LINK', 'DB_LINK',
'JOB', 'PROCOBJ',
'RULE SET', 'PROCOBJ',
'RULE', 'PROCOBJ',
'EVALUATION CONTEXT', 'PROCOBJ',
'PACKAGE', 'PACKAGE_SPEC',
'PACKAGE BODY', 'PACKAGE_BODY',
'TYPE', 'TYPE_SPEC',
'TYPE BODY', 'TYPE_BODY',
'MATERIALIZED VIEW', 'MATERIALIZED_VIEW',
'QUEUE', 'AQ_QUEUE',
'JAVA CLASS', 'JAVA_CLASS',
'JAVA TYPE', 'JAVA_TYPE',
'JAVA SOURCE', 'JAVA_SOURCE',
'JAVA RESOURCE', 'JAVA_RESOURCE',
object_type
) object_type
from dba_objects
where owner = ('SCOTT')
--These objects are included with other object types.
and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',
'LOB','LOB PARTITION','TABLE PARTITION','TABLE SUBPARTITION')
--Ignore system-generated types that support collection processing.
and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')
--Exclude nested tables, their DDL is part of their parent table.
and (owner, object_name) not in (select owner, table_name from dba_nested_tables)
--Exclude overflow segments, their DDL is part of their parent table.
and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')
)
order by owner, object_type, object_name;
spool off
/
exit

C:UsersForms>

==> Vou executar no meu banco de testes :

C:UsersForms>sqlplus CHIAPPA/senha@wteste @get_schema_ddl.sql

==< demora um pouqinho mas ao acabr obtenho os DDLs desejados no arquivo schema.out indicado : C:UsersForms>type schema.out

CREATE DATABASE LINK “DB_LINK_EZ_CONN”
CONNECT TO “CHIAPPA” IDENTIFIED BY VALUES ‘05827B27DED89A976AF3B66DE8FC93BBE7’
USING ‘192.168.10.140:1521/teste’

CREATE UNIQUE INDEX “SCOTT”.”PK_DEPT” ON “SCOTT”.”DEPT” (“DEPTNO”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “USER_DATA”

CREATE UNIQUE INDEX “SCOTT”.”PK_EMP” ON “SCOTT”.”EMP” (“EMPNO”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “USER_DATA”

CREATE UNIQUE INDEX “SCOTT”.”PK_EMP2″ ON “SCOTT”.”EMP2″ (“EMPNO”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “USER_DATA”
…….

okdoc ?? E evidentemente, FACILMENTE vc consegue mudar o DDL que será gerado alterando tablespaces, cláusula de STORAGE e etc setando os parâmetros adequados na chamada à DBMS_METADATA….

[]s

Chiappa

[Autor]

Posts