Trigger DDL after on database erro 31600 bco 10g

[José Laurindo ChiappaModerador]

Blz ?? Então, Airton, pra começo de conversa eu ** lembro ** meio vagamente que vc já tinha tentado escrever algo do tipo em outra thread aqui do fórum, e ficou meio no ar a possibilidade ** CONCRETA ** de bug na versão 10g do DBMS_METADATA – INCLUSIVE, no Suporte Oracle há ** vários bugs ** registrados pra isso, todos listados na nota metalink “Calling Dbms_metadata.Get_ddl From Stored Procedure Results In Ora-31603” (Doc ID 463483.1)…. OKDOC ??

No caso não estou aqui com 10g então não posso testar/confirmar isso mas eu recomendaria que vc testasse tudo que discutimos aqui num banco 11g qquer de teste seu, E se confirmado use como work-around as OUTRAS possibilidades de acesso a código-fonte de PL/SQL (packages no seu caso, mas enfim) , tais como a DBA_SOURCE… Siiiim ??

Isso posto, alguns reparos nesses códigos que andaram rolando nesta thread (todos válidos pra banco acima de 10g, mas Acredito que valem também pra 10g, e COM a obs que vou usar um banco 11gR2 XE ) :

1. eu ** contra-recomendo ** Totalmente códigos a nível de database, pois tal código vai ser disparado INCLUSIVE nas operações internas do database !!! Risco INACEITÁVEL de interferência em performance e em estabilidade, imho….
O Mínimo Aceitável num ambiente que não seja CASA DA MÃE JOANA imho seria que POUCOS e ESCOLHIDOS schemas/usuários tivessem permissão de sair criando PL/SQL em produção, E isso sempre DEPOIS de um code review por parte do DBA…. Assim, se fosse ee essa tal trigger seria criada ON SCHEMA, e nunca ON DATABASE…

2. eu acredito de coração que *** NUNCA *** se deve apelar pro SQL dinâmico sem uma Causa e Motivo *** FORTÍSSIMOS ***, pois com ele vc via de regra PERDE EM PERFORMANCE e obtém uma Complexidade Muito muito **** MUITO **** maior…. Então vou fazer aqui sem SQL dinâmico, E no schema SYS com AUTHID CURRENT_USER, cfrrme recomendado na nota que indiquei :

SYS@XE:SQL> create or replace procedure PRC_BUSCAR_OBJETO (
2 pTipoObjeto in varchar2,
3 pNomeObjeto in varchar2,
4 pOwner in varchar2) authid current_user is
5 —
6 v_fonte clob;
7 v_objetotipo varchar2(20);
8 clSQL varchar2(300);
9 —
10 Begin
11 if pTipoObjeto = ‘PACKAGE BODY’ then
12 v_objetotipo:= ‘PACKAGE’;
13 else
14 v_objetotipo:= pTipoObjeto;
15 end if;
16 —
17 select dbms_metadata.get_ddl(v_objetotipo, pNomeObjeto, pOwner) into v_fonte from dual;
18 dbms_output.put_line(‘Fonte=’ || v_fonte || ‘!!!’);
19 —
20 — execute immediate clSQL into v_fonte;
21 —
22 — insert into codigo_ddl(codigo) values(v_fonte);
23 — commit;
24 end PRC_BUSCAR_OBJETO;
25 /

Procedimento criado.

==> vou fazer um teste com uma package que eu já tinha :

SYS@XE:SQL> set PAGES 50000 lines 133 long 500000
SYS@XE:SQL> set serveroutput on size 1000000
SYS@XE:SQL> exec PRC_BUSCAR_OBJETO(‘PACKAGE’, ‘CUST_SAL’, ‘HR’);

Fonte=
CREATE OR REPLACE PACKAGE “HR”.”CUST_SAL” AS
x number;
y date;

PROCEDURE find_sal(c_id employees.EMPLOYEE_ID%type);
END cust_sal;
CREATE OR
REPLACE PACKAGE BODY “HR”.”CUST_SAL” AS

PROCEDURE find_sal(c_id
employees.EMPLOYEE_ID%TYPE) IS
c_sal employees.salary%TYPE;
BEGIN

SELECT salary INTO c_sal FROM employees WHERE EMPLOYEE_ID = c_id;

dbms_output.put_line(‘Salary: ‘|| c_sal);
END find_sal;
END cust_sal;!!!

Procedimento PL/SQL concluído com sucesso.

SYS@XE:SQL>

3. Vc até pode inserir diretamente numa coluna CLOB de uma tabela via comando INSERT, já que nos releases mais recentes o PL/SQL automaticamente converte CLOB para VARCHAR2, ** desde que ** o CLOB seja menor que 32k, o limite para strings varchar2 no PL/SQL : vou fazer isso aqui no meu exemplo, mas como eu disse vc pode ter problemas com essa conversão implícita, imho o Melhor em termos de Segurança seria usa a API de LOBs, ie, as rotinas contidas na package do sistema chamada DBMS_LOB…
Mas vamos lá :

system@XE:SQL> create table codigo_ddl(codigo varchar2(4000));

Tabela criada.

