OraFAQ Forum: SQL & PL/SQL » Bind Variables

Home » SQL & PL/SQL » SQL & PL/SQL » Bind Variables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Bind Variables [message #188309]

Thu, 17 August 2006 21:11

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 =)

Report message to a moderator

Re: Bind Variables [message #188349 is a reply to message #188309]

Fri, 18 August 2006 02:58

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

Report message to a moderator

Re: Bind Variables [message #188355 is a reply to message #188349]

Fri, 18 August 2006 03:16

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.

Report message to a moderator

Re: Bind Variables [message #188358 is a reply to message #188355]

Fri, 18 August 2006 03:25

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

Report message to a moderator

Re: Bind Variables [message #188359 is a reply to message #188358]

Fri, 18 August 2006 03:42

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

Report message to a moderator

Re: Bind Variables [message #188360 is a reply to message #188359]

Fri, 18 August 2006 03:45

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

Frank
Messages: 7901
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.

Report message to a moderator

Re: Bind Variables [message #188424 is a reply to message #188309]

Fri, 18 August 2006 08:55

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

Report message to a moderator

Re: Bind Variables [message #188829 is a reply to message #188424]

Mon, 21 August 2006 23:24

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.

Report message to a moderator

Re: Bind Variables [message #188844 is a reply to message #188829]

Tue, 22 August 2006 01:52

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?

Report message to a moderator

Re: Bind Variables [message #188853 is a reply to message #188844]

Tue, 22 August 2006 02:34

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 =)

Report message to a moderator

Re: Bind Variables [message #188858 is a reply to message #188853]

Tue, 22 August 2006 03:13

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 01:26:25 CST 2024