Re: Help with TYPE syntax

From: The Magnet <art_at_unsu.com>
Date: Thu, 23 Jun 2011 09:04:38 -0700 (PDT)
Message-ID: <33d34656-3444-4c6e-89c7-d1f294327059_at_h12g2000pro.googlegroups.com>

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..... Received on Thu Jun 23 2011 - 11:04:38 CDT