1 2 Previous Next 16 Replies Latest reply: Jan 4, 2013 11:12 AM by 668136 Go to original post RSS
      • 15. Re: Dynamic SQL execute immediate
        rp0428
        >
        We have one table where we keep the query that is going to be executed and the using bind variables.

        But im facing some problems when i try to use it in execute immediate.
        >
        No - the problems you are facing are because your function uses invalid syntax. The problems have NOTHING to do with 'execute immediate'. This works for me and no changes were made to the dynamic part.
        create or replace function fn_validate return varchar2 is
        varaux varchar2(200);
        begin
        for r1 in (select 'select ename from emp where empno = :1' query,
        7369 using_bind from dual) loop
        execute immediate r1.query into varaux using r1.using_bind;
        return varaux;
        end loop;
        return null;
        end; 
        
        declare
         myVar varchar2(200);
        begin
         myVar := fn_validate();
         dbms_output.put_line(myVar);
        end;
        
        SMITH
        • 16. Re: Dynamic SQL execute immediate
          668136
          rp0428 wrote:
          >
          We have one table where we keep the query that is going to be executed and the using bind variables.

          But im facing some problems when i try to use it in execute immediate.
          >
          No - the problems you are facing are because your function uses invalid syntax. The problems have NOTHING to do with 'execute immediate'. This works for me and no changes were made to the dynamic part.
          create or replace function fn_validate return varchar2 is
          varaux varchar2(200);
          begin
          for r1 in (select 'select ename from emp where empno = :1' query,
          7369 using_bind from dual) loop
          execute immediate r1.query into varaux using r1.using_bind;
          return varaux;
          end loop;
          return null;
          end; 
          
          declare
          myVar varchar2(200);
          begin
          myVar := fn_validate();
          dbms_output.put_line(myVar);
          end;
          
          SMITH
          Hi rp0428,

          Thank you very much for the help.

          As you can imagine I can not disclose the full code.

          It is very like my code :)

          More im going to close this thread.

          Regards,
          1 2 Previous Next