Re: pl/sql table and commit

On 15 Nov 2005 12:56:10 -0800, "Guy" <guhar1_at_yahoo.com> wrote:

>When I insert into a pl/sql table, do I need to commit ?
>
>I have a package body which defines a pl/sql table to contains a DATE
>table type;
>
> TYPE DateTable IS TABLE OF DATE
> INDEX BY BINARY_INTEGER;
>
>I have procedure P_PROC(), which
>
> - declares a pl/sql table:
> v_DateTable PACKAGE_1.DateTable;
>
> - and calls a package procedure which will fill the v_DateTable (an
>IN OUT )
> PACKAGE_1.MyProc(arg1, arg2,..., v_DateTable)
>
>The MyProc() is very simple: opens a cursor which performs a SELECT and
>return rows of one date column:
> n := 1;
> LOOP
> FETCH C1 INTO v_DateTable(n);
> EXIT WHEN C1%NOTFOUND;
> n := n + 1;
> END LOOP;
>
>By accident I found if I do the following, my legacy application works:
> begin
> n := 1;
> LOOP
> FETCH C1 INTO v_DateTable(n);
> EXIT WHEN C1%NOTFOUND;
> n := n + 1;
> END LOOP
> commit;
> end;
>
>Do we need to commit the v_DateTable to be valid?
>Is there a visibiliby problem for a v_DateTable declared in a proc and
>filled in a proc of a package?
>
>Thanks,

1 No, you don't need to commit
2 If you declare the table in a standalone procedure there is. If you declare the table in a package spec, the table is visible anywhere provided it is prefixed by the package name. If you declare the table globally in the package body and both procedures in the package, there also isn't a problem.

--
Sybrand Bakker, Senior Oracle DBA