Home » RDBMS Server » Server Administration » trace bind variable for insert statement not working (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
trace bind variable for insert statement not working [message #645944] |
Wed, 16 December 2015 10:55 |
|
acherifi
Messages: 7 Registered: December 2015 Location: PARIS
|
Junior Member |
|
|
Hi,
I am trying to trace bind variables value for an insert statement and it's not working
Here is my test code :
1- Trace bind variable value for a select statement (this one work fine)
create table sag.test (a varchar2(2000));
ALTER SESSION SET EVENTS '10046 trace name context forever, level 4';
declare
b varchar2(1) := '1';
res varchar2(1);
begin
select max(a) into res from test where test.a = b;
end;
-- to check the bind variable value
SELECT
a.sql_text,
b.name,
b.position,
b.datatype_string,
b.value_string
FROM
v$sql_bind_capture b,
v$sqlarea a
WHERE b.sql_id = a.sql_id
AND b.sql_id = 'czh677ju2qpak';
-- the value_string is not null and set to the variable value passed to the statement
But
2- Trace bind variable value for an insert statement (this one is not working for me)
create table sag.test (a varchar2(2000));
ALTER SESSION SET EVENTS '10046 trace name context forever, level 4';
declare
a varchar2(1) := '1';
begin
insert into test values (a);
end;
-- to check the bind variable value
SELECT
a.sql_text,
b.name,
b.position,
b.datatype_string,
b.value_string
FROM
v$sql_bind_capture b,
v$sqlarea a
WHERE b.sql_id = a.sql_id
AND b.sql_id = '9tckxpvzmsq2d';
the value_string is null !!!
Any idea ?
Thanks for your help.
-- moderator edit: image inserted into message body by bb
[Updated on: Wed, 16 December 2015 11:07] by Moderator Report message to a moderator
|
|
|
|
|
Re: trace bind variable for insert statement not working [message #645971 is a reply to message #645946] |
Thu, 17 December 2015 04:10 |
|
acherifi
Messages: 7 Registered: December 2015 Location: PARIS
|
Junior Member |
|
|
Thanks Watson for the welcome.
I tried that one too and it's not working too
execute DBMS_MONITOR.SESSION_TRACE_ENABLE(7, 3800, binds=>true);
completed
The trace is enabled
select user, sid,serial#,sql_trace
from sys.v_$session
where sid =7;
SQL_TRACE = ENABLED
But the bind variable value still null
SELECT
a.sql_text,
b.name,
b.position,
b.datatype_string,
b.value_string
FROM
v$sql_bind_capture b,
v$sqlarea a
WHERE b.sql_id = a.sql_id
AND b.sql_id = '9tckxpvzmsq2d';
Another point :
select * from V$SQL where module = 'SQL Developer'
The [ declare a varchar2(1) := '1'; begin insert into test values (a); end; ] is being caught with SQL_ID = d74ft9thpdk85
This one do exist in v$sqlarea
select * from v$sqlarea where sql_id = 'd74ft9thpdk85';
but don't exist in v$sql_bind_capture
I tried to create a stored procedure
create or replace procedure test_bind_var_val_insert_stm(param in test.a%TYPE)
is
begin
insert into test(a) values (param);
commit;
END;
Then I call it in my test after having activated the trace
declare
parm varchar2(1) := '1';
res varchar2(1);
begin
test_bind_var_val_insert_stm(parm);
end;
and I have the same result
no id_sql in v$sql_bind_capture for the stored procedure
and the bind variable is null for the statement INSERT INTO TEST(A) VALUES (:B1 )
I am confused !!!
I am not seeing the problem
Thanks for your help
[Updated on: Thu, 17 December 2015 04:19] Report message to a moderator
|
|
|
|
|
Re: trace bind variable for insert statement not working [message #645976 is a reply to message #645973] |
Thu, 17 December 2015 04:41 |
|
acherifi
Messages: 7 Registered: December 2015 Location: PARIS
|
Junior Member |
|
|
Yes I generated trace file with bind variable, but tkprof rapport don't display the bind variables values and I can't get the bind value from the trace file (.trc)
I have a insert with 65 bind variable, How to upload the trace file so you can see what's going on ? thanks
INSERT INTO T_MY_TABLE (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32,COL33, COL33, COL34, COL35, COL36, COL37, COL38, COL39,COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47,COL48,COL49,COL50,COL51, COL52, COL53, COL54,COL55,COL56, COL57,COL58,COL59, COL60,COL61,COL62,COL63,COL64, COL65) VALUES (:B65 , :B64 , :B63 , :B62 , :B61 , :B60 , :B59 , :B58 , :B57 , :B56 , :B55 , :B54 , EMPTY_BLOB(), :B53 , :B52 , :B51 , :B50 , :B49 , :B48 , :B47 , :B46 , :B45 , :B44 , :B43 , :B42 , DES.OBF(:B41 ), :B40 , DES.OBF(:B39 ), :B38 , :B37 , :B36 , :B35 ,:B34 , :B33 , :B32 , :B31 , :B30 , :B29 , :B28 , DES.OBF(:B27 ), :B26 , :B25 , :B24 , :B23 , :B22 , :B21 , :B20 , :B19 ,:B18 ,:B17 ,:B16 ,:B15 ,:B14 , :B13 , DES.OBF(:B12 ),:B11 ,:B10 , :B9 ,:B8 ,:B7 , :B6 ,:B5 ,:B4 ,:B3 , :B2 , :B1 )
|
|
|
Re: trace bind variable for insert statement not working [message #645978 is a reply to message #645976] |
Thu, 17 December 2015 05:02 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
My memory is failing. Not tkprof, the Trace Analyzer Tool:
TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces (Doc ID 224270.1)
Or of course you can see the binds in the raw trace file. There are probably third party tools out there as well.
|
|
|
|
|
|
Re: trace bind variable for insert statement not working [message #645997 is a reply to message #645984] |
Thu, 17 December 2015 07:23 |
|
acherifi
Messages: 7 Registered: December 2015 Location: PARIS
|
Junior Member |
|
|
TYPICAL
SELECT statistics_name,
session_status,
system_status,
activation_level,
session_settable
FROM v$statistics_level
ORDER BY statistics_name;
Active Session History ENABLED ENABLED TYPICAL NO
Adaptive Thresholds Enabled ENABLED ENABLED TYPICAL NO
Automated Maintenance Tasks ENABLED ENABLED TYPICAL NO
Automatic DBOP Monitoring ENABLED ENABLED TYPICAL YES
Bind Data Capture ENABLED ENABLED TYPICAL NO
Buffer Cache Advice ENABLED ENABLED TYPICAL NO
Global Cache CPU Statistics DISABLED DISABLED ALL NO
Global Cache Statistics ENABLED ENABLED TYPICAL NO
Longops Statistics ENABLED ENABLED TYPICAL NO
Modification Monitoring ENABLED ENABLED TYPICAL NO
MTTR Advice ENABLED ENABLED TYPICAL NO
OLAP row load time precision DISABLED DISABLED TYPICAL YES
PGA Advice ENABLED ENABLED TYPICAL NO
Plan Execution Sampling ENABLED ENABLED TYPICAL YES
Plan Execution Statistics DISABLED DISABLED ALL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
Shared Pool Advice ENABLED ENABLED TYPICAL NO
SQL Monitoring ENABLED ENABLED TYPICAL YES
Streams Pool Advice ENABLED ENABLED TYPICAL NO
Threshold-based Alerts ENABLED ENABLED TYPICAL NO
Time Model Events ENABLED ENABLED TYPICAL YES
Timed OS Statistics DISABLED DISABLED ALL YES
Timed Statistics ENABLED ENABLED TYPICAL YES
Ultrafast Latch Statistics ENABLED ENABLED TYPICAL NO
Undo Advisor,Alerts and Ramp up ENABLED ENABLED TYPICAL NO
V$IOSTAT_* statistics ENABLED ENABLED TYPICAL NO
[Updated on: Thu, 17 December 2015 07:25] Report message to a moderator
|
|
|
|
|
Re: trace bind variable for insert statement not working [message #646008 is a reply to message #646002] |
Thu, 17 December 2015 11:12 |
|
acherifi
Messages: 7 Registered: December 2015 Location: PARIS
|
Junior Member |
|
|
Hi BlackSwan
I do did a research to get this "result", I have already got the bind variables values in the trace file but when I request the oracle views (expecting to get the same result) I am getting the bind variables values set to null and I don't get why !!
thanks
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:22:20 CDT 2024
|