Objeto na memoria

[marcelo0906]

Bom dia!

tenho mais uma dúvida e não sei como resolver sou iniciante em oracle.


coloquei um execute immediate dentro de uma estrutura de loop a estrutura é mais ou menos essa;


1º Loop

execute immediate NNNNN
2º Loop

execute immediate yyyyyy
fim 2º loop

fim 1º loop



me falaram que quando se usa execute immediate dentro do loop ele não utiliza o mesmo espaço de memória, ele sempre cria um novo espaço e não destrói o anterior.

teria como tratar isso?? qual comando para destroir o espaço da memoria antes de executar o proximo comando ???

não sei se fui muito claro.

obrigado

[diegolenhardt]

uhmm veja se esta usando bind variables, dessa forma é mantido em buffer cache,

?, ?, ? etc

posta o codigo que fica mais facil..

[fsitja]

Vamos partir da sua necessidade. Porque você está usando "Execute Imeddiate"?
Na verdade, usar execute immediate não é recomendado para comandos SQL "normais", como regra geral use o execute immediate para SQL dinâmico.

Por SQL dinâmico entenda-se aqueles que você não sabe quais colunas você quer buscar, de qual tabela ou quais colunas você quer filtrar no where. Execute immediate é também uma forma de implementar DDL (alter table, create index, etc...) em procedures, mas evite a prática.

Ainda assim, na maioria dos casos o melhor é usar cursor variables com OPEN cursor FOR "select NNNNN from ..." no lugar de execute immediate.

SQL dinâmico é nocivo aos seus fios de cabelo e sanidade, alémde ser um grande culpado por problemas com SQL injection, que tenho certeza que você já ouviu falar. Se for realmente necessário há cuidados específicos de segurança e performance, um deles é bind variable como o Diego já apontou. Dependendo da sua necessidade podemos apontar outras alternativas.

Isso que lhe falaram não está bem contado acho... parece faltando uma parte da história. Com código dá para ajudar mais, como já disse o Diego.

[marcelo0906]

Estou usando o execue immediate para fazer sql dinâmico ai vai o codigo é um sistema de limpeza de dados de uma base, serve para limpar os dados com uma data menor que atual menos uma certa quantidade de dias ai vai o código:

Selecionar tudo

create or replace
PROCEDURE P_LIMPDADOSGO AS

v_SQL        VARCHAR2(256); 
v_SQLDEL     VARCHAR2(256); 
v_Idc1       NUMBER; 
v_Idc2       NUMBER;
v_dtSys      VARCHAR2(50);

TYPE Ty_RecordTabConfig IS RECORD  (   
       v_NomeTabela      VARCHAR2(50), 
       v_CmpFiltro       VARCHAR(50),
       v_QtdDias         INTEGER,
       v_CommitPoint     INTEGER,
       V_TipoCampo       VARCHAR(50),
       v_Formato         VARCHAR(50)
       );
       
TYPE Ty_TabLimpaDadosGeo IS TABLE OF Ty_RecordTabConfig;
       v_tabelas   Ty_TabLimpaDadosGeo;
       
TYPE Ty_RecordTempLimpDadosGO IS RECORD(
      idRow   varchar(20)
);

TYPE Ty_TabTemp IS TABLE OF Ty_RecordTempLimpDadosGO;
       v_TempTab   Ty_TabTemp;       
            
