Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Red face Unanswered: Bind Mismatch Issue

    Hi,
    I received a mail from our DBA saying that below query always reparsed by Oracle due to "bind mismatch" problem although we are using bind variables.

    Code is -

    There are 2 types of code which we used to insert data into tables.
    Code1 with bind variables -


    Code:
    String hqlUpdate = "update " + boSource + " a set tibStatus = :loadStatusString where tibStatus = :intermediateStatusString and "+selectMaxOf +" from " + boSource
    + " where tibStatus = a.tibStatus " + sqlkeyCriteria + ")";
    HashMap hParamMap = new HashMap();
    hParamMap.put("loadStatusString", "LOAD");
    hParamMap.put("intermediateStatusString", "INTR");
    objectPS.executeQuery(hqlUpdate, hParamMap);
    objectPS.flush();



    Code2 without bind variables -


    Code:
    private void logErrorInDB(BOErrorLog objBOErrorLog, String userCode)
    {
    logger.logInfo("logErrorInDB", "Entering the method");
    objBOErrorLog.setCreatedBy(userCode);
    objBOErrorLog.setCreatedOn(new Date());
    objBOErrorLog.setModifiedBy(userCode);
    objBOErrorLog.setModifiedOn(new Date());
    objectPS.saveOrUpdate(objBOErrorLog);
    logger.logInfo("logErrorInDB", "Exiting the method");
    }



    generated SQL - Provided by DBA


    Code:
    insert into ERROR_LOG (OBJECT_CODE, OBJECT_KEY, PROCESS_CODE, ERROR_CODE, DESCRIPTION, CREATED_BY, CREATED_ON, MODIFIED_BY, MODIFIED_ON, id) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10);


    Please let me know why i am getting bind mismatch with bind variables itself? Or is there any other way to rewrite the above code to get rid of this problem.
    Thanks in advance.

  2. #2
    Join Date
    Aug 2009
    Posts
    262
    Varying in lists...
    Varying in lists. This is such a frequently asked question on asktom that I’ve decided just to write up the various ways you can do this.

    First the problem statement: You have a string, it looks like

    * 1, 2, 3, 4
    * ‘A’, ‘B’, ‘C’

    Or something similar. You would like to retrieve all rows from some table/query such that some column is in that string. That is, you would like to execute:

    * select * from t where x in (1,2,3,4)
    * select * from t where y in (‘A’,’B’,’C’)

    You would like to use bind variables (because you’ve heard through the grapevine that bind variables are “good”). However, when you try:


    SQL> variable txt varchar2(25)
    SQL> exec :txt := ' ''SYS'', ''SYSTEM'' '
    PL/SQL procedure successfully completed.

    SQL> print txt

    TXT
    --------------------------------
    'SYS', 'SYSTEM'

    SQL> select *
    2 from all_users
    3 where username in (:txt);
    no rows selected

    SQL> select *
    2 from all_users
    3 where username in ('SYS','SYSTEM');

    USERNAME USER_ID CREATED
    ---------- ---------- ---------
    SYS 0 30-JUN-05
    SYSTEM 5 30-JUN-05


    It does not seem to work at all. When you “bind” the inlist – no data, when you hard code it – data is found. The reason – well, that should be clear, the example above that used the bind variable in this case is equivalent to this query with literals:

    SQL> select *
    2 from all_users
    3 where username in ( ' ''SYS'', ''SYSTEM'' ' );
    no rows selected

    There is a single string, a single value in that in list. What we need to do is turn that into a “set”. Here are some approaches you can take.

    If you have a finite number of items in the in-list
    By all means just bind the individual elements. That is, in the above example, suppose we let the user pick up to 10 items in a pick list. I would strongly encourage the query you use to be:

    Select * from all_users where username in ( :bv1, :bv2, :bv3, … :bv10 );

    And you would NOT use a single string, you would bind 10 inputs to this query (binding NULLs for any bind variable they did not set a value for). This works well for small lists – it would be quite tedious obviously for dozens or hundreds of items.

    If you are in Oracle 8i
    We can use a function that returns a collection. We’ll make it so that we can “query a string” – use the string as if it were a table itself. The concept goes like this. We’ll need a collection type (I’ll just use a table of varchar2(4000)), and a PLSQL function that will parse a delimited string and return it as a collection:


    SQL> create or replace type str2tblType as table of varchar2(4000)
    2 /
    Type created.

    SQL> create or replace
    2 function str2tbl
    3 ( p_str in varchar2,
    4 p_delim in varchar2 default ',' )
    5 return str2tblType
    6 as
    7 l_str long default p_str || p_delim;
    8 l_n number;
    9 l_data str2tblType := str2tblType();
    10 begin
    11 loop
    12 l_n := instr( l_str, p_delim );
    13 exit when (nvl(l_n,0) = 0);
    14 l_data.extend;
    15 l_data(l_data.count) := ltrim(rtrim(substr(l_str,1,l_n-1)));
    16 l_str := substr( l_str, l_n+1 );
    17 end loop;
    18 return l_data;
    19 end;
    20 /
    Function created.

    SQL> column column_value format a10
    SQL> exec :txt := 'SYS, SYSTEM'
    PL/SQL procedure successfully completed.

    SQL> select *
    2 from TABLE( cast( str2tbl(:txt) as str2TblType ) )
    3 /

    COLUMN_VAL
    ----------
    SYS
    SYSTEM

    SQL> select *
    2 from all_users
    3 where username in
    4 (select *
    5 from TABLE( cast( str2tbl(:txt) as str2TblType ) )
    6 )
    7 /

    USERNAME USER_ID CREATED
    ---------- ---------- ---------
    SYS 0 30-JUN-05
    SYSTEM 5 30-JUN-05


    If you are in 9iR2 and above
    Then we can skip the function all together and just use DUAL to generate rows and parse the string. Consider:

    SQL> select level l
    2 from dual
    3 connect by level <= 5;

    L
    ----------
    1
    2
    3
    4
    5

    So, we can use DUAL to generate rows and then using substr/instr – effectively parse the bind variable and return the i'th element from it. For example:

    SQL> exec :txt := 'SYS, SYSTEM'
    PL/SQL procedure successfully completed.

    SQL> with data
    2 as
    3 (
    4 select
    5 trim( substr (txt,
    6 instr (txt, ',', 1, level ) + 1,
    7 instr (txt, ',', 1, level+1)
    8 - instr (txt, ',', 1, level) -1 ) )
    9 as token
    10 from (select ','||:txt||',' txt
    11 from dual)
    12 connect by level <=
    13 length(:txt)-length(replace(:txt,',',''))+1
    14 )
    15 select * from data;

    TOKEN
    ----------------------------------
    SYS
    SYSTEM

    Once we have that accomplished – the rest is easy:

    SQL> with data
    2 as
    3 (
    4 select
    5 trim( substr (txt,
    6 instr (txt, ',', 1, level ) + 1,
    7 instr (txt, ',', 1, level+1)
    8 - instr (txt, ',', 1, level) -1 ) )
    9 as token
    10 from (select ','||:txt||',' txt
    11 from dual)
    12 connect by level <=
    13 length(:txt)-length(replace(:txt,',',''))+1
    14 )
    15 select *
    16 from all_users
    17 where username in (select * from data);

    USERNAME USER_ID CREATED
    ---------- ---------- ---------
    SYSTEM 5 30-JUN-05
    SYS 0 30-JUN-05

    Now, some people look at that “with data” bit and say “that is too much, too hard to code that every time”. We can use a VIEW to hide the complexity here – and use a stored procedure as our way to “bind to the view” (this is sort of a parameterized view in effect). It would look like this:

    SQL> create or replace context my_ctx using my_ctx_procedure
    2 /
    Context created.

    SQL> create or replace
    2 procedure my_ctx_procedure
    3 ( p_str in varchar2 )
    4 as
    5 begin
    6 dbms_session.set_context
    7 ( 'my_ctx', 'txt', p_str );
    8 end;
    9 /
    Procedure created.

    SQL> create or replace view IN_LIST
    2 as
    3 select
    4 trim( substr (txt,
    5 instr (txt, ',', 1, level ) + 1,
    6 instr (txt, ',', 1, level+1)
    7 - instr (txt, ',', 1, level) -1 ) )
    8 as token
    9 from (select ','||sys_context('my_ctx','txt')||',' txt
    10 from dual)
    11 connect by level <=
    12 length(sys_context('my_ctx','txt'))
    13 -length(replace(sys_context('my_ctx','txt'),',','') )+1
    14 /
    View created.

    SQL> exec my_ctx_procedure( :txt )
    PL/SQL procedure successfully completed.

    SQL> select *
    2 from all_users
    3 where username in
    4 (select * from IN_LIST);

    USERNAME USER_ID CREATED
    ---------- ---------- ---------
    SYSTEM 5 30-JUN-05
    SYS 0 30-JUN-05

    Now, you “bind” your queries in-list by calling MY_CTX_PROCEDURE and the view does the rest

  3. #3
    Join Date
    Nov 2009
    Posts
    3
    Thanks for the reply.

    It would be better if you could suggest the changes in my code itself.

  4. #4
    Join Date
    Aug 2009
    Posts
    262
    actually this is the job of your DBA .

    what you people pay him for ?

  5. #5
    Join Date
    Nov 2009
    Posts
    3
    Since our code is responsible for creating bind mismatch issue, so its our responsibility to get this corrected.
    Could you please help me...

Tags for this Thread

Posting Permissions

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