Ето начин да го направиш:
How To Display The Values Of A Bind Variable In A SQL Statement
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 bounce your database afterwards.
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 oct=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 oacflg=03
bfp=20080d30 bln=22 avl=02 flg=05
value=88
bind 1: dty=96 mxl=32(05) mal=00 scl=00 pre=00 oacflg=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.
|