Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: Problem during report running queries

    Hi All,
    I use Oracle 10g on Solaris OS and I need to monitor running query with bind variable values.
    I use the field V$SQL.SQL_TEXT to get the current query and I get the value of bind variable from V$SQL_BIND_CAPTURE view (fields : NAME,VALUE_STRING, DATATYPE_STRING) using the join V$SQL.SQL_ID = V$SQL_BIND_CAPTURE.SQL_ID.

    The problem is the following :
    when I use lots of queries (many insert with different values) i look all the insert command but the values of bind variables has always the same value (the first value used).
    Please, Can someone help me
    Many Thanks
    Mitia

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    V$SQL_BIND_CAPTURE

    "Reference to the cursor defining the bind variable
    (hash_value, address) for the parent cursor and (hash_value, child_address) for the child cursor."

    are you getting the correct child cursor values?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2012
    Posts
    2

    Red face

    Hi Anacedent,
    many thanks for your prompt answer, only today I try the query using your information.
    I think that the join between V$SQL and V$SQL_BIND_CAPTURE must use the clause :
    V$SQL.SQL_ID= V$SQL_BIND_CAPTURE.SQL_ID AND
    V$SQL.HASH_VALUE = V$SQL_BIND_CAPTURE.HASH_VALUE AND\
    V$SQL.ADDRESS = V$SQL_BIND_CAPTURE.CHILD_ADDRESS

    I have the query like the following :
    update <table> set M2=1 where FIELD1= 'GGGSGS' AND FIELD2=1 AND FIELD3='TEST';
    for FIELD2 in {1,2,......,750}

    with the following query :
    select T0.SQL_TEST,T1.NAME,T1.VALUES_STRING,T1.DATATYPE_S TRING
    from V$SQL T0, V$SQL_BIND_CAPTURE T1
    WHERE T0.SQL_TEXT LIKE 'update <table> %' AND
    T0.SQL_ID = T1.SQL_ID AND
    T0.HASH_VALUE = T1.HASH_VALUE;

    the results are :
    update <table> set M2=1 where FIELD1= :b0 AND FIELD2=:b1 AND FIELD3=:b2;
    :B0
    GGGSGS
    VARCHAR2(128)
    update <table> set M2=1 where FIELD1= :b0 AND FIELD2=:b1 AND FIELD3=:b2;
    :B1
    101
    NUMBER
    update <table> set M2=1 where FIELD1= :b0 AND FIELD2=:b1 AND FIELD3=:b2;
    :B2
    TEST
    VARCHAR2(128)

    if I run again the above query I get the same value, but the value of FIELD2 increase from 1 to 750

    Please can you help me.
    Many Thanks
    Mitia64

Posting Permissions

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