Home » SQL & PL/SQL » SQL & PL/SQL » problem with SET-Operator on COLLECTIONS (ORACLE 10.2.0.1 WINDOWS 64bit)
problem with SET-Operator on COLLECTIONS [message #433655] Thu, 03 December 2009 09:07 Go to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
Why ist it possible to use the SET-Operator on COLLECTION fruitBoxV, but not on COLLECTION fruitBoxF ?
SET SERVEROUTPUT ON SIZE 100000;

--build up types
CREATE OR REPLACE TYPE fruitT  AS OBJECT (fruit VARCHAR2(100));
/
SHOW ERRORS;

CREATE OR REPLACE TYPE fruitsV AS TABLE OF VARCHAR2(100);
/
SHOW ERRORS;

CREATE OR REPLACE TYPE fruitsT AS TABLE OF fruitT;
/
SHOW ERRORS;

--test it
DECLARE
    fruitBoxV fruitsV:= fruitsV(       'Apple',        'Apple',        'Orange',        'Banana');
    fruitBoxF fruitsT:= fruitsT(fruitT('Apple'),fruitT('Apple'),fruitT('Orange'),fruitT('Banana'));
BEGIN
    DBMS_OUTPUT.PUT_LINE(    fruitBoxV.count);
    DBMS_OUTPUT.PUT_LINE(SET(fruitBoxV).count);
    DBMS_OUTPUT.PUT_LINE(    fruitBoxF.count);
    --ORA-06550 PLS-00306 ERROR ?
    --DBMS_OUTPUT.PUT_LINE(SET(fruitBoxF).count);
END;
/

--clean up
DROP TYPE              fruitsT FORCE;

DROP TYPE              fruitsV FORCE;

DROP TYPE              fruitT  FORCE;

icon5.gif  Re: problem with SET-Operator on COLLECTIONS [message #433664 is a reply to message #433655] Thu, 03 December 2009 09:52 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member


And who told you that SET() is a collection method?
Razz
Re: problem with SET-Operator on COLLECTIONS [message #433666 is a reply to message #433664] Thu, 03 December 2009 10:06 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
Found it in the meantime:
http://www.oracle-developer.net/display.php?id=303
Quote:
In PL/SQL, Oracle cannot determine the SET from the complex collection (although the error message is not particularly helpful in this instance)...

So I have to create a MAP method and try again.
SET SERVEROUTPUT ON SIZE 100000;

--build up types
CREATE OR REPLACE TYPE fruitT AS OBJECT(
   fruit   VARCHAR2(100),
   MAP     MEMBER FUNCTION sort_key RETURN VARCHAR2);

CREATE TYPE BODY fruitT AS
   MAP MEMBER FUNCTION sort_key     RETURN VARCHAR2
   IS  BEGIN RETURN fruit; END;
END;
/
SHOW ERRORS;

CREATE OR REPLACE TYPE fruitsV AS TABLE OF VARCHAR2(100);
/
SHOW ERRORS;

CREATE OR REPLACE TYPE fruitsT AS TABLE OF fruitT;
/
SHOW ERRORS;


--test it
DECLARE
    fruitBoxV fruitsV:= fruitsV(       'Apple',        'Apple',        'Orange',        'Banana',        'Peach');
    fruitBoxF fruitsT:= fruitsT(fruitT('Apple'),fruitT('Apple'),fruitT('Orange'),fruitT('Banana'));
    cnt INTEGER;
BEGIN
    DBMS_OUTPUT.PUT_LINE(    fruitBoxV.count);
    DBMS_OUTPUT.PUT_LINE(SET(fruitBoxV).count);
    DBMS_OUTPUT.PUT_LINE(    fruitBoxF.count);
    --no ERROR !
    DBMS_OUTPUT.PUT_LINE(SET(fruitBoxF).count);

    SELECT count(*) INTO cnt FROM TABLE(CAST(fruitBoxV AS fruitsV));
    dbms_output.put_line('fruitBoxV='||cnt);
    SELECT count(*) INTO cnt FROM TABLE(CAST(fruitBoxF AS fruitsT));
    dbms_output.put_line('fruitBoxF='||cnt);

END;
/

--clean up
DROP TYPE              fruitsT FORCE;

DROP TYPE              fruitsV FORCE;

DROP TYPE              fruitT  FORCE;


Thanks a lot.

[Updated on: Thu, 03 December 2009 10:14]

Report message to a moderator

Re: problem with SET-Operator on COLLECTIONS [message #433671 is a reply to message #433666] Thu, 03 December 2009 10:58 Go to previous message
Michel Cadot
Messages: 63809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback!

Regards
Michel
Previous Topic: Sql query for display client version.
Next Topic: generic function for contains
Goto Forum:
  


Current Time: Thu Sep 29 02:13:17 CDT 2016

Total time taken to generate the page: 0.10521 seconds