Home » SQL & PL/SQL » SQL & PL/SQL » Bind Variables
Bind Variables [message #188309] Thu, 17 August 2006 21:11 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
im currently a script which on main has an update statement, its inside a loop that provides it the begin and end date, i've select the sql statement like this


CTS@ctstestnew:SQL>select sql_text from v$sqlarea
  2  where address = '5E1DD910'
  3  /

SQL_TEXT
--------------------------------------------------------------------------------

UPDATE CTS_SNAPSHOT_TBL SET CONTAINER_NUMBER = CONTAINER_NUMBER       WHERE BOOK

ING_DATE       BETWEEN :b2 AND :b1





can i get what the value of :b2 and :b1 currently is? i need it for me to know what month and year it is currently updating, thanks sir/mam =)
Re: Bind Variables [message #188349 is a reply to message #188309] Fri, 18 August 2006 02:58 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
I don't think you can get this info without turning on Fine grained auditing.
look at the dbms_fga package

Jim
Re: Bind Variables [message #188355 is a reply to message #188349] Fri, 18 August 2006 03:16 Go to previous messageGo to next message
orafaqer
Messages: 48
Registered: July 2006
Member
JSI2001 wrote on Fri, 18 August 2006 11:58

I don't think you can get this info without turning on Fine grained auditing.
look at the dbms_fga package

Jim

Another option is to set up 10046 trace at level 4 and to look at raw trace file.
Re: Bind Variables [message #188358 is a reply to message #188355] Fri, 18 August 2006 03:25 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

Another option is to set up 10046 trace at level 4 and to look at raw trace file.

Something I really need to gen up on. Cheers. Can you set that up for a specific column in a table?

Jim
Re: Bind Variables [message #188359 is a reply to message #188358] Fri, 18 August 2006 03:42 Go to previous messageGo to next message
orafaqer
Messages: 48
Registered: July 2006
Member
JSI2001 wrote on Fri, 18 August 2006 12:25

Quote:

Another option is to set up 10046 trace at level 4 and to look at raw trace file.

Something I really need to gen up on. Cheers. Can you set that up for a specific column in a table?

Jim

Of course no. It's a generic approach for catching binds. Besides it doesn't require EE option.
PS. KISS philosophy is really nice Smile
Re: Bind Variables [message #188360 is a reply to message #188359] Fri, 18 August 2006 03:45 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Yep that's what I thought, That would make fga better, as you are only auditing a very focused set of tasks i.e. only auditing a single column, which would have (I would think)a lot less overhead.

Jim
As well as being more simplistic Smile

[Updated on: Fri, 18 August 2006 03:45]

Report message to a moderator

Re: Bind Variables [message #188362 is a reply to message #188360] Fri, 18 August 2006 03:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I may have misunderstood the OP, but my idea was that he simply wanted to follow progress of a long-running job...

If I DID understand it correctly: look into using DBMS_APPLICATION_INFO for that.
Re: Bind Variables [message #188424 is a reply to message #188309] Fri, 18 August 2006 08:55 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You don't mention what version of the db you are using, but if you are on 10g, you can use the following to find bind values:

SQL> variable vname varchar2(20);
SQL> exec :vname := 'ALLEN';

PL/SQL procedure successfully completed.

SQL> select /* TAG */ empno from scott.emp where ename = :vname;

     EMPNO
----------
      7499

SQL> select datatype_string, value_string
  2     from v$sqlarea a, v$sql_bind_capture b
  3     where a.sql_text like ('%/* TAG */%')
  4     and a.hash_value = b.hash_value
  5     and a.address = b.address;

DATATYPE_STRING VALUE_STRING
--------------- ------------------------------
VARCHAR2(32)    ALLEN
Re: Bind Variables [message #188829 is a reply to message #188424] Mon, 21 August 2006 23:24 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
thanks for the reply, yep im trying to trace the current part of the script, i dont know why but i've run this code for 3 days but not even a single record have been inserted to my log table, here's the code:



declare
  l_yy number;
  l_mm number;
  l_sd number;
  l_ed number;
  l_begin date;
  l_end date;
begin
  /*
  --for 2006  
  l_yy := 2006;
  for i in 3..8 loop
      l_mm := i;
      l_sd := 1;
      
      l_begin :=  to_date(l_mm||'/'||l_sd||'/'||l_yy, 'MM/DD/YYYY');
      l_end := last_day(to_date(l_mm||'/'||l_sd||'/'||l_yy, 'MM/DD/YYYY'));
      
      UPDATE CTS_SNAPSHOT_TBL SET CONTAINER_NUMBER = CONTAINER_NUMBER
      WHERE BOOKING_DATE
      BETWEEN l_begin AND l_end; 
      
      COMMIT;      
  end loop;
  */
  --for 2005  
  l_yy := 2005;
  for i in 2..12 loop
      l_mm := i;
      l_sd := 1;
      l_ed := 15;
      
      l_begin :=  to_date(l_mm||'/'||l_sd||'/'||l_yy, 'MM/DD/YYYY');
      --l_end := last_day(to_date(l_mm||'/'||l_sd||'/'||l_yy, 'MM/DD/YYYY'));
      l_end :=  to_date(l_mm||'/'||l_ed||'/'||l_yy, 'MM/DD/YYYY');
      
      UPDATE CTS_SNAPSHOT_TBL SET CONTAINER_NUMBER = CONTAINER_NUMBER
      WHERE BOOKING_DATE
      BETWEEN l_begin AND l_end; 
      
      insert into mlog values(l_begin, l_end);
      
      COMMIT;
      
      
      
      l_sd := 16;      
      l_begin :=  to_date(l_mm||'/'||l_sd||'/'||l_yy, 'MM/DD/YYYY');
      l_end := last_day(to_date(l_mm||'/'||l_sd||'/'||l_yy, 'MM/DD/YYYY'));
      
      UPDATE CTS_SNAPSHOT_TBL SET CONTAINER_NUMBER = CONTAINER_NUMBER
      WHERE BOOKING_DATE
      BETWEEN l_begin AND l_end; 
      
      insert into mlog values(l_begin, l_end);
      
      COMMIT;      
  end loop;
  
  --for 2004  
  l_yy := 2004;
  for i in 1..12 loop
      l_mm := i;
      l_sd := 1;
      l_ed := 15;
      
      l_begin :=  to_date(l_mm||'/'||l_sd||'/'||l_yy, 'MM/DD/YYYY');
      --l_end := last_day(to_date(l_mm||'/'||l_sd||'/'||l_yy, 'MM/DD/YYYY'));
      l_end :=  to_date(l_mm||'/'||l_ed||'/'||l_yy, 'MM/DD/YYYY');
      
      dbms_output.put_line(l_begin);
      dbms_output.put_line(l_end);
      UPDATE CTS_SNAPSHOT_TBL SET CONTAINER_NUMBER = CONTAINER_NUMBER
      WHERE BOOKING_DATE
      BETWEEN l_begin AND l_end; 
      
      insert into mlog values(l_begin, l_end);
      
      COMMIT;
      
            
      l_sd := 16;      
      l_begin :=  to_date(l_mm||'/'||l_sd||'/'||l_yy, 'MM/DD/YYYY');
      l_end := last_day(to_date(l_mm||'/'||l_sd||'/'||l_yy, 'MM/DD/YYYY'));
      
      dbms_output.put_line(l_begin);
      dbms_output.put_line(l_end);
      
      UPDATE CTS_SNAPSHOT_TBL SET CONTAINER_NUMBER = CONTAINER_NUMBER
      WHERE BOOKING_DATE
      BETWEEN l_begin AND l_end; 
      
      insert into mlog values(l_begin, l_end);
      
      COMMIT;      
  end loop;
end;




mlog is just a table with two date columns, i've tried commenting out the UPDATE statements just to see if it will insert on the table. without the updates the dates are being inserted to mlog, when i try to run it as that code nothing is inserted to mlog, i also tried to message it using dbms_output and it displays the value of l_begin and l_end, the update statement are just being run so that the trigger on the table be fired.
Re: Bind Variables [message #188844 is a reply to message #188829] Tue, 22 August 2006 01:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If it's run for 3 days, then I suspect that some other session had a lock on CTS_SNAPSHOT_TBL and you were simply stuck behind that.

How many rows are there in CTS_SNAPSHOT_TBL?

Re: Bind Variables [message #188853 is a reply to message #188844] Tue, 22 August 2006 02:34 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
its


SQL> select count(*) from cts_snapshot_tbl;

  COUNT(*)
----------
   1293053



when i query the v$sqlarea it takes a long time on mainlog, cts_snapshot_tbl has some triggers that performs an update on mainlog table, this mainlog has


SQL> select count(*) from mainlog;

  COUNT(*)
----------
   5997905




im trying to use that trace level 4, using dbms_system, on my test db i tried executing this



SYS@XE:SQL>exec dbms_system.set_sql_trace_in_session(35,17,true);

PL/SQL procedure successfully completed.

SYS@XE:SQL>exec dbms_system.set_ev(35,17,10046,4,'');

PL/SQL procedure successfully completed.



and then after running my test code on another session, i got a trace file of it, then i run tkprof on it, then on the trace file i got something like this



INSERT INTO MLOG 
VALUES
(:B2 , :B1 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute   1998      1.35       1.51          0          9       6060        1998
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2000      1.35       1.51          0          9       6060        1998

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 36     (recursive depth: 1)




i didnt see the values of the bind variables, where could i find it in the trace files? is there an option on the tkprof that i should set? thanks again =)
Re: Bind Variables [message #188858 is a reply to message #188853] Tue, 22 August 2006 03:13 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
got it! its in the trace file no need to tkprof it =) thanks also to this link =)

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4254158186083


but im just thinking if its possible to limit the "level" of tracing, i mean for example i have a procedure that contains some updates and insert statements

procedure a as
begin
update statement..<=== now i only want to trace this particular statement, also i dont want to trace the statements of the triggers on it, bcoz on my case the DML's on the triggers are accessing another huge table that's causing my trace file to get really big =)

insert statement...
end;

thank you so much again sir/mam for your help =)



also if my observations are correct =)
(on windows xp 10g XE)

i can delete the trace file while still running dbms_system on it, and after i deleted it, and rerun my code, no trace file is generated, i first need to stop the dbms_system, then i reconnect the session running my code, then setup it all again for the trace file to be produced again =)

[Updated on: Tue, 22 August 2006 04:09]

Report message to a moderator

Previous Topic: How to update CLOB column having more than 4000 chars
Next Topic: Is there a more efficient way to do this?
Goto Forum:
  


Current Time: Tue Dec 06 00:26:31 CST 2016

Total time taken to generate the page: 0.10717 seconds