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
  

Poston Thu, 03 Nov 2005 1:42 pm

Guys, I wonder if there is any other way to create this dynamic sql without me having to declare the variaves in USING.

Code: Select all

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
Location: São Paulo

Poston Thu, 03 Nov 2005 3:30 pm

You could not pass these values as a parameter of the procedure?

[] 's
MuLtAnI
Location: Videira - SC


Poston Thu, 03 Nov 2005 3:34 pm

but they are already passed by the procedure!!!

I don't get it. ...

You couldn't put the encoding if there is another way to do?! I would understand better if you put the code.

Grateful
LC_JK
Location: São Paulo

Poston Thu, 03 Nov 2005 3:41 pm

Code: Select all
CREATE OR REPLACE PROCEDURE ( TEST p_owner ) P_VENDOR ( varchar2 10, NUMBER, P_VENDOR_SITE NUMBER, p_region (, ) 10 v_headers_attrib1 VARCHAR2 VARCHAR2, VARCHAR2 10 ( ) v_headers_attrib2 ( 10 ), v_user_id NUMBER, v_user_id1 NUMBER, V_CONT ( ) 9, P_HEADER varchar2 NUMBER ) the begin--select nextval into po_headers_s .-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 = ( 1, : : DECODE 2, : 3, NULL ) ' ||
                     ', ATTRIBUTE2 = ( 4 :, : DECODE 5, : 6, NULL ) ' ||
--', : = ' 7 SUCCESS_STATUS ||
                     ' THE 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 ', ' test ', NULL FROM DUAL-)-select po_headers_all . nextval from dual--po_headers_s end;


If you need to store either short or the values, I don't see any other way but to variables.

I hope mter helped!
MuLtAnI
Location: Videira - SC


Poston Thu, 03 Nov 2005 3:46 pm

ah está ... ok ... that I was already doing ... is that not put the full encoding query ... but qualquer way, thanks for the attention for now I will do it that way even if there is some other way ...

T +
LC_JK
Location: São Paulo

Poston Thu, 03 Nov 2005 3:59 pm

I think you should put in USING also the v_user_id , because that way, the Bank will only PARSE your SQL if the owner is different. (in other cases, the optimizer will not need to easily parse all your sql again) ...

Besides, I don't think there's another way out.
dr_gori
Location: 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

Poston Fri, 04 Nov 2005 7:40 am

dr_gori, PMFJI, but ALL execute immediate does parse. Imagine how Oracle could keep a sql * DYNAMIC * curly. No, he will be parseado to each call. What we need to understand to help the fellow is: what is the need for the use of dynamic SQL.
mportes
Location: São Paulo


Poston Fri, 04 Nov 2005 8:26 am

Hello Marcio but then, what's the point of using BINDS in Dynamic SQL?
Let's say that the select is executed 3 times. And by coincidence the OWNER is equal. (I spoke to owner play his example. Let's just say the OWNER is the dynamic part of SQL). Namely, everything else is equal, changing only the binds.

I thought he was just going to PARSE at first, because in the second and third call SQL would be identical.

Am I wrong?

This is an example of what I'm talking about:
Code: Select all
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>
Code: Select all
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>
more than 10 times faster in just 1000 rows. :-o

That's why I told him to put the v_user_id how to bind, hence will only be the owner that makes the dynamic sql. (this parameter, it probably won't change much).
dr_gori
Location: 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

Poston Sat, 05 Nov 2005 1:01 am

The difference of speed that you obtained was the difference between 1000 HARD PARSEs to 1000 SOFT PARSEs .
If you had run the tkprof in your example, you would find 1000 lines of hard parse to the first anonymous block and the second you would find it:
Code: Select all
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
I think that answer your first question: we use bind variable in dynamic sql to decrease hard parse to the fullest. But we cannot avoid them.

Following, you mention the owner equal to just changing the bind and if you're wrong: indeed, the SQL will be HARD parsed the first time and from then on SOFT parsed.

Another problem I found in your comparison, the first question mentions RUN IMMEDIATE and in your example you use REFCURSOR. Well, everything will turn out in dynamic sentences and believe they have the same behavior.

Already in version 10 g it's a horse of a different color, but that's another story.
mportes
Location: São Paulo



  • See also
    Replies
    Views
    Last Post


        Return to PL/SQL

        Who is online

        Users browsing this forum: Google [Bot] and 8 guests