ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How To Display The Values Of A Bind Variable In A SQL Statement [ID 1068973.6]

How To Display The Values Of A Bind Variable In A SQL Statement [ID 1068973.6]

原创 Linux操作系统 作者:spider0283 时间:2012-05-20 00:40:35 0 删除 编辑
Checked for relevance on 17-Apr-2012 

Problem Description
-------------------

PL/SQL is running in the database.  At some point you want to dump the 
SQL statement along with the values that were substituted in the bind
variables.

How is this accomplished?


Solution Description
--------------------

You can set an event to dump the PL/SQL and the associated bind
variable values to the trace file with the following steps:

1.  Set the following event either in the parameter file (init.ora):

    EVENT = "10046 TRACE NAME CONTEXT FOREVER, LEVEL 4"

    -OR-

    Set it just for the session:

    ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

    Event 10046 is a special event code used to signal Oracle to perform. "SQL_TRACE" actions.  The level 4 enables the trace plus the value
    of the bind variables.  If you set the event in the parameter file,
    you must restart your database afterward.

2.  Run the PL/SQL which uses bind variables.

3.  View the resulting trace file in the directory specified by the
    "user_dump_dest" parameter in the parameter file (init.ora).


Sample PL/SQL code:

    declare
      vcount number := 88;
      vcount2 char(5) := 'meme' ;
    begin
      insert into taba values (vcount, vcount2);
    end;

The resulting trace output:

PARSING IN CURSOR #2 len=36 dep=1 uid=0 ct=2 lid=0 tim=0 hv=540755502
ad='40106
c6c'
INSERT INTO TABA VALUES ( :b1,:b2  )
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=0
BINDS #2:
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 acflg=03
   bfp=20080d30 bln=22 avl=02 flg=05
   value=88
 bind 1: dty=96 mxl=32(05) mal=00 scl=00 pre=00 acflg=03
   bfp=20080d1c bln=05 avl=05 flg=05
   value="meme "
EXEC #2:c=0,e=0,p=0,cr=2,cu=3,mis=0,r=1,dep=1,og=4,tim=0
EXEC #1:c=0,e=0,p=0,cr=2,cu=3,mis=0,r=1,dep=0,og=4,tim=0
XCTEND rlbk=0, rd_only=0

*NOTE:  The values of the bind variables are displayed in the order
        that they appear in the SQL statement.  Find the word "value"
        in the trace file.  The first value 88 corresponds to b1 in
        the SQL statement.  The second value of "meme" corresponds to
        b2 in the SQL statement.


References
----------

                 (including binds/waits)"


Search Words
------------
 
Substitute, Event

返回页首返回页首

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/38267/viewspace-730268/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    194
  • 访问量
    621654