See value of bind variables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: See value of bind variables

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    See value of bind variables

    How can I see the values of bind variables in SQL statements that we find through Top Activity button in Enterprise Manager Database Control?

    E.g:
    SELECT DISTINCT MAX(MRT.MRNCODE) FROM MRNITEMS MRT,MATERIALRECEIPTNOTE MNS WHERE MRT.MRNCODE = MNS.MRNCODE AND MRNDATE = :b1 AND PURCHASEFWDDATE IS NOT NULL
    lucky

  2. #2
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82
    Query view v$sql_bind_capture;

  3. #3
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Thanks. I have described v$sql_bind_capture. But I am not able to figure out how we would use this view to determine the value of bind variables in the above mentioned query.
    Actually I want to formulate a query similar to above query in which bind variables are replaced by run time values of these bind variables.
    lucky

  4. #4
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82

    select
    c.name, c.value_string
    from
    v$sqlarea a, v$sql_bind_capture c
    where
    a.sql_id = c.sql_id and
    upper(a.sql_text) = 'SELECT DISTINCT MAX(MRT.MRNCODE) FROM MRNITEMS MRT,MATERIALRECEIPTNOTE MNS WHERE MRT.MRNCODE = MNS.MRNCODE AND MRNDATE = :b1 AND PURCHASEFWDDATE IS NOT NULL';

  5. #5
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Thanks man.
    lucky

  6. #6
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82
    Glad to help.

    Regards,
    Aljaz

  7. #7
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Hi I found this one rather more informative. So, just wanted to share with u.

    select
    a.sql_fulltext, a.last_load_time,c.name, c.value_string
    from
    v$sql a, v$sql_bind_capture c
    where
    a.sql_id = c.sql_id and
    a.child_address=c.child_address and
    sql_text like
    'select P.partyname,P.partycode,Q.QUOTATIONCODE,Q.QUOT_NO,P.TOLERANCE from partymst P, QUOTATION Q %'
    lucky

  8. #8
    Join Date
    Jul 2009
    Posts
    3
    Hi,
    A level 4 trace also would help you give the bind variables.

    Thanks & Regards,
    Santhosh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width