Re: Dynamic Cursor

From: The Magnet <art_at_unsu.com>
Date: Fri, 18 Jun 2010 06:20:33 -0700 (PDT)
Message-ID: <c9b4945d-21c7-4b16-89fe-c75076ec74b0_at_11g2000prv.googlegroups.com>

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. Received on Fri Jun 18 2010 - 08:20:33 CDT