Re: Dynamic Cursor

From: Tim X <timx_at_nospam.dev.null>
Date: Fri, 18 Jun 2010 08:44:26 +1000
Message-ID: <87sk4l1dpx.fsf_at_rapttech.com.au>

The Magnet <art_at_unsu.com> writes:

> On Jun 16, 6:35 pm, galen_bo..._at_yahoo.com wrote:

>> The Magnet <a..._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: n..._at_netfront.net ---

>
>
> Yes, it makes sense.  I may have to use that approach, though I always
> hated creating temporary tables and such.  I figured if I can do it in
> memory, then it is a better thing to do.
>

Just to clarify a misconception that seems to be prevalent.

Using normal oracle tables for temporary work is a bad thing and as a rule of thumb should be avoided. However, using an oracle temporary global table is designed precisely for this type of job. It as numerous advantages over 'normal' tables and some nice features that make using them very easy. Highly recommend reading up on temporary global tables.

Tim

-- 
tcross (at) rapttech dot com dot au

Received on Thu Jun 17 2010 - 17:44:26 CDT