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

From: Steve Dodsworth <Steven_Dodsworth_at_qsp.co.uk>
Date: 1996/11/05
Message-ID: <55n20f$5or@orion.qsp.co.uk>#1/1

In <55mj8m$rim_at_camel4.mindspring.com>, efried_at_mindspring.com (Eric Friedman) writes:
>In article <327E3FE2.185D_at_cs.wmich.edu>, mamalik_at_cs.wmich.edu 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
>>
>You need to use dynamic pl/sql. It's available in a package from
>Oracle - I think it first shipped with 7.1
>
>The details are a bit gory - if you don't have the manuals, pick up
>a book on pl/sql or check the Oracle web site.
>
>Eric
>
>-------------------------------------------------------
>Eric Friedman Internet: efried_at_mindspring.com

Here's an example:

  declare

       cursor_id integer;
  begin

       /* assign cursor identifier
       */
       cursor_id := dbms_sql.open_cursor;

       /* create sequence statement
       */
       dbms_sql.parse(cursor_id, 'create sequence '||seq_name
                                     || ' minvalue 1'
                                     || ' cycle'
                                     || ' order',       dbms_sql.v7);

       dbms_sql.close_cursor(cursor_id);

  end;

You can pass parameters thru and use them in the parse statement. Statements that have selects in them are a bit more complicated, let mem know if you want an example of them also.

Bye,
Steve

---

| any similarity 'tween my opinions and that |
|  of my employers are purely hypothetical   |
|     and should give no cause for alarm     |
 --------------------------------------------

Received on Tue Nov 05 1996 - 00:00:00 CST