Criar PACKAGE spec e body

[Renato Menezes Viana]

Pessoal, anexei um arquivo que não consigo catalogar sem dar erro.

Apenas spec também não funciona e apenas body também não funciona.

Onde errei?

[Renato Menezes Viana]

O anexo foi?
---------------

Selecionar tudo

create or replace PACKAGE PACK_SYS_TRACE
AS
FUNCION EXECUTA_SYS_TRACE_ON_OFF (variavel) RETURN BOOLEAN; 

procedure who_trace_me
(dctrace in out varchar2,
qifeitas in out number);

procedure       who_called_me( owner      out varchar2,
                         name       out varchar2,
                         lineno     out number,
                         caller_t   out varchar2, n_called in number ); -- caller_t   out varchar2)
                         
END PACK_SYS_TRACE;

create or replace PACKAGE BODY PACK_SYS_TRACE
as
FUNCION EXECUTA_SYS_TRACE_ON_OFF ( VARIAVEL VARCHAR2 ) RETURN BOOLEAN IS
EXECUTA_SYS_TRACE_LOGICAL BOOLEAN := FALSE;
BEGIN
IF UPPER(VARIAVEL) = 'ON' THEN
EXECUTA_SYS_TRACE_LOGICAL := TRUE;
RETURN (TRUE);
ELSE
EXECUTA_SYS_TRACE_LOGICAL := FALSE;
RETURN (FALSE);
END IF;

END EXECUTA_SYS_TRACE_ON_OFF;


procedure who_trace_me
(dctrace in out varchar2,
qifeitas in out number)
is

PRAGMA AUTONOMOUS_TRANSACTION;



    l_owner     varchar2(30);
    l_name      varchar2(30);
    l_lineno    number;
    l_type      varchar2(30);

    t_owner     varchar2(30);
    t_name      varchar2(30);
    t_lineno    number;
    t_type      varchar2(30);

  sid                NUMBER;
  session_user       VARCHAR2(30);
  db_name            VARCHAR2(30);
  os_user            VARCHAR2(30);
  os_terminal        VARCHAR2(30);
  ip_address         VARCHAR2(30);


    v_number    number;
    v_length    number;
    v_first     number := 0;
    v_last      number := 0;



