Extremely Urgent: Bind variable and V$SQL_TEXT problem

Posted on 2002-03-18
Medium Priority
Last Modified: 2009-07-13
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.
Question by:mfarid2
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1

Expert Comment

ID: 6877864
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'.  


Author Comment

ID: 6877953
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.

Expert Comment

ID: 6877984
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?
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


Expert Comment

ID: 6878711
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.

Author Comment

ID: 6880145
waynezhu, would very much appreciate an example as I am really trying to solve this ASAP.

Accepted Solution

waynezhu earned 1200 total points
ID: 6880347
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

Expert Comment

ID: 6880521

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....


Expert Comment

ID: 6899922

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


Author Comment

ID: 7056509
Did not answer my question but I am giving you the points for your effort.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question