v$ views [message #517057] |
Fri, 22 July 2011 03:14 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |
![GETRITESH00%40YAHOO.COM](/forum/theme/orafaq/images/yahoo.png)
|
|
Hi,
Please provide answers to these questions
1) Which v$view gives information about the parameters (bind variables) passed to the called stored procedure
Regards,
Ritesh
|
|
|
|
|
|
Re: v$ views [message #517089 is a reply to message #517074] |
Fri, 22 July 2011 07:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |
![GETRITESH00%40YAHOO.COM](/forum/theme/orafaq/images/yahoo.png)
|
|
Below is a small demo proving that Oracle will only capture bind values used in the WHERE clause and all the other bind variables under SELECT list are ignored.
SQL> conn scott/tiger
Connected.
SQL> variable a number;
SQL> variable b number;
SQL> variable x number;
SQL> exec :a :=0; :b := 0; := 7369;
PL/SQL procedure successfully completed.
SQL> select job, nvl(empno, :a), nvl(comm, :b) from emp where empno = :x;
JOB NVL(EMPNO,:A) NVL(COMM,:B)
--------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
CLERK 7369 0
SQL> select sql_id, sql_text from v$sql where sql_text like 'select job, nvl(empno,%';
SQL_ID
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
3d1079n1gakc1
select job, nvl(empno, :a), nvl(comm, :b) from emp where empno =
SQL> column name format a10
SQL> column VALUE_STRING format a20
SQL> select name, position, datatype_string, last_captured, value_string from v$sql_bind_capture
2 where sql_id = '3d1079n1gakc1' order by position;
NAME POSITION DATATYPE_STRING LAST_CAP VALUE_STRING
--------------------------------------------------------------------------------
:A 1 NUMBER
:B 2 NUMBER
:x 3 NUMBER 03/06/08 7369
Regards,
Ritesh
[Updated on: Fri, 22 July 2011 07:17] Report message to a moderator
|
|
|
Re: v$ views [message #517092 is a reply to message #517089] |
Fri, 22 July 2011 07:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Yes, I forgot to mention that too.
Oracle stores only bind variables that are relevant to optimize the execution plan.
Regards
Michel
|
|
|