5

Sorry for the meaningless title, but I couldn't think up a better one. I'm using Oracle 11g r2.

The following query raise ORA-00932 error:

SELECT BBB.FIELD1 FROM TABLE0 AAA
  JOIN (SELECT * FROM (SELECT AAA.*, RANK() OVER (PARTITION BY SUBSTR(FIELD1,1,1) ORDER BY SUBSTR(FIELD1,1,1),ROWNUM) AS RANK
      FROM TABLE1@DBLINK AAA WHERE UPPER(NAME)!='XXX')
    WHERE RANK<=1) BBB ON CAST(CAST(CAST(SUBSTR(BBB.FIELD1,2,LENGTH(BBB.FIELD1)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(BBB.FIELD1,LENGTH(BBB.FIELD1)) AS VARCHAR2(4000))=CAST(CAST(CAST(SUBSTR(AAA.FIELD0,2,LENGTH(AAA.FIELD0)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(AAA.FIELD0,LENGTH(AAA.FIELD0)) AS VARCHAR2(4000));

However, the following query works perfectly fine.

SELECT * FROM TABLE0 AAA
  JOIN (SELECT * FROM (SELECT AAA.*, RANK() OVER (PARTITION BY SUBSTR(FIELD1,1,1) ORDER BY SUBSTR(FIELD1,1,1),ROWNUM) AS RANK
      FROM TABLE1@DBLINK AAA WHERE UPPER(NAME)!='XXX')
    WHERE RANK<=1) BBB ON CAST(CAST(CAST(SUBSTR(BBB.FIELD1,2,LENGTH(BBB.FIELD1)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(BBB.FIELD1,LENGTH(BBB.FIELD1)) AS VARCHAR2(4000))=CAST(CAST(CAST(SUBSTR(AAA.FIELD0,2,LENGTH(AAA.FIELD0)-2) AS NUMBER) AS VARCHAR2(10))||SUBSTR(AAA.FIELD0,LENGTH(AAA.FIELD0)) AS VARCHAR2(4000));

The only difference is that I selected all (*) columns instead of specifying one specific column. Why is this happening?

EDIT

The error raised is (exactly as it is):

ORA-00932: inconsistent datatypes: expected  got 
ORA-02063: preceding line from DBLINK
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
8
  • Try putting the column names in, 1/2 at a time to determine which column is causing the error. Jan 21, 2013 at 14:20
  • Is table1@dblink perhaps actually a view? Jan 22, 2013 at 11:10
  • nope, both are tables. Jan 24, 2013 at 2:21
  • Can you update the question with a desc of each table? Jan 24, 2013 at 16:42
  • all fields are varchar2(10) Jan 25, 2013 at 3:46

2 Answers 2

3

When you are casting a varchar to a number you have to make sure this is possible for ALL values in this table. So in your case also for the values you think are filtered by "WHERE UPPER(NAME)!='XXX'". The reason is the optimizer does not have to follow a specific order for the logic in your query. So it could do the casting and joining the tables frist and only after do the filtering specified in the inner query. Changing the * to a specific field can change the execution plan the optimizer chooses, hence you could have the problem with one version and not the other.

You could use your own function to avoid this error:

create or replace function tonumberorzero(txt in varchar2) return number
 is
   retval number;
 begin
    return to_number(txt);
 exception
    when invalid_number then return 0;
 end;
3
  • It seems that casting a non number to number raises an invalid_number excption "ORA-01722: invalid number" (You used this fact in your script) and not a "ORA-00932". So how should your function exactly be used to avoid the error? Maybe the database link may "transform" a ORA-01722 error to a ORA-00932 error. Suppressing an exception by catching it and returning 0 instead of will return wrong data.
    – miracle173
    Mar 17, 2014 at 6:55
  • But I agree with your notice that different execution plans and therefore processing of different data will be responsible for the different behavour of the two statements.
    – miracle173
    Mar 17, 2014 at 6:56
  • I tested select cast(dummy as number) from dual@dblink. The error message remain: ORA-01722: invalid number. So I see no chance that your function can help to avoid this error.
    – miracle173
    Mar 17, 2014 at 8:32
0

In my case, I used SUBSTR() over a CLOB column and used the result to compare it with another column that was VARCHAR and I got the error because the result of the SUBSTR was considered a CLOB. I used ASCIISTR(SUBSTR(...)) and it worked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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