Re: PL/SQL syntax, IS TABLE OF and .extend

From: Finn Ellebaek Nielsen <_remove_fen_remove__at_changegroup.dk>
Date: Thu, 24 Oct 2002 12:50:37 +0200
Message-ID: <3db7d076$0$97617$edfadb0f@dspool01.news.tele.dk>

Hi Volker.

You should not and cannot use EXTEND with a PL/SQL table (original name from Oracle7, called index-by table in Oracle8/8i, now called associative array in Oracle9i). You simply assign values to a given index in the array (not necessarily positive and it can be sparse, such as -10, 20, 50, etc). However, with nested tables and VARRAYs you have to use EXTEND.

So the body of your function could be:

  ret(-10) := -10;
  ret(10) := 10;
  ret(20) := 20;

  return ret;

HTH. Finn

"Volker Apelt" <gq437x_at_yahoo.de> wrote in message news:lgu1jccid2.fsf_at_mu.biosolveit.local...
>
> Do you know how to use IS TABLE OF and .EXTEND in PL/SQL
> functions?
>
> I can define a TYPE with a PL/SQL table, but I have not yet
> found the proper syntax to .EXTEND it in a procedure.
>
> I tried all three forms from the PL/SQL reference
> manual but all are rejected with this syntax error.
>
> PLS-00306: wrong number or types of arguments in call to 'EXTEND'
>
> How should I fix my code?
>
> Thank you,
>
> Volker
>
> --------------------- EXAMPLE CODE
>
> create or replace package with_syntax_error as
> TYPE sequence_id_list IS TABLE OF NUMBER index BY binary_integer;
> FUNCTION my_func( i_count IN POSITIVEN) RETURN
sequence_id_list;
> END with_syntax_error;
> /
> --
> --
> create or replace
> package BODY with_syntax_error as
>
> FUNCTION my_func( i_count IN POSITIVEN ) RETURN sequence_id_list
> as
> ret sequence_id_list ;
> begin
> /* case 1 */
> ret.extend(i_count);
> /* case 2 */
> ret.extend;
> /* case 3 */
> ret.extend(i_count,0);
> /* do something with ret -- not shown here */
> return ret;
> end;
>
> END with_syntax_error;
> /
> show errors
> ----------------------------- END EXAMPLE
> compiling them in sqlplus returns:
>
> Warning: Package Body created with compilation errors.
>
> SQL> Errors for PACKAGE BODY WITH_SYNTAX_ERROR:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 8/4 PLS-00306: wrong number or types of arguments in call to 'EXTEND'
> 8/4 PL/SQL: Statement ignored
> 10/4 PLS-00306: wrong number or types of arguments in call to 'EXTEND'
> 10/4 PL/SQL: Statement ignored
> 12/4 PLS-00306: wrong number or types of arguments in call to 'EXTEND'
> 12/4 PL/SQL: Statement ignored
> SQL>
>
> --
> Volker Apelt
>
>
Received on Thu Oct 24 2002 - 05:50:37 CDT