Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dynamically Passed index hints are failing in few scenarios using dbms_sql

User_54OH1May 12 2021 — edited May 12 2021

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);
This post has been answered by BluShadow on May 12 2021
Jump to Answer

Comments

Post Details

Added on May 12 2021
5 comments
55 views