--*====================================================================
--* Procedure..:  who_trace_me
--* Descricao..:  Registra Descrição do Trace SID.OWNER.PROCESSO requisitante
--*               na Tabela SYS_TRACE com controle de interações.
--*               É um PRAGMA AUTONOMOUS_TRANSACTION onde seu commit não
--*               interfere na lógica da aplicação "em trace".
--*               IMPORTANTE:
--*               ==========
--*               dctrace é um parâmetro IN OUT varchar2 tamanho mínimo(3)/máximo(500); 
--*               sua função é conter dados que auxiliem o registro do Trace
--*               de um Processo (Nº da Inscrição sendo processada);
--*               qifeitas também é um parâmetro IN OUT number;
--*               sua função é informar/controlar as interações feitas E SEU USO
--*               É EXCLUSIVO da who_trace_me, embora seja IN.
--*
--*               AS PRIMEIRAS TRÊS POSIÇÔES do dctrace QUANDO NUMÉRICAS são
--*               utilizadas para controle da Quantidade de Interações (QI). 
--*
--*               Caso QI seja < 2 a procedure NÃO EXECUTA NENHUMA AÇÃO.
--*               Caso DCTRACE tenha menos de 3 posições a procedure NÃO EXECUTA NENHUMA AÇÃO. 
--*
--*               QI NÂO SENDO NUMÉRICA a rotina assume 002 para o USO DEFAULT.
--*
--*               QI sendo diferente de ZEROS, who_trace_me faz no mínimo o INSERT
--*               INICIAL (traced_begin_end = 'B') e o OUTRO INSERT na última interação
--*               (traced_begin_end = 'E').
--*               Assim, por exemplo, sendo QI = 100 e a who_trace_me for executada
--*               1000 vezes, acontece 20 INSERTs, ou seja, 2 INSERTs a cada 100 QI:
--*
--*               1(B)..100(E),101(B)..200(E),....500(B)..501(E),........901(B)..1000(E) 
--*
--*               O USO DEFAULT é visto como aquele que tem por objetivo registrar o
--*               HORÁRIO DE INÍCIO, TÉRMINO e CONDIÇÃO NORMAL DE TÉRMINO, assim a
--*               variável DCTRACE NÃO NECESSITA SER INICIALIZADA e terá a mesma
--*               funcionalidade descrita no código abaixo:  
--*    BEGIN
--*       dctrace := '002';
--*       who_trace_me (dctrace, qifeitas);
--*       select COLUNA_X into v_inscricao from TABELA_X when COLUNA_Y = VARIAVEL_Z;
--*       ...
--*       ...
--*       loop
--*       ...
--*         dctrace_loop := '100 Inscrição:' || v_inscricao;
--*         who_trace_me (dctrace_loop, qi_loop);
--*       end loop;
--*       ...
--*       commit;
--*       pscderro := 'OK';
--*       who_trace_me (dctrace, qifeitas);
--*   END
--*
--*               NOTE que existe um who_trace_me "mais interno" sendo responsável
--*               pelo trace do LOOP e assim usa variáveis próprias para controle
--*               deste "TRACE_LOOP".
--*
--*               Das colunas registradas na BNF_TRACE está a SID (Session ID)
--*               permitindo unificar o registro de uma aplicação independente
--*               da quantidade de OBJETOS chamados.
--*
--* Programa...:  Esta procedure chama who_called_me (QUEM_ME_CHAMOU) para
--*               ter além do OWNER.OBJETO "em trace" ter também
--*               o OWNER.OBJETO ORIGINADOR (CHAMADOR DO "em trace").
--*
--* Data.......:  15/03/2013
--* Autor .....:  Renato Viana
--* Revisões...:
--* Data.......:
--* Autor Rev..:
--*=====================================================================


begin

   IF (NOT pack_sys_trace.EXECUTA_SYS_TRACE_LOGICAL) THEN 
      RETURN;
   END IF;
   
   if dctrace is null then
      dctrace := '002';
      qifeitas := 002;
   end if;  

   if length(dctrace) < 3 then
     return;
   end if; 

   v_length := length(dctrace);
   if v_length > 500 then
     dctrace := substr(dctrace,1,500);
     v_length := length(dctrace);
   end if; 


     BEGIN
     v_number := to_number(substr(dctrace,1,3));
     EXCEPTION
       WHEN OTHERS THEN
         v_number := 002;
         if v_length > 3 then
         dctrace := '002' || substr(dctrace,4,v_length - 3);
         else
         dctrace := '002';
         end if;  
         qifeitas := 002;
     END;


   v_length := length(dctrace);

   if v_number < 2 then
     return;
   end if;

   if qifeitas is null or qifeitas > v_number or qifeitas < 1 then
     qifeitas := v_number;
   end if;

  if qifeitas = v_number then
     v_first := 1;
     qifeitas := qifeitas - 1;
  else


  if qifeitas = 1 then
     v_last := 1;
     qifeitas := v_number;
  end if;

  end if;


  if v_first = 0 and v_last = 0 then
    qifeitas := qifeitas - 1;
    return;
  end if;


      SELECT SYS_CONTEXT( 'USERENV', 'SID' ) INTO sid FROM DUAL;
      SELECT SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) INTO ip_address FROM DUAL;
      SELECT SYS_CONTEXT( 'USERENV', 'TERMINAL' ) INTO os_terminal FROM DUAL;
      SELECT SYS_CONTEXT( 'USERENV', 'OS_USER' ) INTO os_user FROM DUAL;
      SELECT SYS_CONTEXT( 'USERENV', 'DB_NAME' ) INTO db_name FROM DUAL;
      SELECT SYS_CONTEXT( 'USERENV', 'SESSION_USER' ) INTO session_user FROM DUAL;

