Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Define a MAP or ORDER method for the object type

MuzzOct 19 2015 — edited Oct 21 2015

Hi Gurus

I created an object and then its type and then I use that object and type in pipe line function, see below:

Create Object

CREATE OR REPLACE

TYPE test_object

IS

  OBJECT

  (

    next_appearance_dt     DATE,         --next_appearance_dt

    youth_adult            VARCHAR2(5) --youth_adult

    ) ;


/

Create Object Type

CREATE OR REPLACE TYPE t_docket_object IS TABLE OF test_object;

/

Create Pipeline function

CREATE OR REPLACE FUNCTION f_report(p_dt date,p_c_cd VARCHAR2)
return t_test_object pipelined
IS
BEGIN
FOR J IN (
select distinct test_object(
next_appearance_dt, --862,
'YOUTH'
) AS test_object
FROM jen.next_appearance base
WHERE  1=1
AND(base.next_appearance_dt= p_dt)
AND(base.circuit_point_cd = p_c_cd)
--and cse.information_id=322
--ORDER BY 15--alias_name
)

loop
PIPE ROW(J.test_object);
END loop;
END;

/

Execute function

SELECT *  FROM TABLE (F_REPORT(TO_DATE('25-sep-2015','dd-mon-yyyy'),'1'))

Error

ORA-22950: cannot ORDER objects without MAP or ORDER method

ORA-06512: at "F_REPORT", line 5

22950. 00000 -  "cannot ORDER objects without MAP or ORDER method"

*Cause:    an object type must have a MAP or ORDER method defined for

           all comparisons other than equality and inequality comparisons.

*Action:   Define a MAP or ORDER method for the object type

I know the reason for this error and the reason is that I use distinct clause in my pipeline function but don't know how to get rid of this error...

This post has been answered by Solomon Yakobson on Oct 19 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 18 2015
Added on Oct 19 2015
17 comments
3,415 views