PLS-00306: wrong number of type of arguments

It appears you are missing a parameter for:

CREATE OR REPLACE TYPE APPS.xxoc_shipping AS OBJECT (shipcost NUMBER, shipmethod VARCHAR2(400));

From xxoc_shiprec(cc.shipping) as the error "wrong number of type of arguments" suggests.

So is something missing from these TABLE and OBJECT defines:

 

CREATE OR REPLACE TYPE APPS.xxoc_shipping AS OBJECT (shipcost NUMBER, shipmethod VARCHAR2(400));
CREATE OR REPLACE TYPE APPS.xxoc_allrecords AS OBJECT (inventory_item_id NUMBER, shipping xxoc_shipping);
CREATE OR REPLACE TYPE APPS.XXOC_SHIPREC as table of XXOC_SHIPPING;
CREATE OR REPLACE TYPE APPS.XXOC_ALLREC as table of XXOC_ALLRECORDS;

Select all Open in new window

??

No, look at the OBJECT defines it lists two values: shipping code and ship method. I am not sure which cc.shipping represents, but the other is missing from xxoc_shiprec(cc.shipping), right?

SHIPPING is defined in xxoc_allrecords and is the Nested Table name ?

 
which is made up of two "subcolumns" : SHIPCOST AND SHIPMETHOD

Can you accomplish the same thing another way?

No, I was not suggesting I knew another way. Maybe I am not being clear. You asked why are you getting "wrong number of type of arguments" error. My answer is that xxoc_shiprec(cc.shipping) clearly only has one argument. If you are saying cc.shipping is already of object xxoc_shipping, then why is it wrapped?

Try like this: v(v.COUNT) := xxoc_allrec(cc.inventory_item_id, cc.shipping);

PROCEDURE sp_getmodel(
feedname              IN         VARCHAR2 DEFAULT NULL,
feedtype              IN         VARCHAR2 DEFAULT NULL,
RCT1                  IN  OUT    SYS_REFCURSOR
)
IS
/**
CREATE OR REPLACE TYPE APPS.xxoc_shipping AS OBJECT (shipcost NUMBER, shipmethod VARCHAR2(400));
CREATE OR REPLACE TYPE APPS.xxoc_allrecords AS OBJECT (inventory_item_id NUMBER, shipping xxoc_shipping);
CREATE OR REPLACE TYPE APPS.XXOC_SHIPREC as table of XXOC_SHIPPING;
CREATE OR REPLACE TYPE APPS.XXOC_ALLREC as table of XXOC_ALLRECORDS;
**/
-- define and initialize the parent and child table --
v XXOC_ALLREC := XXOC_ALLREC();

BEGIN
    mo_global.init('ONT');
    DBMS_APPLICATION_INFO.SET_MODULE(module_name=>'APPS.xxoc_conmodel_pkg', action_name => 'sp_getmodel');
    /***** this works return DATASET  *********
    FOR cc IN (select cr.cross_reference, v.description from mtl_cross_references cr join (select description, tag from fnd_lookup_values where lookup_type = 'OC_CHANNEL_SHIPPING_MAPPING' and lookup_code like 'EBAY%') v on UPPER(cr.description) = UPPER(v.tag) where cr.cross_reference_type = 'eBay Shipping' and inventory_item_id = 600462) LOOP
        w.EXTEND;
        w(w.COUNT) := xxoc_shipping(cc.cross_reference, cc.description);
    END LOOP;
    OPEN RCT1 FOR SELECT * FROM TABLE(w);
    ***********************************/
    FOR cc IN (select cr.inventory_item_id,  CAST(COLLECT(xxoc_shipping(cr.cross_reference, v.description)) as XXOC_SHIPREC) as shipping 
                        from mtl_cross_references cr join (select description, tag from fnd_lookup_values where lookup_type = 'OC_CHANNEL_SHIPPING_MAPPING' and lookup_code like 'EBAY%') v on UPPER(cr.description) = UPPER(v.tag) 
                        where cr.cross_reference_type = 'eBay Shipping' and inventory_item_id = 600462 group by cr.inventory_item_id) LOOP
        v.EXTEND;
        v(v.COUNT) := xxoc_allrec(cc.inventory_item_id, cc.shipping);
    END LOOP;
    OPEN RCT1 FOR SELECT * FROM TABLE(v);
    DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
END;

Select all Open in new window

Yes, thanks for the clarification. You are correct.

