Re: Dynamic Cursor

From: <galen_boyer_at_yahoo.com>
Date: Wed, 16 Jun 2010 19:35:26 -0400
Message-ID: <14qmxuu1rfa.fsf_at_yahoo.com>

The Magnet <art_at_unsu.com> writes:

> On Jun 15, 9:16 pm, galen_bo..._at_yahoo.com wrote:

>> The Magnet <a..._at_unsu.com> writes:
>> > We have a dynamic cursor with a dynamic IN clause:
>>
>> >      SELECT article_id, subject, teaser_message, message, category_id,
>> > category_name,
>> >             publish_date, ex_publish_date, status_id, status_text,
>> > author_id, author_name
>> >      FROM (SELECT article_id, subject, teaser_message, message,
>> >                   TO_CHAR(publish_date,''MM/DD/YYYY HH24:MI:SS'')
>> > publish_date,
>> >                   TO_CHAR(ex_publish_date,''MM/DD/YYYY HH24:MI:SS'')
>> > ex_publish_date,
>> >                   s.status_id, status_text, author_id, author_name,
>> > category_id, category_name
>> >            FROM articles a, ststus s
>> >            WHERE category_id ' || v_in_clause || '
>> >              AND a.status_id = s.status_id
>> >            ORDER BY publish_date DESC)
>> >      WHERE rownum <= ' || p_return_count;
>>
>> > Then I have this:
>>
>> > FOR v_rec IN v_select LOOP
>>
>> > 408/16   PLS-00456: item 'V_SELECT' is not a cursor
>>
>> > What's seems to be the issue here?
>>
>> First, why do you feel you need to have a dynamic query?  Create a
>> object_type, fill it up and then select from it, or, create a global
>> temp table, insert into it then "category_id IN (select xxx from
>> global)"
>>
>> Second, the global temp solution won't cause constant parsing because
>> your v_in_clause is different per list of attributes in the "in
>> clause".  Bad news.  You did the same thing with the p_return_count.
>>
>> Just create a global temp and use it, then, create static sql.
>>
>> --
>> Galen Boyer
>>
>> --- news://freenews.netfront.net/ - complaints: n..._at_netfront.net ---

>
>
> I use the object type elsewhere in our application, but there is is
> either static SQL or simple parameter replacement in a cursor.  Here,
> the IN clause is dynamic, so, the SQL has to be dynamic, correct?

Here's a sqlplus example:

     SQL> create global temporary table t1(id number) on commit preserve rows
          variable v_id number;
          exec :v_id := 1;
          insert into t1 values (:v_id);
          exec :v_id := 2;
          insert into t1 values (:v_id);
          exec :v_id := 3;
          insert into t1 values (:v_id);
          SELECT * FROM (select id from t1);


             ID
     ----------
              1
              2
              3

The above would replace code that looks like the following, and probably has some analogy in your client code:

set serveroutput on size 10000
DECLARE

        v_sql varchar2(1000);
        type typ_curs IS REF CURSOR;
        curs typ_curs;
        v_id number;
BEGIN
        v_sql := 'SELECT id FROM t1 where id IN (';
        FOR i in 1..3 LOOP
           v_sql := v_sql || i || ',';
        END LOOP;
        v_sql := v_sql || 'NULL)';
        OPEN curs FOR v_sql;
        LOOP
            fetch curs into v_id;
            exit when curs%notfound;
            dbms_output.put_line(v_id);
        END LOOP;

END;
/

1
2
3

Make sense?

-- 
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news_at_netfront.net ---

Received on Wed Jun 16 2010 - 18:35:26 CDT