-- Caso Execução na Web
      if os_user is null then
      SELECT SYS_CONTEXT( 'USERENV', 'HOST' ) INTO os_user FROM DUAL;
      end if; 

      if os_terminal is null then
      SELECT SYS_CONTEXT( 'USERENV', 'SERVER_HOST' ) INTO os_terminal FROM DUAL;
      end if;


  who_called_me( l_owner, l_name, l_lineno, l_type, 4 );
  who_called_me( t_owner, t_name, t_lineno, t_type, 3 );


  INSERT INTO SYS_TRACE
(
DTINICIO
,SID
,CALLED_TRACED_OWNER
,CALLED_TRACED_NAME
,CALLED_TRACED_LINE
,CALLED_TRACED_TYPE
,TRACED_OWNER
,TRACED_NAME
,TRACED_LINE
,TRACED_TYPE
,TRACED_BEGIN_END
,DCTRACE
,SESSION_USER
,DB_NAME
,OS_USER
,OS_TERMINAL
,IP_ADDRESS
,SYS_TRACE_ID
)
VALUES
(
SYSTIMESTAMP
,SID
,l_owner
,l_name
,l_lineno
,l_type
,t_owner
,t_name
,t_lineno
,t_type
,CASE WHEN v_first = 1 THEN 'B' ELSE 'E' END
,dctrace
,session_user
,db_name
,os_user
,os_terminal
,ip_address
,SYS_TRACE_SEQ.nextval
);

commit;
return;

     EXCEPTION
       WHEN OTHERS THEN
         return;

end;
END WHO_TRACE_ME;

procedure       who_called_me( owner      out varchar2,
                         name       out varchar2,
                         lineno     out number,
                         caller_t   out varchar2, n_called in number ) -- caller_t   out varchar2)
as
    call_stack  varchar2(4096); --default dbms_utility.format_call_stack;
    outro varchar2(500);
    n           number;
    found_stack BOOLEAN default FALSE;
    line        varchar2(255);
    cnt         number := 0;
    
--*====================================================================
--* Funcao.....:  who_called_me (QUEM_ME_CHAMOU)
--* Descricao..:  Recupera o nome do OWNER, PROCESSO, LINHA, CHAMADOR corrente
--* Programa...:  Esta função é chamada pela who_am_i (QUEM_SOU_EU) 
--*               que posiciona n_called em 3 (Their Caller);
--*               Caso n_called <> 1, 2, 3 ou 4 no momento return.
--* Tabelas ...:
--* Revisões...:  Revisão da Funcao
--* Data.......:  08/03/2013
--* Autor Rev..:  Renato Viana (Fonte http://glufke.net/)
--* Revisões...:
--* Data.......:
--* Autor Rev..:
--*=====================================================================
       
begin
--
    if n_called is NULL or
      (n_called <> 1 and n_called <> 2 and n_called <> 3 and n_called <> 4) then --
      return;                                                     --
    end if;                                                       --
    
    call_stack := dbms_utility.format_call_stack; -- now default
--
n := instr( call_stack, chr(10) );  
outro := substr( call_stack, 1,500 );



    loop
        n := instr( call_stack, chr(10) );
        exit when ( cnt = n_called or n is NULL or n = 0 ); -- ( cnt = 3 or n is NULL or n = 0 );
--
        line := substr( call_stack, 1, n-1 );
        call_stack := substr( call_stack, n+1 );
--
        if ( NOT found_stack ) then
            if ( line like '%handle%number%name%' ) then
                found_stack := TRUE;
            end if;
        else
            cnt := cnt + 1;
            -- cnt = 1 is ME
            -- cnt = 2 is MY Caller
            -- cnt = 3 is Their Caller
            if ( cnt = n_called ) then -- ( cnt = 3 )
                lineno := to_number(substr( line, 19, 8 )); --line 13, 8 line, 13,6
                line   := substr( line, 29 ); -- line, 23 line, 21
                if ( line like 'pr%' ) then
                    n := length( 'procedure ' );
  
                elsif ( line like 'fun%' ) then
                    n := length( 'function ' );
                elsif ( line like 'package body%' ) then
                    n := length( 'package body ' );
                elsif ( line like 'pack%' ) then
                    n := length( 'package ' );
                elsif ( line like 'anonymous%' ) then
                    n := length( 'anonymous block ' );
                else
                    n := null;
                end if;
                if ( n is not null ) then
                   caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
                else
                   caller_t := 'TRIGGER';
                end if;

                line := substr( line, nvl(n,1) );
                n := instr( line, '.' );
                owner := ltrim(rtrim(substr( line, 1, n-1 )));
                name  := ltrim(rtrim(substr( line, n+1 )));
            end if;
        end if;
    end loop;
end;
END WHO_CALLED_ME;

END PACK_SYS_TRACE;

[Renato Menezes Viana]

Já tentei sem a function e nada
------------------
create or replace PACKAGE PACK_SYS_TRACE
AS

procedure who_trace_me
(dctrace in out varchar2,
qifeitas in out number);

procedure who_called_me( owner out varchar2,
name out varchar2,
lineno out number,
caller_t out varchar2, n_called in number ); -- caller_t out varchar2)

