Bind Variables in Dynamic SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Bind Variables in Dynamic SQL

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Question

    i wud like to use bind variables in dynamic sql with an IN clause. for example,

    declare
    q varchar2(1000) := 'select count(*) from emp where sal in (:1)';
    n number;
    w varchar2(1000) := '1250,500,750';
    begin
    execute immediate q into n using w;
    exception when others then
    dbms_output.put_line(sqlerrm);
    end;
    /

    the above pl/sql block gives the foll. error:
    ORA-01722: invalid number

    can someone throw some lite on why this happens?


    Cheers!
    OraKid.

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712

  3. #3
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    try one bind variable for each parameter:

    declare
    q varchar2(1000) := 'select count(*) from emp where sal in (:a, :b, :c)';
    n number;
    a varchar2(10) := '1250';
    b varchar2(10) := '500';
    c varchar2(10) := '750';
    begin
    execute immediate q into n using a, b, c;
    end;


    F.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width