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'

"The Magnet" <art (AT) unsu (DOT) com> a écrit dans le message de news: 8d654aa9-0b35-4f6d-a4db-a913c2cfa35f...oglegroups.com...
The element of estimate_date_tab are not date value but estimate_date_type object.

Regards
Michel

On 06/23/2011 05:04 PM, The Magnet wrote:
Well, as it says: since it's a table of estimate_date_type you need to
pass in estimate_date_type instances and not DATEs. Maybe

v_dates_tab := estimate_date_tab(estimate_date_type(SYSDATE, SYSDATE,
SYSDATE, SYSDATE));

Just guessing since I don't have an Oracle handy right now.

Kind regards

robert

On Jun 23, 8:04*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
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

On Jun 23, 10:57*am, ddf <orat... (AT) msn (DOT) com> wrote:

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.....

On Jun 23, 9:04*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
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>

Giving you practice for declaring table types?



David Fitzjarrell

The Magnet <art (AT) unsu (DOT) com> writes:

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