END PACK_SYS_TRACE;

create or replace PACKAGE BODY PACK_SYS_TRACE
as

procedure who_trace_me
(dctrace in out varchar2,
qifeitas in out number)
is

PRAGMA AUTONOMOUS_TRANSACTION;

EXECUTA_SYS_TRACE_LOGICAL BOOLEAN := FALSE;

l_owner varchar2(30);
l_name varchar2(30);
l_lineno number;
l_type varchar2(30);

t_owner varchar2(30);
t_name varchar2(30);
t_lineno number;
t_type varchar2(30);

sid NUMBER;
session_user VARCHAR2(30);
db_name VARCHAR2(30);
os_user VARCHAR2(30);
os_terminal VARCHAR2(30);
ip_address VARCHAR2(30);


v_number number;
v_length number;
v_first number := 0;
v_last number := 0;



--*====================================================================
--* Procedure..: who_trace_me
--* Descricao..: Registra Descrição do Trace SID.OWNER.PROCESSO requisitante
--* na Tabela SYS_TRACE com controle de interações.
--* É um PRAGMA AUTONOMOUS_TRANSACTION onde seu commit não
--* interfere na lógica da aplicação "em trace".
--* IMPORTANTE:
--* ==========
--* dctrace é um parâmetro IN OUT varchar2 tamanho mínimo(3)/máximo(500);
--* sua função é conter dados que auxiliem o registro do Trace
--* de um Processo (Nº da Inscrição sendo processada);
--* qifeitas também é um parâmetro IN OUT number;
--* sua função é informar/controlar as interações feitas E SEU USO
--* É EXCLUSIVO da who_trace_me, embora seja IN.
--*
--* AS PRIMEIRAS TRÊS POSIÇÔES do dctrace QUANDO NUMÉRICAS são
--* utilizadas para controle da Quantidade de Interações (QI).
--*
--* Caso QI seja < 2 a procedure NÃO EXECUTA NENHUMA AÇÃO.
--* Caso DCTRACE tenha menos de 3 posições a procedure NÃO EXECUTA NENHUMA AÇÃO.
--*
--* QI NÂO SENDO NUMÉRICA a rotina assume 002 para o USO DEFAULT.
--*
--* QI sendo diferente de ZEROS, who_trace_me faz no mínimo o INSERT
--* INICIAL (traced_begin_end = 'B') e o OUTRO INSERT na última interação
--* (traced_begin_end = 'E').
--* Assim, por exemplo, sendo QI = 100 e a who_trace_me for executada
--* 1000 vezes, acontece 20 INSERTs, ou seja, 2 INSERTs a cada 100 QI:
--*
--* 1(B)..100(E),101(B)..200(E),....500(B)..501(E),........901(B)..1000(E)
--*
--* O USO DEFAULT é visto como aquele que tem por objetivo registrar o
--* HORÁRIO DE INÍCIO, TÉRMINO e CONDIÇÃO NORMAL DE TÉRMINO, assim a
--* variável DCTRACE NÃO NECESSITA SER INICIALIZADA e terá a mesma
--* funcionalidade descrita no código abaixo:
--* BEGIN
--* dctrace := '002';
--* who_trace_me (dctrace, qifeitas);
--* select COLUNA_X into v_inscricao from TABELA_X when COLUNA_Y = VARIAVEL_Z;
--* ...
--* ...
--* loop
--* ...
--* dctrace_loop := '100 Inscrição:' || v_inscricao;
--* who_trace_me (dctrace_loop, qi_loop);
--* end loop;
--* ...
--* commit;
--* pscderro := 'OK';
--* who_trace_me (dctrace, qifeitas);
--* END
--*
--* NOTE que existe um who_trace_me "mais interno" sendo responsável
--* pelo trace do LOOP e assim usa variáveis próprias para controle
--* deste "TRACE_LOOP".
--*
--* Das colunas registradas na BNF_TRACE está a SID (Session ID)
--* permitindo unificar o registro de uma aplicação independente
--* da quantidade de OBJETOS chamados.
--*
--* Programa...: Esta procedure chama who_called_me (QUEM_ME_CHAMOU) para
--* ter além do OWNER.OBJETO "em trace" ter também
--* o OWNER.OBJETO ORIGINADOR (CHAMADOR DO "em trace").
--*
--* Data.......: 15/03/2013
--* Autor .....: Renato Viana
--* Revisões...:
--* Data.......:
--* Autor Rev..:
--*=====================================================================


