 | |
#11
 
| |||
| |||
|
Re: Dynamic Cursor -
06-18-2010
, 08:40 PM
|
On Jun 17, 5:44Â*pm, Tim X <t... (AT) nospam (DOT) dev.null> wrote: The Magnet <a... (AT) unsu (DOT) com> writes: On Jun 16, 6:35Â*pm, galen_bo... (AT) yahoo (DOT) com wrote: The Magnet <a... (AT) unsu (DOT) com> writes: On Jun 15, 9:16 pm, galen_bo... (AT) yahoo (DOT) com wrote: The Magnet <a... (AT) unsu (DOT) 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 (DOT) 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? Here's a sqlplus example: Â* Â* Â*SQL> create global temporary table t1(id number) on commit preserve rows Â* Â* Â* Â* Â* variable v_id number; Â* Â* Â* Â* Â* exec :v_id := 1; Â* Â* Â* Â* Â* insert into t1 values (:v_id); Â* Â* Â* Â* Â* exec :v_id := 2; Â* Â* Â* Â* Â* insert into t1 values (:v_id); Â* Â* Â* Â* Â* exec :v_id := 3; Â* Â* Â* Â* Â* insert into t1 values (:v_id); Â* Â* Â* Â* Â* SELECT * FROM (select id from t1); Â* Â* Â* Â* Â* Â* Â*ID Â* Â* Â*---------- Â* Â* Â* Â* Â* Â* Â* 1 Â* Â* Â* Â* Â* Â* Â* 2 Â* Â* Â* Â* Â* Â* Â* 3 The above would replace code that looks like the following, and probably has some analogy in your client code: set serveroutput on size 10000 DECLARE Â* Â* Â* Â* v_sql varchar2(1000); Â* Â* Â* Â* type typ_curs IS REF CURSOR; Â* Â* Â* Â* curs typ_curs; Â* Â* Â* Â* v_id number; BEGIN Â* Â* Â* Â* v_sql := 'SELECT id FROM t1 where id IN ('; Â* Â* Â* Â* FOR i in 1..3 LOOP Â* Â* Â* Â* Â* Â*v_sql := v_sql || i || ','; Â* Â* Â* Â* END LOOP; Â* Â* Â* Â* v_sql := v_sql || 'NULL)'; Â* Â* Â* Â* OPEN curs FOR v_sql; Â* Â* Â* Â* LOOP Â* Â* Â* Â* Â* Â* fetch curs into v_id; Â* Â* Â* Â* Â* Â* exit when curs%notfound; Â* Â* Â* Â* Â* Â* dbms_output.put_line(v_id); Â* Â* Â* Â* END LOOP; END; / 1 2 3 Make sense? -- Galen Boyer --- news://freenews.netfront.net/ - complaints: n... (AT) netfront (DOT) net --- Yes, it makes sense. Â*I may have to use that approach, though I always hated creating temporary tables and such. Â*I figured if I can do it in memory, then it is a better thing to do. Just to clarify a misconception that seems to be prevalent. Using normal oracle tables for temporary work is a bad thing and as a rule of thumb should be avoided. However, using an oracle temporary global table is designed precisely for this type of job. It as numerous advantages over 'normal' tables and some nice features that make using them very easy. Highly recommend reading up on temporary global tables. Tim -- tcross (at) rapttech dot com dot au That is what I always try and do. I hate temporary tables, and if I can create a collection, or an object of sorts, I'll do that instead. |
when they are not necessary. I've also been told that MS SQL Server
actually encourages/relies on temporary tables to some extent. We have
probably all inherited that maintenance nightmare schema at some point
or another that is just littered with old temporary tables or had to
deal with the performance hit from using a table for temp data and
wondering what all this weird behavior with redo segments and table
spaces was all about!
Like any feature, it can be abused. On theother hand, I've seen many
examples of people creating overly complex, inefficient and
unmaintainable solutions simply because they hate using temporary
tables. We also need to distinguish between the use of normal tables for
temporary data, which is usually the wrong solution, and the use of
Oracle's temporary global tables, which are not the same as standard
Oracle tables. I think in the last 8 years or so, I've used temporary
global tables less than 10 times. However, when I have used them, they
have greatly simplified the task while making it more reliable and
robust than any other solution I cold come up with.
Like all features, it is getting the balance right that is the difficult
part.
Tim
--
tcross (at) rapttech dot com dot au
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.