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
  

Mensagemem Qui, 03 Nov 2005 1:42 pm

Galera,

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

Código: Selecionar todos

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;
   
LC_JK
Localização: São Paulo

Mensagemem Qui, 03 Nov 2005 3:30 pm

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

[]'s
MuLtAnI
Localização: Videira - SC


Mensagemem Qui, 03 Nov 2005 3:34 pm

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
LC_JK
Localização: São Paulo

Mensagemem Qui, 03 Nov 2005 3:41 pm

Código: Selecionar todos
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!
MuLtAnI
Localização: Videira - SC


Mensagemem Qui, 03 Nov 2005 3:46 pm

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+
LC_JK
Localização: São Paulo

Mensagemem Qui, 03 Nov 2005 3:59 pm

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.
dr_gori
Localização: Portland, OR USA

Thomas F. G

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

Mensagemem Sex, 04 Nov 2005 7:40 am

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.
mportes
Localização: São Paulo


Mensagemem Sex, 04 Nov 2005 8:26 am

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:
Código: Selecionar todos
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>


Código: Selecionar todos
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).
dr_gori
Localização: Portland, OR USA

Thomas F. G

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

Mensagemem Sáb, 05 Nov 2005 1:01 am

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:

Código: Selecionar todos
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.
mportes
Localização: São Paulo



  • Veja também
    Respostas
    ExibiÇões
    Última mensagem


        Voltar para PL/SQL

        Quem está online

        Usuários navegando neste fórum: Google Adsense [Bot] e 8 visitantes