Execute Immediate

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
LC_JK
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 46
Registrado em: Ter, 24 Ago 2004 2:02 pm
Localização: São Paulo
Contato:

Galera,

Gostaria de saber se existe alguma outra forma de criar esse sql dinamico sem que eu tenha que declarar as variaves no USING.

Selecionar tudo


declare
p_owner  varchar2(10):= 'PO';
P_VENDOR NUMBER:= 10;
P_VENDOR_SITE NUMBER := 11;
p_region VARCHAR2(10) := 'AMER';
v_headers_attrib1 VARCHAR2(10) := 'TESTE1';
v_headers_attrib2 VARCHAR2(10) := 'TESTE2';
v_user_id  NUMBER := 8555;
v_user_id1  NUMBER ;
V_CONT  varchar2(9):= 'AMER';
P_HEADER NUMBER := 107;

begin

--select po_headers_s.nextval 
-- into v_user_id1
-- from dual;

EXECUTE IMMEDIATE 
               ('UPDATE '||p_owner||'.PO_HEADERS_ALL'||
                  ' SET   VENDOR_ID           = 11'||--P_VENDOR||
                     ' ,VENDOR_SITE_ID      = '||P_VENDOR_SITE||
                     ' ,ATTRIBUTE1           = DECODE(:1,:2,:3,NULL)'||
                     ' ,ATTRIBUTE2           = DECODE(:4,:5,:6,NULL)'||
--                     ' ,SUCCESS_STATUS          = :7'||
                     ' ,CREATION_DATE           = SYSDATE'||
                     ' ,CREATED_BY              = '||v_user_id||
                     ' ,LAST_UPDATE_DATE        = SYSDATE'||
                     ' ,LAST_UPDATED_BY          = '||v_user_id||
                     ' ,attribute3 = :7 '||
                  ' WHERE PO_HEADER_ID = '||P_HEADER)
                   USING p_region,V_CONT,v_headers_attrib1,
                   p_region,V_CONT,v_headers_attrib2,'y';--,v_user_id;

--SELECT * FROM PO_HEADERS_ALL WHERE PO_HEADER_ID = 107                  
--SELECT  DECODE('AMER','AMER','TESTE',NULL) FROM DUAL
--select po_headers_all.nextval from dual
--po_headers_s

end;
	
Avatar do usuário
MuLtAnI
Moderador
Moderador
Mensagens: 90
Registrado em: Ter, 01 Jun 2004 9:21 am
Localização: Videira - SC
Contato:

Você não poderia passar estes valores como parâmetro da procedure?

[]'s
LC_JK
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 46
Registrado em: Ter, 24 Ago 2004 2:02 pm
Localização: São Paulo
Contato:

mas eles já são passados pela procedure!!!

não entendi....

Você não poderia colocar a codificação caso existe uma outra forma de fazer?! Acho q compreenderia melhor se colocasse o código.

Grato
Avatar do usuário
MuLtAnI
Moderador
Moderador
Mensagens: 90
Registrado em: Ter, 01 Jun 2004 9:21 am
Localização: Videira - SC
Contato:

Selecionar tudo

CREATE OR REPLACE PROCEDURE TESTE(
     p_owner  varchar2(10), 
     P_VENDOR NUMBER, 
     P_VENDOR_SITE NUMBER,
     p_region VARCHAR2(10),
     v_headers_attrib1 VARCHAR2(10), 
     v_headers_attrib2 VARCHAR2(10),
     v_user_id  NUMBER,
     v_user_id1  NUMBER,
     V_CONT  varchar2(9),
     P_HEADER NUMBER) AS
begin 

--select po_headers_s.nextval 
-- into v_user_id1 
-- from dual; 

EXECUTE IMMEDIATE 
               ('UPDATE '||p_owner||'.PO_HEADERS_ALL'|| 
                  ' SET   VENDOR_ID           = 11'||--P_VENDOR|| 
                     ' ,VENDOR_SITE_ID      = '||P_VENDOR_SITE|| 
                     ' ,ATTRIBUTE1           = DECODE(:1,:2,:3,NULL)'|| 
                     ' ,ATTRIBUTE2           = DECODE(:4,:5,:6,NULL)'|| 
--                     ' ,SUCCESS_STATUS          = :7'|| 
                     ' ,CREATION_DATE           = SYSDATE'|| 
                     ' ,CREATED_BY              = '||v_user_id|| 
                     ' ,LAST_UPDATE_DATE        = SYSDATE'|| 
                     ' ,LAST_UPDATED_BY          = '||v_user_id|| 
                     ' ,attribute3 = :7 '|| 
                  ' WHERE PO_HEADER_ID = '||P_HEADER) 
                   USING p_region,V_CONT,v_headers_attrib1, 
                   p_region,V_CONT,v_headers_attrib2,'y';--,v_user_id; 

--SELECT * FROM PO_HEADERS_ALL WHERE PO_HEADER_ID = 107                  
--SELECT  DECODE('AMER','AMER','TESTE',NULL) FROM DUAL 
--select po_headers_all.nextval from dual 
--po_headers_s 

