Re: Dynamic Cursor
From: The Magnet <art_at_unsu.com>
Date: Wed, 16 Jun 2010 06:01:40 -0700 (PDT)
Message-ID: <882f04c0-5160-44e6-84da-43115846b0a8_at_x21g2000yqa.googlegroups.com>
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 ---
Date: Wed, 16 Jun 2010 06:01:40 -0700 (PDT)
Message-ID: <882f04c0-5160-44e6-84da-43115846b0a8_at_x21g2000yqa.googlegroups.com>
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?
Storing it in an object is a very useful thing. I'm not sure how to run the SQL and access the results, since this is dynamic like this. Received on Wed Jun 16 2010 - 08:01:40 CDT