Re: Help with TYPE syntax

> On Jun 23, 10:57 am, ddf <orat..._at_msn.com> wrote:
>> On Jun 23, 8:04 am, The Magnet <a..._at_unsu.com> wrote:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> > I'm sure this is a syntactical issue, but I'm still searching the web:
>>
>> > CREATE OR REPLACE TYPE estimate_date_type AS OBJECT (
>> >  qr1_end_date    DATE,
>> >  qr2_end_date    DATE,
>> >  fr1_end_date    DATE,
>> >  fr2_end_date    DATE);
>> > /
>> > CREATE OR REPLACE TYPE estimate_date_tab AS TABLE OF
>> > estimate_date_type;
>> > /
>>
>> > DECLARE
>> >   v_dates_tab         estimate_date_tab := estimate_date_tab();
>>
>> > BEGIN
>> >   v_dates_tab := estimate_date_tab(SYSDATE, SYSDATE, SYSDATE,
>> > SYSDATE);
>> > .
>> > .
>> > .
>> > PLS-00306: wrong number or types of arguments in call to
>> > 'ESTIMATE_DATE_TAB'
>>
>> SQL> CREATE OR REPLACE TYPE estimate_date_type AS OBJECT (
>>   2   qr1_end_date    DATE,
>>   3   qr2_end_date    DATE,
>>   4   fr1_end_date    DATE,
>>   5   fr2_end_date    DATE);
>>   6  /
>>
>> Type created.
>>
>> SQL> CREATE OR REPLACE TYPE estimate_date_tab AS TABLE OF
>>   2  estimate_date_type;
>>   3  /
>>
>> Type created.
>>
>> SQL>
>> SQL>
>> SQL> DECLARE
>>   2    v_dates_tab         estimate_date_tab := estimate_date_tab();
>>   3
>>   4
>>   5  BEGIN
>>   6    v_dates_tab := estimate_date_tab(estimate_date_type(SYSDATE,
>> SYSDATE, SYSDATE, SYSDATE));
>>   7  END;
>>   8  /
>>
>> PL/SQL procedure successfully completed.
>>
>> SQL>
>>
>> David Fitzjarrell
>
>
> Thanks guys. Maybe one can help with this. Here is the declaration:
>
> CREATE OR REPLACE TYPE estimate_date_type AS OBJECT (
> qr1_end_date DATE,
> qr2_end_date DATE,
> fr1_end_date DATE,
> fr2_end_date DATE);
> /
>
> So, WHY do I need this declaration?
>
> CREATE OR REPLACE TYPE estimate_date_tab AS TABLE OF
> estimate_date_type;
> /
>
> The column in the table is a nested column of 4 elements, I do not
> understand why I need to create a table of the object type. Would
> the column in the table not be of type estimate_date_type?
>
> Thinking in the terms of the PL/SQL code, each row in the table will
> contain one of these nested columns, which in itself contains 4
> elements. So, why create the estimate_date_tab? There will forever,
> at any time, only be one instance of this object being processed. So,
> only one instance of estimate_date_type, what is the purpose of
> estimate_date_tab?
>
> Maybe I am just not seeing how Oracle uses the constructs, etc.....
>
>

estimate_data_tab is your collection - if you need/want to collect instances of estimate_date_type you need to put them somewhere and you really only have three options

1. Declare multiple variables of estimate_data_type and instantiate each one
2. Created a PL/SQL collection
3. Define an SQL table with a nested column definition

If on the other hand you really only want to maintain a single instance, you could just use the type definition and skip the rest.

Tim

-- 
tcross (at) rapttech dot com dot au