begin

IF (NOT pack_sys_trace.EXECUTA_SYS_TRACE_LOGICAL) THEN
RETURN;
END IF;

if dctrace is null then
dctrace := '002';
qifeitas := 002;
end if;

if length(dctrace) < 3 then
return;
end if;

v_length := length(dctrace);
if v_length > 500 then
dctrace := substr(dctrace,1,500);
v_length := length(dctrace);
end if;


BEGIN
v_number := to_number(substr(dctrace,1,3));
EXCEPTION
WHEN OTHERS THEN
v_number := 002;
if v_length > 3 then
dctrace := '002' || substr(dctrace,4,v_length - 3);
else
dctrace := '002';
end if;
qifeitas := 002;
END;


v_length := length(dctrace);

if v_number < 2 then
return;
end if;

if qifeitas is null or qifeitas > v_number or qifeitas < 1 then
qifeitas := v_number;
end if;

if qifeitas = v_number then
v_first := 1;
qifeitas := qifeitas - 1;
else


if qifeitas = 1 then
v_last := 1;
qifeitas := v_number;
end if;

end if;


if v_first = 0 and v_last = 0 then
qifeitas := qifeitas - 1;
return;
end if;


SELECT SYS_CONTEXT( 'USERENV', 'SID' ) INTO sid FROM DUAL;
SELECT SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) INTO ip_address FROM DUAL;
SELECT SYS_CONTEXT( 'USERENV', 'TERMINAL' ) INTO os_terminal FROM DUAL;
SELECT SYS_CONTEXT( 'USERENV', 'OS_USER' ) INTO os_user FROM DUAL;
SELECT SYS_CONTEXT( 'USERENV', 'DB_NAME' ) INTO db_name FROM DUAL;
SELECT SYS_CONTEXT( 'USERENV', 'SESSION_USER' ) INTO session_user FROM DUAL;

-- Caso Execução na Web
if os_user is null then
SELECT SYS_CONTEXT( 'USERENV', 'HOST' ) INTO os_user FROM DUAL;
end if;

if os_terminal is null then
SELECT SYS_CONTEXT( 'USERENV', 'SERVER_HOST' ) INTO os_terminal FROM DUAL;
end if;


who_called_me( l_owner, l_name, l_lineno, l_type, 4 );
who_called_me( t_owner, t_name, t_lineno, t_type, 3 );


