Re: Help with TYPE syntax

From: ddf <oratune_at_msn.com>
Date: Thu, 23 Jun 2011 16:36:30 -0700 (PDT)
Message-ID: <22c54592-9574-4ce0-96be-06f1a344de06_at_m22g2000yqh.googlegroups.com>

On Jun 23, 9:04 am, The Magnet <a..._at_unsu.com> wrote:
> 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;
> /

You apparently don't:

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>
SQL> DECLARE
  2 v_dates_tab estimate_date_type;   3
  4
  5 BEGIN
  6 v_dates_tab := estimate_date_type(SYSDATE, SYSDATE, SYSDATE, SYSDATE);
  7 END;
  8 /

PL/SQL procedure successfully completed.

SQL>
>
> 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?
>

Giving you practice for declaring table types? :)

> Maybe I am just not seeing how Oracle uses the constructs, etc.....- Hide quoted text -
>
> - Show quoted text -

David Fitzjarrell Received on Thu Jun 23 2011 - 18:36:30 CDT