Re: Dynamic Cursor

From: The Magnet <art_at_unsu.com>
Date: Wed, 16 Jun 2010 06:56:52 -0700 (PDT)
Message-ID: <336c6980-beb9-4e8e-858b-e86735b2b027_at_z25g2000vbk.googlegroups.com>

On Jun 16, 8:39 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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 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!! Received on Wed Jun 16 2010 - 08:56:52 CDT