I made that change and now I'm getting the error :

PLS-00306: wring number or types of arguments in call to 'XXOC_ALLREC'

Same line?

yes - same line:

v(v.COUNT) := xxoc_allrec(cc.inventory_item_id, cc.shipping);

PL/SQL is not my strong suit, but this is what appears to be error to me:
v(v.COUNT) := xxoc_allrec(cc.inventory_item_id, cc.shipping);

Should be:
v(v.COUNT) := xxoc_allrecords(cc.inventory_item_id, cc.shipping);

PROCEDURE sp_getmodel(
feedname              IN         VARCHAR2 DEFAULT NULL,
feedtype              IN         VARCHAR2 DEFAULT NULL,
RCT1                  IN  OUT    SYS_REFCURSOR
)
IS
/**
CREATE OR REPLACE TYPE APPS.xxoc_shipping AS OBJECT (shipcost NUMBER, shipmethod VARCHAR2(400));
CREATE OR REPLACE TYPE APPS.xxoc_allrecords AS OBJECT (inventory_item_id NUMBER, shipping xxoc_shipping);
CREATE OR REPLACE TYPE APPS.XXOC_SHIPREC as table of XXOC_SHIPPING;
CREATE OR REPLACE TYPE APPS.XXOC_ALLREC as table of XXOC_ALLRECORDS;
**/
-- define and initialize the parent and child table --
v XXOC_ALLREC := XXOC_ALLREC();

BEGIN
    mo_global.init('ONT');
    DBMS_APPLICATION_INFO.SET_MODULE(module_name=>'APPS.xxoc_conmodel_pkg', action_name => 'sp_getmodel');
    /***** this works return DATASET  *********
    FOR cc IN (select cr.cross_reference, v.description from mtl_cross_references cr join (select description, tag from fnd_lookup_values where lookup_type = 'OC_CHANNEL_SHIPPING_MAPPING' and lookup_code like 'EBAY%') v on UPPER(cr.description) = UPPER(v.tag) where cr.cross_reference_type = 'eBay Shipping' and inventory_item_id = 600462) LOOP
        w.EXTEND;
        w(w.COUNT) := xxoc_shipping(cc.cross_reference, cc.description);
    END LOOP;
    OPEN RCT1 FOR SELECT * FROM TABLE(w);
    ***********************************/
    FOR cc IN (select cr.inventory_item_id,  CAST(COLLECT(xxoc_shipping(cr.cross_reference, v.description)) as XXOC_SHIPREC) as shipping 
                        from mtl_cross_references cr join (select description, tag from fnd_lookup_values where lookup_type = 'OC_CHANNEL_SHIPPING_MAPPING' and lookup_code like 'EBAY%') v on UPPER(cr.description) = UPPER(v.tag) 
                        where cr.cross_reference_type = 'eBay Shipping' and inventory_item_id = 600462 group by cr.inventory_item_id) LOOP
        v.EXTEND;
        v(v.COUNT) := xxoc_allrecords(cc.inventory_item_id, cc.shipping);
    END LOOP;
    OPEN RCT1 FOR SELECT * FROM TABLE(v);
    DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
END;

Select all Open in new window

Still getting the same basic error:

PLS-00306: wrong number or types of arguments in call to 'XXOC_ALLRECORDS'

I think it's close - there is just something I'm misunderstanding...

sorry, was out most of the today, just got Kevin's note.

the problem is in the area's already identified.

the problem is mixing collections with individual records further complicated by mixing the variables with constructors for the types
just to make sure we're all on the same page (since I'm coming in late) I'll do some summarizing (and hence, repeating) of what I think has been posted above.

in the original question code...

 this seems correct
the columns are combined with the shipping costructor which is then collected into an XXOC_SHIPREC table.
 calling a table a "rec" seems counter intuitive, it's more common to name collections "tab", "table", "array" or something similar that indicates the object holds multiples of some other thing.
 whereas records typically represent a single object
 however that's just naming, the syntax itself is fine that I can see
CAST(COLLECT(xxoc_shipping(cr.cross_reference, v.description)) AS xxoc_shiprec)  

this is NOT correct
xxoc_allrec is a table of XXOC_ALLRECORDS,  and XXOC_ALLRECORDS is itself an object holding one number and one xxoc_shipping object
so, this line attempts to create a collection,  but I think it needs to be xxoc_allrecords instead of xxoc_allrec,  and xxoc_shiprec is wrong
because it should be xxoc_shipping

  v(v.COUNT) := xxoc_allrec(cc.inventory_item_id, xxoc_shiprec(cc.shipping));

