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

From: Muhammad Ahmad Malik <mamalik_at_cs.wmich.edu>
Date: 1996/11/06
Message-ID: <Pine.SOL.3.91.961106121131.16758C-100000_at_vor>#1/1

Thanks a lot for your response. I had to make few changes in order to make your program run. Anyhow, It gave me a start.

Following is the modifies program segment:

create or replace procedure dempno(where_clause in varchar2) as

    emprec emp.empno%type;
    ignore number;
    s_cur number;
  begin
    s_cur := dbms_sql.open_cursor;
    dbms_sql.parse(s_cur, 'select empno from emp '||where_clause, dbms_sql.v7);

    dbms_sql.define_column(s_cur, 1, emprec);     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(emprec);
         else
            exit;
         end if;

    end loop;
    dbms_sql.close_cursor(s_cur);
  end;

Muhammad Ahmad Malik
Western Michigan University
mamalik_at_cs.wmich.edu - http://www.wmich.edu/frc/ahmad.html +1 (616) 373-3065

On Wed, 6 Nov 1996, Lun Wing San wrote:

> 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;
>
 

> A progrma segment

 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   Received on Wed Nov 06 1996 - 00:00:00 CET