INSERT INTO SYS_TRACE
(
DTINICIO
,SID
,CALLED_TRACED_OWNER
,CALLED_TRACED_NAME
,CALLED_TRACED_LINE
,CALLED_TRACED_TYPE
,TRACED_OWNER
,TRACED_NAME
,TRACED_LINE
,TRACED_TYPE
,TRACED_BEGIN_END
,DCTRACE
,SESSION_USER
,DB_NAME
,OS_USER
,OS_TERMINAL
,IP_ADDRESS
,SYS_TRACE_ID
)
VALUES
(
SYSTIMESTAMP
,SID
,l_owner
,l_name
,l_lineno
,l_type
,t_owner
,t_name
,t_lineno
,t_type
,CASE WHEN v_first = 1 THEN 'B' ELSE 'E' END
,dctrace
,session_user
,db_name
,os_user
,os_terminal
,ip_address
,SYS_TRACE_SEQ.nextval
);

commit;
return;

EXCEPTION
WHEN OTHERS THEN
return;

end;
END WHO_TRACE_ME;

procedure who_called_me( owner out varchar2,
name out varchar2,
lineno out number,
caller_t out varchar2, n_called in number ) -- caller_t out varchar2)
as
call_stack varchar2(4096); --default dbms_utility.format_call_stack;
outro varchar2(500);
n number;
found_stack BOOLEAN default FALSE;
line varchar2(255);
cnt number := 0;

--*====================================================================
--* Funcao.....: who_called_me (QUEM_ME_CHAMOU)
--* Descricao..: Recupera o nome do OWNER, PROCESSO, LINHA, CHAMADOR corrente
--* Programa...: Esta função é chamada pela who_am_i (QUEM_SOU_EU)
--* que posiciona n_called em 3 (Their Caller);
--* Caso n_called <> 1, 2, 3 ou 4 no momento return.
--* Tabelas ...:
--* Revisões...: Revisão da Funcao
--* Data.......: 08/03/2013
--* Autor Rev..: Renato Viana (Fonte http://glufke.net/)
--* Revisões...:
--* Data.......:
--* Autor Rev..:
--*=====================================================================

begin
--
if n_called is NULL or
(n_called <> 1 and n_called <> 2 and n_called <> 3 and n_called <> 4) then --
return; --
end if; --

call_stack := dbms_utility.format_call_stack; -- now default
--
n := instr( call_stack, chr(10) );
outro := substr( call_stack, 1,500 );



loop
n := instr( call_stack, chr(10) );
exit when ( cnt = n_called or n is NULL or n = 0 ); -- ( cnt = 3 or n is NULL or n = 0 );
--
line := substr( call_stack, 1, n-1 );
call_stack := substr( call_stack, n+1 );
--
if ( NOT found_stack ) then
if ( line like '%handle%number%name%' ) then
found_stack := TRUE;
end if;
else
cnt := cnt + 1;
-- cnt = 1 is ME
-- cnt = 2 is MY Caller
-- cnt = 3 is Their Caller
if ( cnt = n_called ) then -- ( cnt = 3 )
lineno := to_number(substr( line, 19, 8 )); --line 13, 8 line, 13,6
line := substr( line, 29 ); -- line, 23 line, 21
if ( line like 'pr%' ) then
n := length( 'procedure ' );

elsif ( line like 'fun%' ) then
n := length( 'function ' );
elsif ( line like 'package body%' ) then
n := length( 'package body ' );
elsif ( line like 'pack%' ) then
n := length( 'package ' );
elsif ( line like 'anonymous%' ) then
n := length( 'anonymous block ' );
else
n := null;
end if;
if ( n is not null ) then
caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
else
caller_t := 'TRIGGER';
end if;

line := substr( line, nvl(n,1) );
n := instr( line, '.' );
owner := ltrim(rtrim(substr( line, 1, n-1 )));
name := ltrim(rtrim(substr( line, n+1 )));
end if;
end if;
end loop;
end;
END WHO_CALLED_ME;

END PACK_SYS_TRACE;