BEGIN
 
 --Seleciona os campos da tabela de configurações, 
 -- armazenando as informações das tabelas que terão seus registros excluidos 
  SELECT nomtab, cmpfiltro, qtddias, commitpoint, tipocampo, formato BULK COLLECT INTO v_tabelas  FROM T_CFGLIMPGO  ORDER BY ordemexec ASC  ;  
 
  --Percorre os Registros selecionados na tabelas de configurações 
   FOR v_Idc1 IN 1..v_tabelas.COUNT LOOP
         
     IF v_tabelas(v_Idc1).v_TipoCampo = 'D' THEN
      v_DtSys := SYSDATE -  v_tabelas(v_Idc1).v_QtdDias;
   v_SQL := 'SELECT rowid FROM '|| v_tabelas(v_Idc1).v_NomeTabela ||' WHERE '|| v_tabelas(v_idc1).v_CmpFiltro || ' <=  '''|| v_DtSys ||''' AND ROWNUM <= ''' || v_tabelas(v_Idc1). v_CommitPoint ||'''' ;    
    ELSIF v_tabelas(v_Idc1).v_TipoCampo = 'V' THEN
        
   v_SQL := 'SELECT rowid FROM '|| v_tabelas(v_Idc1).v_NomeTabela ||' WHERE TO_DATE('|| v_tabelas(v_idc1).v_CmpFiltro||','''||v_tabelas(v_Idc1).v_Formato ||' '')  <=  SYSDATE -'''||  v_tabelas(v_Idc1).v_QtdDias ||''' AND ROWNUM <= ''' 
                || v_tabelas(v_Idc1). v_CommitPoint ||'''' ;             
    END IF;
    
    LOOP
      
                   
       -- Executa o SQL Dinâmico armazendo na BULK COLLECT
       EXECUTE IMMEDIATE v_SQL BULK COLLECT INTO  v_TempTab;
       
      
        -- Se a tabela estiver sem registros para deleção sai do LOOP
      EXIT WHEN v_TempTab.COUNT = 0;
       
      FOR v_Idc2 IN 1.. v_TempTab.COUNT LOOP 
        --Prepara o SQL Dinâmico para Deleção
          v_SQLDEL := 'DELETE FROM '|| v_tabelas(v_Idc1).v_NomeTabela||' WHERE ROWID = '''|| v_TempTab(v_Idc2).idRow ||''''  ;
       
        -- Executa o SQL Dinâmico de deleção  
         EXECUTE IMMEDIATE v_SQLDEL;
       
         
        END LOOP; 

        COMMIT;
       
      
    END LOOP;
   
  END LOOP;

EXCEPTION
     WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE(to_Char(sysdate,'dd/mm/yyyy hh24:mi:ss')||' - Erro Script: '||SQLCODE||' - '||SQLERRM);

END P_LIMPDADOSGO;

[fsitja]

Olá Marcelo. O uso do execute immediate no seu caso está ok, há alguns tunings que podem ser feitos.

1 - Como já foi mencionado,, os valores concatenados podem ser substituídos por bind variables, que funcionam como no exemplo abaixo, que faz uma chamada de procedure:

Selecionar tudo

DECLARE
  a NUMBER := 4;
  b NUMBER := 7;
  plsql_block VARCHAR2(100);
BEGIN
  plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';
  EXECUTE IMMEDIATE plsql_block USING a, b;  -- calc_stats(a, a, b, a)
END;

Repare que no seu caso não dá para substituir o nome da tabela ou campo do filtro por bind variable, mas sim v_dtSys e v_commitPoint por exemplo, pois são valores e não nomes de objetos ou colunas.

No seu caso não traz grandes benefícios pois sua query não é executada igual muitas vezes apenas mudando os valores ":x" e ":y". Um benefício além do desempenho é que ele pode reduzir a vulnerabilidade a sql injection pois qualquer coisa que seja concatenada sem ser validada por você pode ser trocada por código malicioso.

2 - O seu BULK COLLECT deve ter uma cláusula LIMIT sempre, pois se retornar milhares de linhas a memória do seu servidor vai pro chão e seu DBA vai querer sua cabeça. Um valor razoável é LIMIT 100: percorra um loop fazendo Fetches de 100 em 100 num array e deletando.

3 - Como o LIMIT já "quebra" sua lógica num loop com mais de um delete por tabela, o rownum não faz mais sentido e o parâmetro v_CommitPoint vira o LIMIT lá no FETCH.

Foi o que consegui pensar por enquanto. Qualquer coisa é só mandar.

[marcelo0906]

Bom dia, pessoal!

fsitja ,

eu tentei usar o LIMIT na bulk collect, mas quando compilo ele da o erro

Error(64,61): PLS-00103: Encontrado o símbolo "100" quando um dos seguintes símbolos era esperado: . ( , % ; return returning using O símbolo "using" foi substituído por "100" para continuar.

Selecionar tudo

 EXECUTE IMMEDIATE v_SQL BULK COLLECT INTO  v_TempTab LIMIT  100;

não sei se estou usando errado. ou se o execute immediate não aceita essa cláusula.


sabe o que tenho que fazer para funcionar???

abraços

[fsitja]

Para o select você pode usar um cursor para abrir seu select, onde sei que aceita o limit.

Tenta algo parecido com isso:

Selecionar tudo

declare
  v_cursor sys_refcursor;
  v_sql_stmt varchar2(4000);
  v_limit number := 1;
  type t_rec_cur is record
    (num number,
     nome varchar2(4000));
  type t_tab_cur is table of t_rec_cur;
  v_tab_cur t_tab_cur;
begin
  v_sql_stmt := 'SELECT 1 num, ''A'' nome FROM DUAL
                 UNION ALL
                 SELECT 2, ''B'' FROM DUAL
                 UNION ALL
                 SELECT 3, ''C'' FROM DUAL';
  open v_cursor for v_sql_stmt;
  loop
  fetch v_cursor bulk collect
    into v_tab_cur limit 2;
    -- insira seu processamento aqui
      for i in 1 .. v_tab_cur.count
      loop
        dbms_output.put_line('Linha ' || v_cursor%rowcount || ' - NOME = ' || v_tab_cur(i).nome);
      end loop;
    --
    exit when v_cursor%notfound;
  end loop;  
  close v_cursor;
end;