Re: Help Again, Something about Dynamic PL/SQL

lkw wrote:
>
> thanks for yr help....but i still hv some doubts ...
> .
> .
> >rept_flag_tab dbms_sql.Date_Table
> >rept_count_tab dbms_sql.Number_table
> .
> .
> what does this two declaretion means?

These two statements declare dbms_sql tables of date and number columns to be used in the procedure, similar to an array. Since my select statement returned more than one row, I needed a table to hold the returned values.

> >dbms_sql.define_array(c_sel_tmp_cnt, 1, rept_flag_tab,10, indx)

This defines the table variable to 'fetch' rows into.

> what is the '10' and 'indx' means? i know indx is initialize as 1 in front.

The '10' is the count or # of rows that will be returned into the named table variable each time dbms_sql.fetch is executed. Indx defines what value the table_variable subscript will start with. I.E. In my procedure, if the fetch loop returns a total of 17 values into rept_flag_tab, the first value will be in rept_flag_tab(1). The remainder in rept_flag_tab(2) thru rept_flag_tab(17). If I had initialized indx to be 10, then the first value would be in rept_flag_tab(10), the next in rept_flag_tab(11), etc..

I am still trying to get comfortable with dynamic sql myself. If it helps, most of the documentation I have found is in the Oracle Application Developer's Guide, Chapter 14.

Good Luck!

Joe Long Received on Mon Dec 20 1999 - 09:57:51 CST