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);