Re: PL/SQL: defining default value for table type

"Matthias Wirtz" <Matthias.Wirtz_at_gmx.net> wrote in news:YthCe.56237$iU.31253_at_lakeread05:

> Hi,
>
> supposingly I have the following package which defines one type:
>
> CREATE OR REPLACE PACKAGE p1 IS
> TYPE t_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
> END;
>
>
> If I then create another package which should use this table type in a
> procedure called 'result' I'm temted to write:
>
> CREATE OR REPLACE PACKAGE p2 IS
> PROCEDURE result(array p1.t_array DEFAULT p1.t_array);
> END;
>
>
> But his gives me the error:
>
> 2/3 PL/SQL: Declaration ignored
> 2/48 PLS-00330: invalid use of type name or subtype name
>
>
> On the net I found a solution that suggested to create the package
> 'p2' in the following way:
>
> CREATE OR REPLACE PACKAGE p2 IS
> empty_array p1.t_array;
> PROCEDURE result(array p1.t_array DEFAULT empty_array);
> END;
>
>
> And now I'm wondering how I possibly could solve the issue when I want
> to use the table type 't_array' in a procedure:
>
> CREATE OR REPLACE PROCEDURE proc1
> (
> array p1.t_array DEFAULT p1.t_array
> )
> AS
> BEGIN
> NULL;
> END;
>
> That gives me again the above error. I think I'm looking for a way to
> instantiate an empty object of type t_array to use for the DEAULT
> clause.
>

I get the distinct impression that you are a JAVA coder trying to work with Oracle DB.

I'm not clear on why you think you can or should use "DEFAULT" as you want to do.

http://downloadwest.
oracle.com/docs/cd/B10501_01/appdev.920/a96624/02_funds.htm#3524

Using DEFAULT

You can use the keyword DEFAULT instead of the assignment operator to initialize variables. For example, the declaration

blood_type CHAR := 'O';

can be rewritten as follows:

blood_type CHAR DEFAULT 'O';

Use DEFAULT for variables that have a typical value. Use the assignment operator for variables (such as counters and accumulators) that have no typical value. A couple of examples follow:

hours_worked INTEGER DEFAULT 40;
employee_count INTEGER := 0;

You can also use DEFAULT to initialize subprogram parameters, cursor parameters, and fields in a user-defined record. Received on Sat Jul 16 2005 - 19:59:40 CDT