Re: Dynamic Cursor
Date: Sat, 19 Jun 2010 10:40:58 +1000
Message-ID: <878w6b26sl.fsf_at_rapttech.com.au>
The Magnet <art_at_unsu.com> writes:
> On Jun 17, 5:44 pm, Tim X <t..._at_nospam.dev.null> wrote:
>> The Magnet <a..._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
> > > That is what I always try and do. I hate temporary tables, and if I > can create a collection, or an object of sorts, I'll do that instead. >
I would agree that less experienced uses do tend to use temporary tables when they are not necessary. I've also been told that MS SQL Server actually encourages/relies on temporary tables to some extent. We have probably all inherited that maintenance nightmare schema at some point or another that is just littered with old temporary tables or had to deal with the performance hit from using a table for temp data and wondering what all this weird behavior with redo segments and table spaces was all about!
Like any feature, it can be abused. On theother hand, I've seen many examples of people creating overly complex, inefficient and unmaintainable solutions simply because they hate using temporary tables. We also need to distinguish between the use of normal tables for temporary data, which is usually the wrong solution, and the use of Oracle's temporary global tables, which are not the same as standard Oracle tables. I think in the last 8 years or so, I've used temporary global tables less than 10 times. However, when I have used them, they have greatly simplified the task while making it more reliable and robust than any other solution I cold come up with.
Like all features, it is getting the balance right that is the difficult part.
Tim
-- tcross (at) rapttech dot com dot auReceived on Fri Jun 18 2010 - 19:40:58 CDT