Re: Dynamic Cursor
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 16 Jun 2010 06:39:07 -0700 (PDT)
Message-ID: <6ecaa38c-1ca8-470c-9d9b-67fdc8bb9d74_at_c33g2000yqm.googlegroups.com>
On Jun 15, 5:30 pm, The Magnet <a..._at_unsu.com> wrote:
> 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?
Date: Wed, 16 Jun 2010 06:39:07 -0700 (PDT)
Message-ID: <6ecaa38c-1ca8-470c-9d9b-67fdc8bb9d74_at_c33g2000yqm.googlegroups.com>
On Jun 15, 5:30 pm, The Magnet <a..._at_unsu.com> wrote:
> 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?
You may want to check out the following thread on this common coding mistake:
- Dynamic In clause
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:210612357425
- Dynamic Dynamic SQL http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:227413938857
Generally speaking you should avoid dynamic SQL anytime a static SQL statement using bind variable can be substituted.
In your case actually coding a select in the IN list should probably be your first consideration.
HTH -- Mark D Powell -- Received on Wed Jun 16 2010 - 08:39:07 CDT