Forum Stats

  • 3,768,190 Users
  • 2,252,758 Discussions
  • 7,874,486 Comments

Discussions

Dynamically Passed index hints are failing in few scenarios using dbms_sql

User_54OH1
User_54OH1 Member Posts: 3 Employee
edited May 12, 2021 4:46AM in SQL & PL/SQL

I am trying to pass index hints dynamically to sql queries using dbms_sql.

Select :hint emp_name from EMPLOYEE emp; -> This is working fine

Below cases are failing while parsing (dbms_sql.parse)

ex:

 Select :hint count(1) from EMPLOYEE emp;
    Select :hint Distinct emp_name from EMPLOYEE emp;
    Select :hint emp.emp_name from EMPLOYEE emp;

Getting error: ORA-00923: FROM keyword not found where expected

How to handle index hints in the above failed cases?

Oracle version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

Here example hint:

l_sql_hint := /*+ full(emp) */

Code:

    curs_handle := dbms_sql.open_cursor;

    dbms_sql.parse(curs_handle, l_sql, dbms_sql.native);

    IF l_sql_hint IS NOT NULL THEN
        dbms_sql.bind_variable(curs_handle, ':hint', l_sql_hint);
    END IF;

    no_of_rows_affected := dbms_sql.EXECUTE(curs_handle);


Tagged:

Best Answer

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy

    Hints are not to be passed as bind variables. Nor can table names be passed as bind variables.

    I suggest you should do something like below:

    c_sql_template constant varchar2(32000):= q'[select <SQL_HINT> ... from ... where... ]';
    v_sql varchar2(32000);
    v_hint varchar2(1000);
    begin
    if... then v_hint:=...;
    
    elsif ... then v_hint:=...
    
    else v_hint:=...
    
    end if;
    
    v_sql:=replace(c_sql_template,'<SQL_HINT>', v_hint);
    
     curs_handle := dbms_sql.open_cursor;
    
        dbms_sql.parse(curs_handle, v_sql, dbms_sql.native);
    


  • BluShadow
    BluShadow Member, Moderator Posts: 41,474 Red Diamond
    Accepted Answer

    You're trying to use bind variables as if they are SQL*Plus substitution variables...


    Binding is for values, not for substituting pieces of code, or object names etc. in your statements. A hint is a piece of comment text in the statement, it's not a value to be bound in as a particular data type. Bind variables should be used for things like passing in a VARCHAR2 variable, a DATE variable, a NUMBER variable to be used where "values" are required.

    BEDE
  • BluShadow
    BluShadow Member, Moderator Posts: 41,474 Red Diamond


    Actually, I'd suggest investigating why index hints are being considered as required in the first place. If it's to try and improve performance, then I'd look at where the performance bottlenecks are and what has caused those. i.e. is it incorrect indexing? is it stale statistics? is it poor relational table design? and then tackle the underlying issue so that performance hints aren't required in the first place.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,581 Red Diamond
    Select :hint emp_name from EMPLOYEE emp; -> This is working fine
    

    This works as the bind variable supplies a string literal with the column or label name EMP_NAME for the SQL projection of the select cursor.


     Select :hint count(1) from EMPLOYEE emp;
        Select :hint Distinct emp_name from EMPLOYEE emp;
        Select :hint emp.emp_name from EMPLOYEE emp;
    

    This fails as the column/label names for the bind variable string literal are invalid label names for a SQL projection. Unless enclosed by double quotes.

    Bind variables are used as VALUES in a cursor - not as statement tokens to be parsed as part of the actual SQL statement.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy

    Besides what was already said, what you should think first is: does it have to be dynamic SQL? Why dynamic? Do table names vary? If table names vary, that would be a reason. But if table names vary that would make me think there is poor database design: such as having several tables when there should been only one table, eventually partitioned.

    Still, seeing that redesign of a system takes quite some effort and is too often rejected by management, then dynamic SQL turns out to be the way to go.

    Although, try as much as possible not to use dynamic SQL because it too often leads to runtime errors and is quite hard to debug.