Home » Developer & Programmer » Precompilers, OCI & OCCI » ProC, trace, bind variables
ProC, trace, bind variables [message #122029] Thu, 02 June 2005 08:14 Go to next message
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 #122182 is a reply to message #122029] Fri, 03 June 2005 10:33 Go to previous messageGo to next message
Michael Hartley
Messages: 110
Registered: December 2004
Location: West Yorkshire, United Ki...
Senior Member

Hi,

you have to work with V$SQL_CURSOR and V$SQL_BIND_DATA to identify the current open cursor, then iterate over the bind_data to get your values.

kind regards

Michael Hartley http://www.openfieldsolutions.co.uk
Re: ProC, trace, bind variables [message #122573 is a reply to message #122182] Tue, 07 June 2005 06:24 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.Sad((

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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:

  1. line 1 is the user defined cursor and has the bind data.
  2. line 2 is the inner curosr and has no bind data which is what you would expect.
  3. 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

Previous Topic: Pro C & Oracle 9i problem
Next Topic: OCCI-10.1.0.3 link failed under X86_64(Intel EM64T)
Goto Forum:
  


Current Time: Wed Dec 07 03:27:46 CST 2022