Menu de navegação

[airoospParticipante]

Boa noite,

Verificando alguns exemplos na internet, criei a trigger abaixo para capturar o que foi executado no banco.

create or replace trigger audit_ddl_trigger_teste
after ddl on database
declare

n number;
stmt varchar2(9000);
S_DDL CLOB;
v_errorcode number :=0;
v_errortext varchar2(200);
sql_text ora_name_list_t;

begin

n := ora_sql_txt(sql_text);

for i in 1..n loop

stmt := stmt || sql_text(i);

end loop;

begin

insert into audit_trail_teste(username,machine,client_ip,os_user,owner,object_name,object_type,action,sql_text,time)
values(ora_login_user,SYS_CONTEXT(‘USERENV’,’HOST’),SYS_CONTEXT(‘USERENV’,’IP_ADDRESS’),
SYS_CONTEXT(‘USERENV’,’OS_USER’),ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,
ora_sysevent,stmt,sysdate);

exception
when others then
begin
v_errorcode:=SQLCODE;
v_errortext:=SUBSTR(SQLERRM,1,200);
end;

end;

select cast(dbms_metadata.get_ddl(ora_dict_obj_type, sys.dictionary_obj_name, sys.dictionary_obj_owner) as varchar2(4000)) into s_ddl from dual;

insert into codigo_ddl(codigo)
values(s_ddl);

end audit_ddl_trigger_teste;

Funciona, o problema ocorre quando alguma package é alterada, ai aparece o erro:

ORA-31600: input value invalid PACKAGE BODY for parameter OBJECT_TYPE function GET_DDL
ORA-06502: PL/SQL: erro: buffer string to small

Alguém tem ideia de como resolver ou já passou por uma situação parecida?

Banco 10g em Windows.

Obrigado

Airton

[José Laurindo ChiappaModerador]

Bom, a primeira coisa que eu vejo é que a mensagem tá clara :

ORA-06502: PL/SQL: erro: buffer string to small

OU SEJA, tem algum buffer (ie, variável ou coluna string ou função de caracter, ou operador de caracter, como o concatenador) que está recebendo mais do que seu limite….

A segunda coisa que me salta aos olhos é esse CAST para varchar2 de um monte de colunas/valores/variáveis de sistema que iirc JÁ SÃO varchar2, como sys.dictionary_obj_name e sys.dictionary_obj_owner : esse negócio de transformar para caracter algo que JÁ É caracter só pode dar inhaca, Não Faz o MENOR dos MENORES SENTIDOS… Simplesmente é ERRADO, imho, vc está Inventando Moda….
O procedimento CORRETO para vc concatenar pedaços de valores string numa string maior (que é o que vc quer ao que entendo) imho é vc usar as FUNCÇÕES DE CARACTER, como SUBSTR, ao invés da maluquice de transformar em string via cast quem já é string…. Sorry….

A terceira coisa é que eu vejo é que vc definiu a variável S_DDL como CLOB : lá na tabela de Auditoria a coluna é CLOB também ??? Se for, Saiba que o procedimento correto pra inserir num LOB é usar a package DBMS_LOB, e ** não ** fazer o INSERT diretamente !!!!

Altere/reveja essas coisas e se precisar nos dê o DDL da tabela E dos objetos também envolvidos, que a gente pode tentar te ajudar mais….

[]s

Chiappa

[airoospParticipante]

Chiappa,

Alterei a linha do select para a linha abaixo:

select dbms_metadata.get_ddl(ora_dict_obj_type, sys.dictionary_obj_name, sys.dictionary_obj_owner) into s_ddl_ from dual;

Na tabela o campo CODIGO é do tipo CLOB.

O erro esta ocorrendo no SELECT, mesmo utilizando uma package body com 49 linhas aparece o erro:

ORA-31600: input value invalid PACKAGE BODY for parameter OBJECT_TYPE function GET_DDL

Obrigado.

Airton

[spernegaParticipante]

Bom dia,

Sem entrar nos méritos do seu procedimento, o erro está no tipo de objeto que está sendo passado.

select dbms_metadata.get_ddl(‘PACKAGE BODY’,’PCK_FINANCEIRO’) “DDL” from dual;
ERROR:
ORA-31600: invalid input value PACKAGE BODY for parameter OBJECT_TYPE in function GET_DDL

select dbms_metadata.get_ddl(‘PACKAGE’,’PCK_FINANCEIRO’) “DDL” from dual;
OK…

O correto é passar PACKAGE e não PACAKAGE BODY.

[airoospParticipante]

Boa tarde,

Fiz a alteração que você falou passando PACKAGE o invés de PACKAGE BODY, mas não funcionou.

Então, criei a procedure abaixo:

create or replace procedure PRC_BUSCAR_OBJETO
(
pTipoObjeto in varchar2,
pNomeObjeto in varchar2,
pOwner in varchar2
)

authid current_user

is

v_fonte clob;
v_objetotipo varchar2(20);

Begin

if pTipoObjeto = ‘PACKAGE BODY’ then

  v_objetotipo:= 'PACKAGE';

else

  v_objetotipo:= pTipoObjeto;

end if;

select dbms_metadata.get_ddl(v_objetotipo,pNomeObjeto,pOwner) into v_fonte from dual;

