Solved

Extremely Urgent: Bind variable and V$SQL_TEXT problem

Posted on 2002-03-18
9
640 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
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'.  

Sorry.
0
 

Author Comment

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

Expert Comment

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

 
LVL 7

Expert Comment

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

If you need further information, I can detail using an example.
0
 

Author Comment

by:mfarid2
ID: 6880145
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
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)
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
ID: 6880521
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
ID: 6899922
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
ID: 7056509
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.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

808 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