Solved

Extremely Urgent: Bind variable and V$SQL_TEXT problem

Posted on 2002-03-18
9
619 Views
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:

WHERE SUB_CODE = :b1

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:

WHERE UPPER(SQL_TEXT) LIKE '%EU%'

100 extra points for anyone who answers correctly within the next hour.
0
Comment
Question by:mfarid2
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 5

Expert Comment

by:kelfink
Comment Utility
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'.  

Sorry.
0
 

Author Comment

by:mfarid2
Comment Utility
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.
0
 
LVL 5

Expert Comment

by:kelfink
Comment Utility
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 );
(hangs...)

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?
0
 
LVL 7

Expert Comment

by:waynezhu
Comment Utility
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,
.
.
.
        value=EU
.
.
.

If you need further information, I can detail using an example.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:mfarid2
Comment Utility
waynezhu, would very much appreciate an example as I am really trying to solve this ASAP.
0
 
LVL 7

Accepted Solution

by:
waynezhu earned 300 total points
Comment Utility
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)
Regards,
Wayne

---------------------------------------------------------
--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.0.1.1.1 - Production vsnsta=0
vsnsql=10 vsnxtr=3
Windows 2000 Version 5.0 , CPU type 586
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - 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'
END OF STMT
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
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=45902408
BINDS #1:
 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
   value="EU"
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'
END OF STMT
PARSE #1:c=10014,e=10000,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=59682408
BINDS #1:
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=59682408
0
 
LVL 5

Expert Comment

by:kelfink
Comment Utility
mfarid2,

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

thanks
0
 
LVL 5

Expert Comment

by:sora
Comment Utility
mfarid2

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

sora
0
 

Author Comment

by:mfarid2
Comment Utility
Did not answer my question but I am giving you the points for your effort.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now