This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jan 4, 2013 9:12 AM by 668136 Go to original post RSS
  • 15. Re: Dynamic SQL execute immediate
    rp0428 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points