You Asked

Hello Tom,

When DBMS_SQL is used with collections in a loop, I discovered that the first index in each iteration is not necessarily 1. The following scenario demonstrates what I mean:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE locations (
locn_code VARCHAR2(03)
,locn_name VARCHAR2(100)
)
/

INSERT INTO locations VALUES ('PER','PERTH')
/
INSERT INTO locations VALUES ('FRE','FREMANTLE')
/
INSERT INTO locations VALUES ('BRO','BROOME')
/
INSERT INTO locations VALUES ('GER','GERALDTON')
/
INSERT INTO locations VALUES ('BUN','BUNBURY')
/
INSERT INTO locations VALUES ('DEN','DENMARK')
/
INSERT INTO locations VALUES ('ESP','ESPERANCE')
/

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE
C_BATCH_LIMIT CONSTANT INTEGER := 1;
g_sql VARCHAR2(200);
g_cursor_id PLS_INTEGER;
g_coll_locn_name DBMS_SQL.VARCHAR2_TABLE;
g_result PLS_INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('max fetch size='||C_BATCH_LIMIT);
g_sql := 'SELECT locn_name FROM locations';
g_cursor_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(g_cursor_id, g_sql, DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_ARRAY(g_cursor_id, 1, g_coll_locn_name, C_BATCH_LIMIT, 1);
g_result := DBMS_SQL.EXECUTE(g_cursor_id);

<<outer_loop>>
LOOP
g_coll_locn_name.DELETE;
g_result := DBMS_SQL.FETCH_ROWS(g_cursor_id);
DBMS_SQL.COLUMN_VALUE(g_cursor_id, 1, g_coll_locn_name);
EXIT outer_loop WHEN (g_result = 0);

<<for_loop>>
FOR i IN g_coll_locn_name.FIRST..g_coll_locn_name.LAST LOOP
DBMS_OUTPUT.PUT_LINE('i='||i||' locn_name='||g_coll_locn_name(i));
END LOOP for_loop;

EXIT outer_loop WHEN (g_result < C_BATCH_LIMIT);

END LOOP outer_loop;

DBMS_SQL.CLOSE_CURSOR(g_cursor_id);

END;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1. Why is g_coll_locn_name.FIRST not 1 every time as the array has been cleared?
2. If a few million records are being processed, does it matter that FIRST is not 1 every loop?
3. Can FIRST be forced to 1 every loop?

Alwyn

and Tom said...

that is just the way dbms_sql works. it'll put N rows at the "rowcount" offset.

if you "clear out" the array each time, it'll not consume any undo memory.

But the first 100 rows will go into 1..100 the next 100 rows into 101..200 and so on.

Just

DECLARE
C_BATCH_LIMIT CONSTANT INTEGER := 1;
g_sql VARCHAR2(200);
g_cursor_id PLS_INTEGER;
g_coll_locn_name DBMS_SQL.VARCHAR2_TABLE;
g_empty dbms_sql.varchar2_table;
g_result PLS_INTEGER;
BEGIN
....

-- at top of loop, before the fetch
g_coll_locn_name := g_empty;

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DBMS_SQL and array initialisation

Alwyn, March 15, 2004 - 7:45 pm UTC

Thanks for the simple explanation and clarifying that no extra memory is consumed. Is there any difference between using

g_coll_locn_name := g_empty
and
g_coll_locn_name.DELETE ?

Alwyn

March 15, 2004 - 8:15 pm UTC

they are "the same" for memory.

however, coll.delete is "faster" (guess i'm showing my 'age', .delete was "new" at one time -- it is very "old" now - .delete would be

a) faster
b) more appropriate

:)

How about initializing ?

A reader, May 02, 2007 - 10:37 am UTC

Tom, How do we initialize dbms_sql.varchar2_table type array? If I want to assign 10 values to it like a collection type how should I do it instead of giving each value on after the other

   Declare
     my_array     dbms_sql.varchar2_table:=dbms_sql.varchar2_table['a','b','c'..];  ?????

   begin
     ..
   end;

Thanks,

May 02, 2007 - 5:18 pm UTC

you cannot treat the plsql table type like a collection, they are not the same.

you have to use discrete assignments to the table, unfortunate legacy of the "index by binary_integer" table type.

Breadcrumb

Question and Answer