I'm getting the following error message: PLS-00306: wrong number or types of arguments in call to: 'XXOC_SHIPREC'
Our code is as follows:
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,xxoc_shiprec(cc.shipping)); END LOOP; OPEN RCT1 FOR SELECT * FROM TABLE(v); DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);END;
The OBJECT and TABLE defines used are listed as comments. I'm sure we're close on this - just relatively new to this topic so I'm not seeing the problem.
When I execute the SELECT statement by itself - it runs fine.
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.
0
globalwm2Author Commented:
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;
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?
0
An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
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;
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;
It is not obvious to me either after that last change, beyond it being some CAST() issue where you have to explicitly CAST() the values to appropriate types OR as you did with CAST(COLLECT(xxoc_shipping(cr.cross_reference, v.description)) as XXOC_SHIPREC) using COLLECT()...Eureka!? You are casting to XXOC_SHIPREC when xxoc_allrecords is expecting (inventory_item_id NUMBER, shipping xxoc_shipping). I wonder if you change that to:
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, xxoc_shipping(cr.cross_reference, v.description) 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;
Or make proper adjustments elsewhere if that will fix it. Since that is the extent of my helpfulness, I have asked sdstuber and slightwv to stop by if they are free. Therefore, if my above suggestion isn't the cause, hopefully one of them will chime in shortly.
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
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.
0
globalwm2Author Commented:
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?
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.
0
globalwm2Author Commented:
Yes, there will be multiple shipping recs (cost and method) for each inventory_item_id.
CREATE OR REPLACE TYPE xxoc_shipping AS OBJECT( shipcost NUMBER, shipmethod VARCHAR2(400));CREATE OR REPLACE TYPE xxoc_ship_tab AS TABLE OF xxoc_shipping;CREATE OR REPLACE TYPE xxoc_allrecords AS OBJECT( inventory_item_id NUMBER, shipping xxoc_ship_tab);CREATE OR REPLACE TYPE xxoc_all_tab AS TABLE OF xxoc_allrecords;PROCEDURE sp_getmodel(feedname IN VARCHAR2 DEFAULT NULL, feedtype IN VARCHAR2 DEFAULT NULL, rct1 IN OUT SYS_REFCURSOR )IS -- define and initialize the parent and child table -- v xxoc_all_tab := xxoc_all_tab();BEGIN mo_global.init('ONT'); DBMS_APPLICATION_INFO.set_module(module_name => 'APPS.xxoc_conmodel_pkg', action_name => 'sp_getmodel' ); FOR cc IN (SELECT cr.inventory_item_id, CAST(COLLECT(xxoc_shipping(cr.cross_reference, v.description)) AS xxoc_shiptab) 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;
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;
actually, you can remove all the pl/sql looping too, you can simply collect into the final output...
note, your "v" table will only have one record due to your current grouping.
CREATE OR REPLACE PROCEDURE sp_getmodel(feedname IN VARCHAR2 DEFAULT NULL, feedtype IN VARCHAR2 DEFAULT NULL, rct1 IN OUT SYS_REFCURSOR )ISBEGIN mo_global.init('ONT'); DBMS_APPLICATION_INFO.set_module(module_name => 'APPS.xxoc_conmodel_pkg', action_name => 'sp_getmodel' ); OPEN rct1 FOR SELECT inventory_item_id, CAST(COLLECT(xxoc_allrecords(inventory_item_id, shipping)) AS xxoc_all_tab) allrec FROM (SELECT cr.inventory_item_id, CAST( COLLECT(xxoc_shipping(cr.cross_reference, v.description)) AS xxoc_ship_tab) 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); DBMS_APPLICATION_INFO.set_module(NULL, NULL);END;
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...
0
globalwm2Author Commented:
Runs great in SQL*PLUS - thanks for the lesson!
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
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.