Re: Dynamic Cursor

> 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: news_at_netfront.net ---