using Hint in a hosted variable?

Question

All,

I want to use a hint in a hosted variable; in fact, we need to use a dynamic value of the hint (Hint should be valued at runtime). can we write an sql statement from this:

SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b) 
           USE_NL (glcc glf) USE_MERGE (gp gsb) */
 b.application_id ,
 b.set_of_books_id ,
 b.personnel_id,
 p.vendor_id Personnel,
 p.segment1 PersonnelNumber,
 p.vendor_name Name
FROM  jl_br_journals j,
      jl_br_balances b,
      gl_code_combinations glcc,
      fnd_flex_values_vl glf,
      gl_periods gp,
      gl_sets_of_books gsb,
      po_vendors p
WHERE

to :

SELECT /*+ :hosted_hintp */
 b.application_id ,
 b.set_of_books_id ,
 b.personnel_id,
 p.vendor_id Personnel,
 p.segment1 PersonnelNumber,
 p.vendor_name Name
FROM  jl_br_journals j,
      jl_br_balances b,
      gl_code_combinations glcc,
      fnd_flex_values_vl glf,
      gl_periods gp,
      gl_sets_of_books gsb,
      po_vendors p
WHERE

hosted_hintp contain the value of hint we need at runtime.

thanks

I'm not sure that I understand the context. Are you trying to bind the text of the hint? That's not going to work. You could potentially use dynamic SQL to assemble the SQL statement before you execute it-- the specifics of that will depend on the language that the client you're running the SQL statement from is written in. If you're using SQL*Plus, you could probably use a substitution variable... Fundamentally, I'd strongly question the requirement here-- using hints like this is generally a bad idea, dynamic hints are worse. Oracle has plenty of other options to manipulate plans. — Justin Cave, May 26 '15 at 16:27
@JustinCave Thanks for the response, our DAL supports dynamique statements, with pro *C we can use exec sql prepare dyn_stmt for select ...., Problem is(for dynamique) it will be a risk of SQL injection. what do you think? — Aymanadou, May 27 '15 at 06:26
@Aymanadou - Yes, if you are accepting SQL from a client to dynamically generate a SQL statement, you'd run the risk of SQL injection. That's one of many reasons that the approach seems like a bad idea. Oracle has plenty of ways to force plan stability without using explicit hints, it would seem reasonable to use one of those alternatives. — Justin Cave, May 27 '15 at 06:52

score 1 · Answer 1 · edited Mar 13 '19 at 15:22

1

Your query must be executed using dynamic SQL. Something like this:

CREATE OR REPLACE PROCEDURE query_emp (a_hint VARCHAR2)
AS
    TYPE cur_typ IS REF CURSOR; c cur_typ;
BEGIN
    OPEN c FOR 'SELECT ' || a_hint || ' empno, ename, sal, job
                FROM emp WHERE empno = 7566';

    -- process
END;
/

edited Mar 13 '19 at 15:22

beaver

523
1
9
20

answered May 27 '15 at 01:56

Daniel Vukasovich

1,692
1
18
26

we are using dynamic statment with PRO *C EXEC SQL PREPARE statement_name FROM { :host_string | string_literal }; – Aymanadou May 27 '15 at 06:30

using Hint in a hosted variable?

1 Answers1