Re: Help: Dynamic where clause in a PL/SQL procedure

Muhammad Ahmad Malik wrote:
>
> I want to pass as a variable the "where clause" for a cursor in the
> procedure. There must be someway to handle this. Unfortunatly I don't
> have the manuals.
>
> Example:
>
> create or replace procedure dempno(where_clause varchar)
> as
> cursor c is select empno from emp where_clause;
> -- ^^^^^^^^^^^^^^
> -- How can I handle this part
>
> emp_rec emp.empno%type;
> begin
> dbms_output.enable;
> open c;
> loop
> fetch c into emp_rec;
> exit when c%notfound;
> dbms_output.put_line(emp_rec);
> end loop;
> close c;
> end;

  create or replace procedure dempno(where_clause in varchar2) as     emp_rec emp.empno%type;
    ignore integer;
    s_cur integer;
  begin
    s_cur := dbms_sql.open_cursor;
    dbms_sql.parse(s_cur, where_clause, dbms_sql.v7);     dbms_sql.define_column(s_cur, 1, empno);     ignore := dbms_sql.execute(s_cur);

    dbms_output.enable;
    loop

         if dbms_sql.fetch_rows(s_cur) > 0 then
            dbms_sql.column_value(s_cur, 1, emprec);
            dbms_output.put_line(emp_rec);

-- 
Name        : Lun Wing San
Title       : Oracle developer of the Hong Kong Productivity Council
              System Administrator and Oracle DBA of the Quick Response 
Center
Email Addr. : sunny_at_hkpc.org
Telephone   : 852-2788-5841