Home » Developer & Programmer » Precompilers, OCI & OCCI » ProC, trace, bind variables
| ProC, trace, bind variables [message #122029] |
Thu, 02 June 2005 08:14  |
robert.koltai
Messages: 7
Registered: June 2005
|
Junior Member |
|
|
Hello,
I need to trace in a ProC application. In case of error I'd like to
know what went wrong.
I use
sqlgls(stmt_buf, (size_t *) &buflen, (size_t *) &function_code);
to retrieve the sql statement that was executed.
Unfortunately using bind variables I get something like:
"insert into sec_mocdefaultpassword (userName,mocName,pwString) values (:s1:s2 ,:s3:s4 ,:s5:s6 )"
Does anyone know a solution to retrieve also the values for the bind variables?
Thanks,
Rob
|
|
|
|
|
|
| Re: ProC, trace, bind variables [message #122573 is a reply to message #122182] |
Tue, 07 June 2005 06:24  |
robert.koltai
Messages: 7
Registered: June 2005
|
Junior Member |
|
|
Thanks Michael,
I've checked it.
I did not mention it last time that I'm on 8.1.7.4.
1) Are you sure that is works on 8.1.7.4 as well?
2) I have no idea how to "identify the current cursor" if there is more than one of it.
3) But the biggest problem is that the data seems to be mess.
Any idea?
Here is the output of 3 different queries:
***
TRY 1
EXEC SQL DECLARE c_bindVariables CURSOR FOR
SELECT (' Curno: ' || CUR.CURNO ||
' Position: ' || BIND.POSITION ||
' Datatype: ' || BIND.DATATYPE ||
' Flag: ' || CUR.FLAG ||
' Status: ' || CUR.STATUS ||
' Bind Vars: ' || CUR.BIND_VARS ||
' VALUE: ' || BIND.VALUE)
FROM v$sql_cursor CUR, v$sql_bind_data BIND
WHERE CUR.CURNO = BIND.CURSOR_NUM AND
CUR.STATUS <> 'CURNULL'
ORDER BY CURNO;
Curno: 1 Position: 0 Datatype: 0 Flag: 70 Status: CURFETCH Bind Vars: 0 VALUE: T?l®Ó?lÓ
Curno: 2 Position: 1 Datatype: 1 Flag: 68 Status: CURBOUND Bind Vars: 5 VALUE: T?l®Ó?lÓ
Curno: 3 Position: 1 Datatype: 1 Flag: 68 Status: CURBOUND Bind Vars: 3 VALUE: T?l®Ó?lÓ
***
TRY2
EXEC SQL DECLARE c_bindVariables CURSOR FOR
SELECT TO_CHAR(vbd.position) || ':' || vbd.value
FROM v$sql_bind_data vbd,
sys.v_$sql vsql,
sys.v_$sql_cursor vsqlc
WHERE vsql.address = vsqlc.parent_handle
AND vsqlc.curno = vbd.cursor_num ;
0:?lh
1:?lh
1:?lh
***
TYR3
EXEC SQL DECLARE c_bindVariables CURSOR FOR
SELECT (' Cursor num: ' || BIND.CURSOR_NUM ||
' Position: ' || BIND.POSITION ||
' Datatype: ' || BIND.DATATYPE ||
' Buf Len: ' || BIND.BUF_LENGTH ||
' Val Len: ' || BIND.VAL_LENGTH ||
' Buf Flag: ' || BIND.BUF_FLAG ||
' Indicator: ' || BIND.INDICATOR ||
' VALUE: ' || BIND.VALUE)
FROM v$sql_bind_data BIND
ORDER BY BIND.CURSOR_NUM, BIND.POSITION;
Cursor num: 1 Position: 0 Datatype: 0 Buf Len: 0 Val Len: 0 Buf Flag: 0 Indicator: 0 VALUE: Ñ
Cursor num: 2 Position: 1 Datatype: 1 Buf Len: 0 Val Len: 0 Buf Flag: 0 Indicator: 0 VALUE: Ñ
Cursor num: 3 Position: 1 Datatype: 1 Buf Len: 0 Val Len: 0 Buf Flag: 0 Indicator: 0 VALUE: Ñ
Cursor num: 4 Position: 0 Datatype: 0 Buf Len: 0 Val Len: 0 Buf Flag: 0 Indicator: 0 VALUE: Ñ
Cursor num: 5 Position: 0 Datatype: 0 Buf Len: 0 Val Len: 0 Buf Flag: 0 Indicator: 0 VALUE: Ñ
Cursor num: 6 Position: 0 Datatype: 0 Buf Len: 0 Val Len: 0 Buf Flag: 0 Indicator: 0 VALUE: Ñ
Cursor num: 7 Position: 0 Datatype: 0 Buf Len: 0 Val Len: 0 Buf Flag: 0 Indicator: 0 VALUE: Ñ
Cursor num: 8 Position: 0 Datatype: 0 Buf Len: 0 Val Len: 0 Buf Flag: 0 Indicator: 0 VALUE: Ñ
Cursor num: 9 Position: 0 Datatype: 0 Buf Len: 0 Val Len: 0 Buf Flag: 0 Indicator: 0 VALUE: Ñ
Cursor num: 10 Position: 0 Datatype: 0 Buf Len: 0 Val Len: 0 Buf Flag: 0 Indicator: 0 VALUE:
Cursor num: 11 Position: 0 Datatype: 0 Buf Len: 0 Val Len: 0 Buf Flag: 0 Indicator: 0 VALUE:
Cursor num: 12 Position: 0 Datatype: 0 Buf Len: 0 Val Len: 0 Buf Flag: 0 Indicator: 0 VALUE:
...
Thanks,
Rob.
[Updated on: Tue, 07 June 2005 06:50] Report message to a moderator |
|
|
|
| Re: ProC, trace, bind variables [message #122585 is a reply to message #122573] |
Tue, 07 June 2005 08:27 |
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
|
Senior Member |
|
|
Hi
Try the following select statement to obtain your bind values.
select
sql.sql_text line,
sqlbd.cursor_num,
sqlbd.position,
sqlbd.datatype,
'xx' || sqlbd.value || 'xx'
from
v$sql sql,
v$sql_cursor sqlc ,
v$sql_bind_data sqlbd
where
sql.address=sqlc.parent_handle and
sqlbd.cursor_num = sqlc.curno;
I'll explain it briefly but you can get the detail view information from the Oracle online documentation.
Firstly, v$sql includes all cursors on the database.
Secondly, v$sql_cursor is just those cursors for the current session.
Thirdly, v$sql_bind_data relates to all bind positions for all cursors on the database.
Since we are joining v$sql_cursor (just the current session cursors) with the v$sql and v$sql_bind_data we get the sql statement and bind position, type, value for each of the bind variables as well.
If you have multiple cursors open you will get multiple result sets.
Now, you have to appreciate that if you prepare, execute, close a cursor, the v$sql_cursor will not contain a reference to the closed cursor, also the bind values will not exist.
So you have to interrogate the bind_data before closing the cursor.
That's my understanding, if you find differently I would be interested to know.
Kind regards
Michael Hartley http://www.openfieldsolutions.co.uk
|
|
|
|
| Re: ProC, trace, bind variables [message #122603 is a reply to message #122585] |
Tue, 07 June 2005 10:08 |
robert.koltai
Messages: 7
Registered: June 2005
|
Junior Member |
|
|
Hello
thanks for your reply.
I do have the same understanding as you, but Oracle does not share this opinion. ((
I modified slightly your code to have nicer output, but the rest is the same.
EXEC SQL DECLARE c_bindVariables CURSOR FOR
select
' A.sql_text: ' || A.sql_text ||
' C.cursor_num: ' || C.cursor_num ||
' C.position: ' || C.position ||
' C.datatype: ' || C.datatype ||
' C.value: <START>' || C.value || '<END>'
from
v$sql A,
v$sql_cursor B,
v$sql_bind_data C
where
A.address = B.parent_handle and
C.cursor_num = B.curno;
In the output we see two lines.
Line 1) is the cursor that we use the see the cursors, e.g. your code. For this line the value is empty.
Line 2) is my code. For this line the value is bullshit.
Here is the output:
A.sql_text: select ((((((((((' A.sql_text: '||A.sql_text)||' C.cursor_num: ')||C.cursor_num)||' C.position: ')||C.position)||' C.datatype: ')||C.datatype)||' C.value: <START>')||C.value)||'<END>') from v$sql A ,v$sql_cursor B ,v$sql_bind_data C where (A.address=B.parent_handle and C.cursor_num=B.curno) C.cursor_num: 1 C.position: 0 C.datatype: 0 C.value: <START><END>
A.sql_text: update sec_mocdefaultpassword set userName=:b0,mocName=:b1,pwString=:b2 where (userName=:b0 and mocName=:b1) C.cursor_num: 2 C.position: 1 C.datatype: 1 C.value: <START><END>||'<END>') from v$sql A ,v$sql_cursor B ,v$sql_bind_data C where (A.address=B.parent_handle and C.cursor_num=B.curno) C.cursor_num: 1 C.position: 0 C.datatype: 0 C.value: <START><END>
What do you think???
Anyway I'd expect 3 lines for the 3 bind variables and I cannot see them...
Thanks,
Robert
|
|
|
|
| Re: ProC, trace, bind variables [message #122624 is a reply to message #122603] |
Tue, 07 June 2005 12:01 |
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
|
Senior Member |
|
|
Hi
It does work, you just have to keep experimenting until you get the formula.
To convince you that it can be done, try the following in sqlplus:
variable bindvar number;
begin
:bindvar:=10;
end;
/
select
sql.sql_text line,
sqlbd.cursor_num,
sqlbd.position,
sqlbd.datatype,
'xx' || sqlbd.value || 'yy'
from
v$sql sql,
v$sql_cursor sqlc ,
v$sql_bind_data sqlbd
where
sql.address=sqlc.parent_handle and
sqlbd.cursor_num = sqlc.curno and
10 = :bindvar;
which will produce results similar to:
| Quote: |
LINE
--------------------------------------------------------------------------------
CURSOR_NUM POSITION DATATYPE
---------- ---------- ----------
'XX'||SQLBD.VALUE||'YY'
--------------------------------------------------------------------------------
select sql.sql_text line, sqlbd.cursor_num, sqlbd.position, sqlbd.datatype,
:"SYS_B_0" || sqlbd.value || :"SYS_B_1" from v$sql sql, v$sql_cursor sqlc ,
v$sql_bind_data sqlbd where sql.address=sqlc.parent_handle and sqlbd.cursor_nu
m = sqlc.curno and :"SYS_B_2" = :bindvar
1 4 2
xx10yy
|
Kind regards
Michael Hartley http://www.openfieldsolutions.co.uk
|
|
|
|
| Re: ProC, trace, bind variables [message #122626 is a reply to message #122624] |
Tue, 07 June 2005 12:09 |
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
|
Senior Member |
|
|
On reflection I realised that probably wasn't the most clear example I could have written, so try the following which is a minor modification of the original.
variable bindvar number;
begin
:bindvar:=10000;
end;
/
break on line
select
sql.sql_text line,
sqlbd.cursor_num,
sqlbd.position,
sqlbd.datatype,
sqlbd.value
from
v$sql sql,
v$sql_cursor sqlc ,
v$sql_bind_data sqlbd
where
sql.address=sqlc.parent_handle and
sqlbd.cursor_num = sqlc.curno and
sqlbd.cursor_num < :bindvar;
and produces for me
| Quote: |
LINE
--------------------------------------------------------------------------------
CURSOR_NUM POSITION DATATYPE
---------- ---------- ----------
VALUE
--------------------------------------------------------------------------------
select sql.sql_text line, sqlbd.cursor_num, sqlbd.position, sqlbd.datatype,
sqlbd.value from v$sql sql, v$sql_cursor sqlc , v$sql_bind_data sqlbd where
sql.address=sqlc.parent_handle and sqlbd.cursor_num = sqlc.curno and sqlbd.cu
rsor_num < :bindvar
1 1 2
10000
|
kind regards
Michael Hartley http://www.openfieldsolutions.co.uk
|
|
|
|
| Re: ProC, trace, bind variables [message #122738 is a reply to message #122626] |
Wed, 08 June 2005 06:02 |
robert.koltai
Messages: 7
Registered: June 2005
|
Junior Member |
|
|
Hi Michael,
you're starting to convince me!
Both examples are 100% clear to me.
There was a bug in my C code, which I corrected, but I still don't have what I want.
Here is my code based on your select,
note that I added the STATUS of the cursor for the output:
select
' A.sql_text: ' || A.sql_text ||
' B.status: ' || B.status ||
' C.cursor_num: ' || C.cursor_num ||
' C.position: ' || C.position ||
' C.datatype: ' || C.datatype ||
' C.value: <START>' || C.value || '<END>'
from
v$sql A,
v$sql_cursor B,
v$sql_bind_data C
where
A.address = B.parent_handle and
C.cursor_num = B.curno
and :v_testBind1 <> 'miau1'
and :v_testBind2 <> 'miau2'
and :v_testBind3 <> 'miau3';
I also added 3 bind variables to see that I really have 3 lines returned by the query _FOR_THIS_CURSOR_.
And in fact I see the 3 lines for this cursor and 1 additional which is supposed to be the one that I'm really interested in.
| Quote: |
A.sql_text: select ((((((((((((' A.sql_text: '||A.sql_text)||' B.status: ')||B.status)||' C.cursor_num: ')||C.cursor_num)||' C.position: ')||C.position)||' C.datatype: ')||C.datatype)||' C.value: <START>')||C.value)||'<END>') from v$sql A ,v$sql_cursor B ,v$sql_bind_data C where ((((A.address=B.parent_handle and C.cursor_num=B.curno) and :b0<>'miau1') and :b1<>'miau2') and :b2<>'miau3') B.status: CURFETCH C.cursor_num: 1 C.position: 1 C.datatype: 1 C.value: <START>vau1<END>
A.sql_text: select ((((((((((((' A.sql_text: '||A.sql_text)||' B.status: ')||B.status)||' C.cursor_num: ')||C.cursor_num)||' C.position: ')||C.position)||' C.datatype: ')||C.datatype)||' C.value: <START>')||C.value)||'<END>') from v$sql A ,v$sql_cursor B ,v$sql_bind_data C where ((((A.address=B.parent_handle and C.cursor_num=B.curno) and :b0<>'miau1') and :b1<>'miau2') and :b2<>'miau3') B.status: CURFETCH C.cursor_num: 1 C.position: 2 C.datatype: 1 C.value: <START>vau2<END>
A.sql_text: select ((((((((((((' A.sql_text: '||A.sql_text)||' B.status: ')||B.status)||' C.cursor_num: ')||C.cursor_num)||' C.position: ')||C.position)||' C.datatype: ')||C.datatype)||' C.value: <START>')||C.value)||'<END>') from v$sql A ,v$sql_cursor B ,v$sql_bind_data C where ((((A.address=B.parent_handle and C.cursor_num=B.curno) and :b0<>'miau1') and :b1<>'miau2') and :b2<>'miau3') B.status: CURFETCH C.cursor_num: 1 C.position: 3 C.datatype: 1 C.value: <START>vau3<END>
A.sql_text: update sec_mocdefaultpassword set userName=:b0,mocName=:b1,pwString=:b2 where (userName=:b0 and mocName=:b1) B.status: CURBOUND C.cursor_num: 2 C.position: 1 C.datatype: 1 C.value: <START><END>
|
As you can see the code was able to tell us everything about ITSELF (just like your example run in SQL*Plus), but was unable to show the bind values for the OTHER CURSOR, which would be the goal at the end:((((
Can this be related to the status of the cursors:
CURFETCH
CURBOUND
and that the views can be used only for cursors in CURFETCH status ???
Note also that my update statement has also 3 bind variables and just one row is returned by the query.
Now it's getting interesting.
Thanks,
Rob
|
|
|
|
| Re: ProC, trace, bind variables [message #122805 is a reply to message #122738] |
Wed, 08 June 2005 10:21 |
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
|
Senior Member |
|
|
Hi,
I think we're starting to close in on this topic.
Taking my previous example, I simply added an out cursor so that I could be sure the sqlplus session had another cursor open.
The code is as follows:
variable bindvar2 number;
begin
:bindvar2:=999;
end;
/
set serveroutput on
declare
cursor mycur is select 2 from dual where rownum < :bindvar2;
begin
for myrow in ( select 2 from dual where rownum < :bindvar2 ) loop
for myrow2 in ( select
substr(sql.sql_text,1, 50) || ' ' ||
sqlbd.cursor_num || ' ' ||
sqlbd.position || ' ' ||
sqlbd.datatype || ' ' ||
sqlbd.value result
from
v$sql sql,
v$sql_cursor sqlc ,
v$sql_bind_data sqlbd
where
sql.address=sqlc.parent_handle and
sqlbd.cursor_num = sqlc.curno ) loop
dbms_output.put_line( substr(myrow2.result,1,255) );
end loop;
end loop;
end;
/
Which should produce the following output:
| Quote: |
declare cursor mycur is select 2 from dual where 1 1 2 999
SELECT SUBSTR(SQL.SQL_TEXT,1, 50) || ' ' || SQLBD. 3 0 0
SELECT 2 FROM DUAL WHERE ROWNUM < :B1 2 1 2
|
I puzzled over this for a minute because I noticed two of the cursors didn't have any any bind_data output. So I pulled a select on the v$sql_bind_data view and noticed there are 64 rows, even though there are no cursors with bind variables open. So it appears v$sql_bind_data keeps 64 rows of cache (if you like).
So, taking a look at the results above again:
- line 1 is the user defined cursor and has the bind data.
- line 2 is the inner curosr and has no bind data which is what you would expect.
- line 3 (the interesting one) has a cursor with no bind data. I re-visited the definition of v$sql_bind_data, and there it states that only user defined bind are published. I believe the select statements embedded with for-loop statements are have automatic/implicit cursors, and hence it kind of makes sense the bind variable would be treated differently by pl/sql. - I think.
Anyway, I think this proves the Oracle documentation is correct in that bind data is available for all session cursors.
Next problem is why can't you see your bind data.
The bind data can only be available if the cursor has been prepared and the input variables bound, also the cursor must not have been closed.
So you'd have to do something like
EXEC SQL DECLARE C CURSOR FOR S;
EXEC SQL OPEN C USING :deptno;
call procedure to iterate over the v$sql_bind_data view
EXEC SQL FETCH C INTO :ename;
EXEC SQL CLOSE C;
etc
Hope this helps.
Instead of busting yourself over this, have you considered using the Oracle tracing facility? This can be used to capture your SQL statements and the associated bind data.
Kind regards
Michael Hartley http://www.openfieldsolutions.co.uk
|
|
|
|
Goto Forum:
Current Time: Sun Feb 02 10:54:12 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
