1 Reply Latest reply: Apr 26, 2005 11:43 AM by joelkallman-Oracle RSS

    Using the "V" function in dynamic queries

    VANJ
      I have a report region using 'pl/sql function body returning query'. It uses 'return package.function;'. The function makes extensive use of the "V" function to read session state.

      If I have a predicate like
      if (something) then
      q:=q||'
      and some_column=v(''P1_ID'')
      ';
      end if;

      When the query is parsed, this gets translated to

      ...
      and some_column=v('P1_ID')
      ...

      Question: Is the v('P1_ID') treated like a bind variable similar to the sys_context() concept? So it is evaluated just once per query and the value is plugged into the query. It is NOT evaluated for every row returned by the query, right?

      I am pretty sure thats the way it works, just wanted someone to confirm it.

      Thanks
        • 1. Re: Using the "V" function in dynamic queries
          joelkallman-Oracle
          Vikas,

          v() is a function, no differently than any other PL/SQL function you would employ in SQL. The way you have composed your query, it would be evaluated for every row.

          A simple example to show this:

          SQL> create or replace function vv (p_in varchar2) return varchar2
          2 is
          3 begin
          4 dbms_output.put_line('vv was called');
          5 return p_in;
          6 end;
          7 /

          Function created.

          SQL> set serveroutput on size 100000
          SQL> select count(*) from emp where ename = vv('KING')
          2 /

          COUNT(*)
          ----------
          1

          vv was called
          vv was called
          vv was called
          vv was called
          vv was called
          vv was called
          vv was called
          vv was called
          vv was called
          vv was called
          vv was called
          vv was called
          vv was called
          vv was called
          SQL>


          SQL> select count(*) from emp where ename = (select vv('KING') from dual)
          2 /

          COUNT(*)
          ----------
          1

          vv was called
          SQL>

          Note the last query, how you can specify this without causing it to be evaluated for every row.

          Joel