Prev: String Concatenation
Next: How to export a LONG field to Excel
From: The Magnet on 15 Jun 2010 17:30
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?
From: Mark D Powell on 16 Jun 2010 09:39
On Jun 15, 5:30 pm, The Magnet <a...(a)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 --

From: The Magnet on 16 Jun 2010 09:56
On Jun 16, 8:39 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote:
> On Jun 15, 5:30 pm, The Magnet <a...(a)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 clausehttp://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:210...
>
> -- Dynamic Dynamic SQLhttp://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:227...
>
> 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 --

I'll have to look at that example. I already have an object type and
CAST commands. But, here are the steps I use now. Maybe there is a
shorter or better way to do this:

1) Define cursor type to select from:
TYPE category_cursor IS REF CURSOR;
v_category_cursor category_cursor;

2) Define record type to fetch data into:
TYPE category_record_type IS RECORD (
article_id NUMBER,
subject VARCHAR2(1000),
teaser_message VARCHAR2(4000),
message CLOB,
category_id NUMBER,
category_name VARCHAR2(100),
publish_date VARCHAR2(20),
ex_publish_date VARCHAR2(20),
status_id NUMBER,
status_text VARCHAR2(100),
author_id NUMBER,
author_name VARCHAR2(50));
v_category_record category_record_type;

3) Create table type to store selected data in:
TYPE category_table IS TABLE OF category_record_type INDEX BY
BINARY_INTEGER;
v_category_table category_table;

4) Store data in object type previously defined:
v_article_data(v_sub) :=
article_record_type(v_category_table(x).article_id,

v_category_table(x).teaser_message,

v_category_table(x).subject,

v_category_table(x).message,
v_tag_data,
v_ticker_data,

v_category_table(x).publish_date,

v_category_table(x).ex_publish_date,

v_category_table(x).status_id,

v_category_table(x).status_text,

v_category_table(x).author_id,

v_category_table(x).author_name,

v_category_table(x).category_id,

v_category_table(x).category_name);

5) Fetch data from object: OPEN p_data FOR SELECT * FROM TABLE (CAST
(v_article_data AS article_table_type));

Note: There are steps between 3 & 4 which create data to store in
object. Notice the variables: v_tag_data, v_ticker_data

Is there an easier or more efficient way to do this?

Thanks!!
 | 
Pages: 1
Prev: String Concatenation
Next: How to export a LONG field to Excel