Re: Dynamic Cursor

From: The Magnet <art_at_unsu.com>
Date: Thu, 17 Jun 2010 06:07:21 -0700 (PDT)
Message-ID: <1da0cde3-0456-48e3-9e5d-4a877e1025a6_at_z31g2000vbk.googlegroups.com>

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. Received on Thu Jun 17 2010 - 08:07:21 CDT