insert into codigo_ddl(codigo) values(v_fonte);

commit;

end PRC_BUSCAR_OBJETO;

Funcionou sem problemas, mas se chamar a procedure através da trigger mencionada anteriormente, o mesmo erro continua.

Então modifiquei a procedure para utilizar a chamada do GET_DDL através de um EXECUTE IMMEDIATE conforme abaixo:

create or replace procedure PRC_BUSCAR_OBJETO
(
pTipoObjeto in varchar2,
pNomeObjeto in varchar2,
pOwner in varchar2
)

authid current_user

is

v_fonte clob;
v_objetotipo varchar2(20);
clSQL varchar2(300);

Begin

if pTipoObjeto = ‘PACKAGE BODY’ then

  v_objetotipo:= 'PACKAGE';

else

  v_objetotipo:= pTipoObjeto;

end if;

clSQL:=’select dbms_metadata.get_ddl(”PACKAGE”,”’||pNomeObjeto||”’,”’||pOwner||”’) into :codigosql from dual’;

execute immediate clSQL into v_fonte;

insert into codigo_ddl(codigo) values(v_fonte);

commit;

end PRC_BUSCAR_OBJETO;

Se executar a procedure direto funciona, mas ao fazer a chamada da mesma através da trigger conforme linha abaixo, o erro continua.

.
.
.

PRC_BUSCAR_OBJETO(ora_dict_obj_type, sys.dictionary_obj_name, sys.dictionary_obj_owner);

.
.
.

Pelos testes, percebi que a DBMS_METADATA.GET_DDL tem um comportamento quando é executada na procedure mas tem outro comportamento que é executa na trigger (audit_ddl_trigger_teste
after ddl on database).

[spernegaParticipante]

airoosp, qual erro dá durante a execução pela TRIGGER?

Não sei se você fez só pra teste, mas pTipoObjeto está fixo como PACKAGE no seu script.

clSQL:=’select dbms_metadata.get_ddl(”PACKAGE”,”’||pNomeObjeto||”’,”’||pOwner||”’) into :codigosql from dual’;

[airoospParticipante]

Então, mesmo deixando o parâmetro pTipoObjeto, no SELECT aparece o erro:

ORA-31600: invalid input value PACKAGE BODY for parameter OBJECT_TYPE in function GET_DDL

[spernegaParticipante]

Tem que deixar o v_objetotipo, né?
Que é a variável que você está tratando.

[airoospParticipante]

Mas mesmo com a variável o erro ocorre ORA-31600.

É uma trigger AFTER DDL ON DATABASE.

[spernegaParticipante]

Eu tentei reproduzir aqui alguma coisa mais ou menos parecida com o que você está fazendo.

Não deu erro.

Veja se assim dá algum erro pra vocês.

create table codigo_ddl(codigo varchar2(4000))
tablespace dados;

create or replace procedure PRC_BUSCAR_OBJETO (
pTipoObjeto in varchar2,
pNomeObjeto in varchar2,
pOwner in varchar2) authid current_user is
—
v_fonte clob;
v_objetotipo varchar2(20);
clSQL varchar2(300);
—
Begin
if pTipoObjeto = ‘PACKAGE BODY’ then
v_objetotipo:= ‘PACKAGE’;
else
v_objetotipo:= pTipoObjeto;
end if;
—
clSQL:=’select dbms_metadata.get_ddl(‘||””||v_objetotipo||””||’,’||””||pNomeObjeto||”’,”’||pOwner||”’) into :codigosql from dual’;
—
execute immediate clSQL into v_fonte;
—
insert into codigo_ddl(codigo) values(v_fonte);
commit;
end PRC_BUSCAR_OBJETO;
/

create or replace trigger audit_ddl_trigger_teste
after ddl on database
declare
—
v_errorcode varchar2(2000);
v_errortext varchar2(2000);
—
begin
PRC_BUSCAR_OBJETO (ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner);
exception
when others then
v_errorcode:=SQLCODE;
v_errortext:=SUBSTR(SQLERRM,1,200);
end;
/

[airoospParticipante]

Utilizei os exemplos que você passou e ocorreu o erro:

ORA-31603: object “PKGTESTE” type PACKAGE not found on schema “USERTESTE”.

Mas esta package existe neste schema, o que eu fiz foi uma alteração e depois recompilar.

[spernegaParticipante]

bom, pelo menos está mudando o erro…

Vou tentar fazer o teste com outro usuário.

[spernegaParticipante]

Bom dia airoosp,

Não consegui reproduzir com outro usuário.

Faça um teste para ver o comportamento da TRIGGER:

create or replace trigger audit_ddl_trigger_teste
after ddl on database
declare
—
v_errorcode varchar2(2000);
v_errortext varchar2(2000);
—
begin
—
dbms_output.put_line(‘ora_dict_obj_type ‘||ora_dict_obj_type);
dbms_output.put_line(‘ora_dict_obj_name ‘||ora_dict_obj_name);
dbms_output.put_line(‘ora_dict_obj_owner ‘||ora_dict_obj_owner);
—
PRC_BUSCAR_OBJETO (ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner);
exception
when others then
dbms_output.put_line(sqlerrm(sqlcode));
end;
/

Talvez apareça uma luz…

[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 !!!

[Autor]

Posts