Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know there is question related to inconsistent datatypes but that comes from a query. while I am getting this error on database side when we try to access user defined datatype on our schema that is written in some other schema. E.g. we are having xxxtypes schema and we have defined several UDT there. Now in our database we are using xxxtypes.employeeList, so it is throwing following error from that line.

java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got SYSTPzM8RTCHEUy/gQAB/AQBa6A==

EDIT: Q :Why this error is coming ? and How to overcome this ?

Examples:

create or replace
TYPE InstalmentList IS TABLE OF InstalmentEntry;

   create or replace
   TYPE InstalmentEntry IS OBJECT
   ( 
    reservationinstalment_id NUMBER,
    value NUMBER
   );

We have created above type in xxxtypes schema, to use it for several clients. Now for a client's database we are using it as :

  SELECT
          column_value AS list,
          rownum AS rownumber
        FROM
          TABLE(
            CAST(
              powermultiset(
                CAST(
                  MULTISET(
                    SELECT
                      rsv_int.reservationinstalment_id,
                      rsv_int.value
                    FROM reservationinstalment rsv_int
                    WHERE
                      rsv_int.reservation_id = p_reservation_id
                      AND
                      rsv_int.payer_type = p_payer_type
                      AND
                      rsv_int.type <> 'r'
                  )
                  AS
                  **xxxtypes.InstalmentList**
                )
              )
              AS
              InstalmentListList
            )
          )
      ) combinations

now xxxtypes.InstalmentList is throwing error saying that it is getting some inconsistent datatypes.

share|improve this question
    
What is the question?!? Also, we won't be able to help you without at least the offending code and ideally a complete example with CREATE TABLE/TYPE/PROCEDURE statements so that we can reproduce the error ! –  Vincent Malgrat Jan 31 '13 at 10:09
    
xxxtypes.InstalmentList in your query should be InstalmentEntry. –  FreshPhilOfSO Jan 31 '13 at 10:33
    
Can you tell me why this error is coming ? –  Ram Dutt Shukla Jan 31 '13 at 10:37
    
You're casting the multiset to xxxtypes.InstalmentList instead of InstalmentEntry. –  FreshPhilOfSO Jan 31 '13 at 10:46
    
But at the end of the day InstalmentList is a TABLE OF InstalmentEntry, so what makes OBJECT type working and TABLE type not working ? –  Ram Dutt Shukla Jan 31 '13 at 11:17
show 1 more comment

1 Answer

up vote 1 down vote accepted

The type InstalmentList is a nested table of InstalmentEntry, so you need to build a set of InstalmentEntry objects to get the list.

The following query works on 11.2:

SELECT column_value AS list, rownum AS rownumber
  FROM TABLE(powermultiset(
               CAST(MULTISET (SELECT InstalmentEntry(1, 1) /*Entry "constructor"*/
                                FROM dual) 
                    AS InstalmentList)
             ));
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.