I think the underlying problem is the record and table types have their names backwards.  

XXOC_ALLRECORDS should be a table of xxoc_allrec  or better yet, pick names that don't both have "rec" in them,
that way the types will be more self-documenting


In later code from Kevin the COLLECT and creation of the tables has been removed,  given the group by I don't believe that's correct.
For one, the syntax can't work, so,  Either that query needs to do the collect but casting to the proper tables instead of record types,  or the group by needs to be removed.

I can't say what is correct, I can only say for sure that what is currently there can't be right.

also the  variable "w" is used, but I don't see any declaration for it, so the assignment of w(w.count) :=  may or may not be correct, we have no way telling from this snippet

Thanks, Sean! I did not catch the group by. I should have made my point about the type more clear. The cast as you pointed out is what I am concerned with.

w(w.count) :=   <<<  is commented out section (notes only)

So, given the issues you pointed out, how would your construct the TYPE, SQL and assignment into v(v.COUNT) using proper naming conventions so I can learn from this and reuse on future projects?

I'm not sure how to answer that.

Kevin removed the COLLECT, was that a correct thing to do?

that is,  for each cr.inventory_item_id,  should there be more than one  
   xxoc_shipping(cr.cross_reference, v.description)  record?  If so, then you probably need to keep the COLLECT and return the nested table.

Yes, there will be multiple shipping recs (cost and method) for each inventory_item_id.

If that is the case, then your allrecords object definition should include a table of shipping records, right?

Yes - a nested table of sorts as defined by xxoc_shipping

The reason I removed the COLLECT() is by your definition, you only have a singular shipping record defined as belong to all records.

Original:
CREATE OR REPLACE TYPE APPS.xxoc_shipping AS OBJECT (shipcost NUMBER, shipmethod VARCHAR2(400));
CREATE OR REPLACE TYPE APPS.xxoc_allrecords AS OBJECT (inventory_item_id NUMBER, shipping xxoc_shipping);
CREATE OR REPLACE TYPE APPS.XXOC_SHIPREC as table of XXOC_SHIPPING;
CREATE OR REPLACE TYPE APPS.XXOC_ALLREC as table of XXOC_ALLRECORDS;

Because of this, COLLECT() with CAST(... AS XXOC_SHIPREC) seems wrong. Given the new information, though, I seems like the definition is what is wrong.

Maybe look at something like:
CREATE OR REPLACE TYPE APPS.XXOC_SHIP_REC AS OBJECT (shipcost NUMBER, shipmethod VARCHAR2(400));
CREATE OR REPLACE TYPE APPS.XXOC_SHIP_TBL AS TABLE OF XXOC_SHIP_REC;
CREATE OR REPLACE TYPE APPS.XXOC_INV_REC AS OBJECT (inventory_item_id NUMBER, shipping XXOC_SHIP_TBL);
CREATE OR REPLACE TYPE APPS.XXOC_INV_TBL  AS TABLE OF XXOC_INV_REC;

Then your code may look like this:
v XXOC_INV_TBL := XXOC_INV_TBL();
...
FOR cc IN (select cr.inventory_item_id,  CAST(COLLECT(XXOC_SHIP_REC(cr.cross_reference, v.description)) AS XXOC_SHIP_TBL) as shipping
                        from mtl_cross_references cr join (select description, tag from fnd_lookup_values where lookup_type = 'OC_CHANNEL_SHIPPING_MAPPING' and lookup_code like 'EBAY%') v on UPPER(cr.description) = UPPER(v.tag)
                        where cr.cross_reference_type = 'eBay Shipping' and inventory_item_id = 600462 group by cr.inventory_item_id) LOOP
        v.EXTEND;
        v(v.COUNT) := XXOC_INV_REC(cc.inventory_item_id, cc.shipping);
    END LOOP;

Kevin

Testing samples now...  be right back.

Tried the first code from sdstuber (09/27/11 10:04 AM, ID: 36711281) - it compiled and looks good but we were not able to display the output in Toad which might be a Toad thing. It was telling us: "Dataset not supported"  - will pass code onto the team trying to use it and see if it works for them in their SOA environment.

Will try the other now...

Runs great in SQL*PLUS - thanks for the lesson!