==> Criei no schema SYSTEM (que no meu banco tá alterado pra ter como DEFAULT TABLESPACE uma tablespace própria) justamente pra não consumir espaço na tablespace do SYS…
OBSERVE TAMBÉM que se a Procedure contém DMLs e precisa fazer COMMIT, é ** CONCEITUAL ** que vc precisa de AUTONOMOUS TRANSACTION, pois um COMMIT *** encerra a Transação *** e um trigger Não Pode Encerrar transação !!!! Isso é TOTALMENTE DOCUMENTADO!!! INCLUSIVE, talvez seja Isso que (meio que Erroneamente, imho) se tentou ‘consertar’ com SQL dinâmico…
Então Observe que vou implementar a exigida Transação Autônoma :

SYS@XE:SQL>create or replace procedure PRC_BUSCAR_OBJETO (
2 pTipoObjeto in varchar2,
3 pNomeObjeto in varchar2,
4 pOwner in varchar2) authid current_user is
5 —
6 PRAGMA AUTONOMOUS_TRANSACTION; — Para permitir COMMITs em rotinas chamadas pro Triggers !!!
7 v_fonte clob;
8 v_objetotipo varchar2(20);
9 clSQL varchar2(300);
10 —
11 Begin
12 if pTipoObjeto = ‘PACKAGE BODY’ then
13 v_objetotipo:= ‘PACKAGE’;
14 else
15 v_objetotipo:= pTipoObjeto;
16 end if;
17 —
18 select dbms_metadata.get_ddl(v_objetotipo, pNomeObjeto, pOwner) into v_fonte from dual;
19 dbms_output.put_line(‘Fonte=’ || v_fonte || ‘!!!’);
20 —
21 — execute immediate clSQL into v_fonte;
22 —
23 insert into system.codigo_ddl(codigo) values(v_fonte);
24 commit;
25 end PRC_BUSCAR_OBJETO;
26 /

Procedimento criado.

==> É só criar a Trigger que vai chamar a procedure : vou criar ON DATABASE só para exemplo, mas já disse acima que Não Gosto Disso…
E um ponto *** CRÍTICO ***, que afaik é o Ponto principal que está pegando aqui : a DBMS_METADATA trabalha acessando as VIEWS E TABELAS INTERNAS DO ORACLE, então o usuário que está conectado no banco e fazendo o DDL ** TEM ** que ter acesso a elas todas!!! idem pra tabela a guardar o código…

SYS@XE:SQL>grant select any dictionary to HR;

Concessão bem-sucedida.

SYS@XE:SQL>grant select on SYSTEM.codigo_ddl to HR;

Concessão bem-sucedida.

SYS@XE:SQL>grant insert on SYSTEM.codigo_ddl to HR;

Concessão bem-sucedida.

SYS@XE:SQL>

SYS@XE:SQL> create or replace trigger audit_ddl_trigger_teste
2 after ddl on database
3 declare
4 —
5 v_errorcode varchar2(2000);
6 v_errortext varchar2(2000);
7 —
8 begin
9 SYSTEM.PRC_BUSCAR_OBJETO (ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner);
10 exception
11 when others then
12 v_errorcode:=SQLCODE;
13 v_errortext:=SUBSTR(SQLERRM,1,200);
14 end;
15 /

Gatilho criado.

SYS@XE:SQL>

hr@XE:SQL> CREATE OR REPLACE PACKAGE “HR”.”CUST_SAL” AS
2 x number;
3 y date;
4 PROCEDURE find_sal(c_id employees.EMPLOYEE_ID%type);
5 END cust_sal;
6 /

Pacote criado.

hr@XE:SQL>CREATE OR REPLACE PACKAGE BODY “HR”.”CUST_SAL” AS
2
3 PROCEDURE find_sal(c_id employees.EMPLOYEE_ID%TYPE) IS
4 c_sal employees.salary%TYPE;
5 BEGIN
6 SELECT salary INTO c_sal FROM employees WHERE EMPLOYEE_ID = c_id;
7 dbms_output.put_line(‘Salary: ‘|| c_sal);
8 END find_sal;
9 END cust_sal;
10 /

Corpo de Pacote criado.

hr@XE:SQL>

===> resultado :

SYS@XE:SQL>select * from SYSTEM.codigo_ddl;

CODIGO
——————————————————————————–

CREATE OR REPLACE PACKAGE “HR”.”CUST_SAL” AS
x number;
y date;
PROCEDURE find_sal(c_id employees.EMPLOYEE_ID%type);
END cust_sal;
CREATE OR REPLACE PACKAGE BODY “HR”.”CUST_SAL” AS

PROCEDURE find_sal(c_id employees.EMPLOYEE_ID%TYPE) IS
c_sal employees.salary%TYPE;
BEGIN
SELECT salary INTO c_sal FROM employees WHERE EMPLOYEE_ID = c_id;
dbms_output.put_line(‘Salary: ‘|| c_sal);
END find_sal;
END cust_sal;

CREATE OR REPLACE PACKAGE “HR”.”CUST_SAL” AS
x number;
y date;
PROCEDURE find_sal(c_id employees.EMPLOYEE_ID%type);
END cust_sal;
CREATE OR REPLACE PACKAGE BODY “HR”.”CUST_SAL” AS

PROCEDURE find_sal(c_id employees.EMPLOYEE_ID%TYPE) IS
c_sal employees.salary%TYPE;
BEGIN
SELECT salary INTO c_sal FROM employees WHERE EMPLOYEE_ID = c_id;
dbms_output.put_line(‘Salary: ‘|| c_sal);
END find_sal;
END cust_sal;

SYS@XE:SQL>

[]s

Chiappa

====>>> IMPORTANTE : friso de novo, esse código Não Está NEM DE LONGE próximo de qualidade produção, é só um EXEMPLO !!!