Friday, March 20, 2015

How to use Parameter in where clause of oracle cursor

An example of Dynamic where clause in Oracle Cursor

declare
    v_deptnos  varchar2(100) ;
    v_rc       sys_refcursor;
    v_dept_name varchar2(200);
     str_sql varchar2(200);
    begin
       v_deptnos := '10,20';
       str_sql:=' or dept_name=''Accounting'';
       open l_rc for 'select dept_name from dept where deptno in (' || v_deptnos || ')'||str_sql;
     loop
       fetch v_rc into v_dept_name;
       exit when v_rc%notfound;
       dbms_output.put_line(v_dept_name);
    end loop;
      close v_rc;
  end;

No comments:

Post a Comment