end;
Se você precisa aramazenar os valores, não vejo outra maneira senão variáveis.

Espero mter ajudado!
LC_JK
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 46
Registrado em: Ter, 24 Ago 2004 2:02 pm
Localização: São Paulo
Contato:

ah está... ok..... isto eu já estava fazendo.... é que não coloquei a codifição completa......

mas de qualquer jeito, obrigado pela atenção.. por enquanto farei dessa forma mesmo, caso exista alguma outra forma.....

T+
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Acho que você deveria colocar no USING também o v_user_id, pois assim, o banco só vai fazer PARSE do seu SQL se o owner seja diferente. (em outros casos, o otimizador não vai precisar parsear todo seu sql denovo)...

No mais, acho que não tem outra saída.
mportes
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 13
Registrado em: Ter, 01 Nov 2005 11:53 pm
Localização: São Paulo
Contato:

dr_gori, PMFJI, mas TODO execute immediate faz parse. Imagine como o Oracle conseguiria manter um sql *DINAMICO* cacheado. Não, ele será parseado a cada chamada. O que precisamos entender para ajudar o colega é: Qual a necessidade do uso do SQL Dinâmico.
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Olá Marcio

Mas então, qual é o motivo de se usar BINDS nos SQL Dinamicos ?
Digamos que o select será executado 3 vezes. E por coincidência o OWNER seja igual. (falei owner pra reproduzir o exemplo dele. Digamos que apenas o OWNER seja a parte dinamica do SQL). Ou seja, todo o resto dele é igual, mudando apenas as binds.

Achei que ele só ia fazer PARSE na primeira, pois na segunda e terceira chamada o SQL seria idêntico.

Estou errado ?

Este é um exemplo do que eu estou falando:

Selecionar tudo

SQL> declare
  2        type rc is ref cursor;
  3        l_rc rc;
  4        l_dummy all_objects.object_name%type;
  5        l_start number default dbms_utility.get_time;
  6    begin
  7        for i in 1 .. 1000
  8        loop
  9            open l_rc for
 10            'select object_name
 11               from all_objects
 12              where object_id = ' || i;   ---> *** SEM CLAUSULA USING
 13            fetch l_rc into l_dummy;
 14            close l_rc;
 15            -- dbms_output.put_line(l_dummy);
 16        end loop;
 17        dbms_output.put_line
 18         (round((dbms_utility.get_time-l_start)/100, 2) ||
 19          ' Seconds...' );
 20    end;
 21  /
15.49 Seconds...

PL/SQL procedure successfully completed.

SQL> 

Selecionar tudo

SQL> declare
  2        type rc is ref cursor;
  3        l_rc rc;
  4        l_dummy all_objects.object_name%type;
  5        l_start number default dbms_utility.get_time;
  6    begin
  7        for i in 1 .. 1000
  8        loop
  9            open l_rc for
 10            'select object_name
 11               from all_objects
 12              where object_id = :x'   --> USING ---> binds !!!
 13            using i;
 14            fetch l_rc into l_dummy;
 15            close l_rc;
 16            -- dbms_output.put_line(l_dummy);
 17        end loop;
 18        dbms_output.put_line
 19         (round((dbms_utility.get_time-l_start)/100, 2) ||
 20          ' Seconds...' );
 21  end;
 22  /
1.31 Seconds...

PL/SQL procedure successfully completed.

SQL> 
Mais de 10 vezes mais rápido em apenas 1000 linhas. :-o

Foi por isso que eu falei pra ele colocar o v_user_id como bind, daí só vai sobrar o owner que deixa o sql dinâmico. (este parametro, provavelmente não vai mudar muito).
mportes
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 13
Registrado em: Ter, 01 Nov 2005 11:53 pm
Localização: São Paulo
Contato:

A diferença de velocidade que você obteve foi a diferença entre 1000 HARD PARSEs para 1000 SOFT PARSEs.
Se você tivesse rodado o tkprof no seu exemplo, você iria encontrar 1000 linhas de hard parse para o primeiro bloco anônimo e para o segundo você iria encontrar isso:

Selecionar tudo

select object_name
             from all_objects
            where object_id = :x

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     1000      0.00       0.14          0          3          0           0
Execute   1000      0.00       0.32          0          0          0           0
Fetch     1000      0.00       1.65          0      24928          0         938
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3000      0.00       2.12          0      24931          0         938
Acho que assim respondo sua primeira pergunta: usamos bind variable em sql dinâmico para diminuir o hard parse ao máximo. Mas não podemos evitá-los.

Seguindo, você menciona o owner igual só mudando as bind e se você está errado: na verdade, o SQL vai ser HARD parsed na primeira vez e a partir dai SOFT parsed.

Outro problema que encontrei na sua comparação, a primeira dúvida menciona EXECUTE IMMEDIATE e no seu exemplo você usa REFCURSOR. Bom, vai acabar tudo em sentenças dinâmicas mesmo e acredito que tenham o mesmo comportamento.

Já na versão 10g a coisa muda de figura, mas ai já é outra história.
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 25 visitantes