Extremely Urgent: Bind variable and V$SQL_TEXT problem

I am trying to query the sql_text column in V$SQLTEXT system table to see if a certain string exists. This string is a parameter to the stored procedure that I call. The sql_text column shows something like this:


b1 is the bind variable. The actual parameter value is 'EU'

How do I do a query to actually test to see if sql_text column has the value of the bind variable? I would like
to do something like:


100 extra points for anyone who answers correctly within the next hour.
Who is Participating?
Below is an example for tracing the current session.
(It has been kept as simple and short as possible).
(NB. If you need to trace a running session other than
the current session, you have to use oradebug or dbms_system.set_ev. Let me know if it is this situation)

--Step 0
--Just prepare a bind variable in sql*plus, in this case,
--b1 is initilaized with a value 'EU'. It is not
--necessary step. In your case, you just need run the
--procedure in the step 2.
SQL> var b1 char(2);
SQL> begin
  2  :b1 := 'EU';
  3  end;
  4  /

PL/SQL procedure successfully completed.

--Step 1
--Turn on the trace at level 4
SQL> alter session set events '10046 trace name context forever, level 4';

Session altered.

--Step 2
--Run a sample query
--In your case, just run you procedure
SQL> select * from dual where dummy=:b1;

no rows selected

--Step 3
--Turn off the trace
SQL> alter session set events '10046 trace name context off';
Session altered.

--Step 4
--locate the trace file in the directory (on Unix/Linux
--you may use 'ls -lt', or grep if many file in the
--directory, on Windows you can do the simplar thing)
--speficied by user_dump_dest. Its content is pasted
--and cut below. You will see the dump file contains
--many information: such as file name, Oracle Version,
--OS type, ...,. Below you will what SQL statement
--has been traced, and what are the value of bind
--variable is. In this case, you see value="EU".

Dump file D:\oracle\admin\dw\udump\ORA00420.TRC
Tue Mar 19 11:28:42 2002
ORACLE V9. - Production vsnsta=0
vsnsql=10 vsnxtr=3
Windows 2000 Version 5.0 , CPU type 586
Oracle9i Enterprise Edition Release - Production
With the Partitioning option
JServer Release - Production
Windows 2000 Version 5.0 , CPU type 586
Instance name: dw

Redo thread mounted by this instance: 1

Oracle process number: 12

Windows thread id: 420, image: ORACLE.EXE

*** 2002-03-19 11:28:42.000
*** SESSION ID:(7.64) 2002-03-19 11:28:42.000
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
PARSING IN CURSOR #1 len=68 dep=0 uid=5 oct=42 lid=5 tim=36899408 hv=2045994513 ad='7a0e5f08'
alter session set events '10046 trace name context forever, level 4'
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=36849408
PARSING IN CURSOR #1 len=34 dep=0 uid=5 oct=3 lid=5 tim=45902408 hv=2583278620 ad='7a12590c'
select * from dual where dummy=:b1
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=45902408
 bind 0: dty=96 mxl=32(02) mal=00 scl=00 pre=00 oacflg=03 oacfl2=10 size=32 offset=0
   bfp=076a005c bln=32 avl=02 flg=05
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=45932408
FETCH #1:c=0,e=0,p=0,cr=1,cu=2,mis=0,r=0,dep=0,og=4,tim=45942408
STAT #1 id=1 cnt=0 pid=0 pos=0 obj=221 op='TABLE ACCESS FULL DUAL '
PARSING IN CURSOR #1 len=56 dep=0 uid=5 oct=42 lid=5 tim=59682408 hv=3475487367 ad='7a101134'
alter session set events '10046 trace name context off'
PARSE #1:c=10014,e=10000,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=59682408
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=59682408
Oracle doesn't keep the value with the bind variable, only with the Cursor object, and I've never heard of anyone querying the cursorss to find information like this.

V$SQLTEXT is a detailed view of the v$SQLAREA.  It lets oracle see whether it can re-use a statement plan, and avoid re-parsing an incoming SQL statement.  If Oracle stored the parameter value with this record, it would inhibit the use of other bind variables when re-executing the statement.  It couldn't store : user1 is executing using variable 'EU' and user2 is using 'US'.  

mfarid2Author Commented:
kelfink, thank you for your comment. Can you explain a bit more when you say "Oracle keeps the value with the cursor object". What I am trying to do is that, once a user has executed a stored procedure for a certain parameter and the stored parameter is running, I would like to prevent the user from calling the same procedure with the same parameter. The user can call the same procedure but with a different parameter only. It seems like if I can somehow query the cursor object, I can accomplish this.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Well, there's a complicated answer which I'm only sort of aware about, myself.  This involves learning about the views v$open_cursor, v$sql_bind_metadata, and v$sql_bind_data.  Joining these views may be able to let your procedure see what bind data the current session is using.  But that sounds like a very complicated way to get at what you want.  I'd like to suggest something different.

How about if you examing the parameters of the procedure, and create some appropriate lock, thus preventing other sessions from getting past that lock?  THis is like achieving thread safety, right?  Directly from your follow-up, it looks like you can create a dummy record in some new table, called, say, 'LOCK_DATA' and have the primary key for that table be a column similar to the parameter coming into the procedure.  When you start the procedure,
you insert into that table, and proceed as you like.
At the end of the procedure, you DELETE that lock record, then issue a commit.  

Here's a for-example:

Create a table:
create table lock_data ( i int  primary key );

Now, with 2 sessions of SQL*Plus open, do this:

sesssion1> insert into lock_data values ( 1 );
(1 row created.)
sesssion2> insert into lock_data values ( 1 );

session1> select * from lock_data ;  
session1> // do other stuff in the procedure...
session1> delete from lock_data values ( 1 );
session1> commit;

<session2 returns now, having created its row>
session2> do session2-stuff
session2> delete from lock_data values ( 1 );
session2> commit;

Does that work for you?
To my knowledge, the only way to find the value of bind variable is to use the sql trace by setting event 10046
at level 4 or 12, and then examine the raw trace file. There are many ways to turn on the sql trace at the
instance level or session level.
If it is development database, for an ease, you may simply set the event in the init.ora file and rebounce the db.
For production, you may have to choose using either dbms_system.set_ev or oradebug for specific sessions
or processes.
If you just run test at current session, you may use
alter session set events '10046 trace name context forever, level 4'; #or level 12

Locate the trace file, and in the trace file,  you will find something like, for example in you case,

If you need further information, I can detail using an example.
mfarid2Author Commented:
waynezhu, would very much appreciate an example as I am really trying to solve this ASAP.

Can you show us an example of the procedure you're trying to restrict?

It doesn't sound like we're helping you with the actual problem....


since you have received sufficient input, may be you should either close this question or provide additional information if required. In theory, I agree with kelfink's approach

mfarid2Author Commented:
Did not answer my question but I am giving you the points for your effort.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.