Dynamic Cursor

#1

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?

#2

The Magnet <art (AT) unsu (DOT) com> writes:

Quote:

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 (DOT) net ---

#3

On Jun 15, 9:16*pm, galen_bo... (AT) yahoo (DOT) com wrote:

Quote:

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?

Storing it in an object is a very useful thing. I'm not sure how to
run the SQL and access the results, since this is dynamic like this.

#4

On Jun 15, 5:30*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:

Quote:

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?...D:210612357425

-- Dynamic Dynamic SQL
http://asktom.oracle.com/pls/apex/f?...D: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 --

#5

On Jun 16, 8:39*am, Mark D Powell <Mark.Powe... (AT) hp (DOT) com> wrote:

Quote:

On Jun 15, 5:30*pm, The Magnet <a... (AT) unsu (DOT) 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!!

#6

The Magnet <art (AT) unsu (DOT) com> writes:

Quote:

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: news (AT) netfront (DOT) net ---

#7

On Jun 16, 6:35*pm, galen_bo... (AT) yahoo (DOT) com wrote:

Quote:

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.

#8

On Jun 17, 6:07*am, The Magnet <a... (AT) unsu (DOT) com> wrote:

Quote:

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.

That's the cool thing about Oracle. It does it in memory, then writes
it out when it feels like it, which may be before or after you
commit. It's optimistic about whether you might commit. So you get
the best of both worlds (memory and disk) without having to do
anything except not work against it (which includes configuring so it
doesn't feel more compelled to write to disk with your data volumes).
Randolph shows one way to investigate some details:
http://oracle-randolf.blogspot.com/2...ce-groups.html

I've seen a lot of stupid code coming from people who thought they
could do better than Oracle, forcing things to memory inappropriately,
necessitating way more I/O in the end.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...t-stolen-data/

#9

The Magnet <art (AT) unsu (DOT) com> writes:

Quote:

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

#10

On Jun 17, 5:44*pm, Tim X <t... (AT) nospam (DOT) dev.null> wrote:

Quote:

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.