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.

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:
share|improve this question
    
Try putting the column names in, 1/2 at a time to determine which column is causing the error. –  Leigh Riffel Jan 21 '13 at 14:20
    
Can you tell us what types the error reported? It should say something like "expected varchar got number". –  Colin 't Hart Jan 21 '13 at 14:39
    
The full error is (exactly, I didn't trimmed anything): ORA-00932: inconsistent datatypes: expected got ORA-02063: preceding line from DBLINK 00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause: *Action: –  Jackey Cheung Jan 22 '13 at 1:27
    
Is table1@dblink perhaps actually a view? –  Colin 't Hart Jan 22 '13 at 11:10
    
nope, both are tables. –  Jackey Cheung Jan 24 '13 at 2:21
show 5 more comments

1 Answer

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;
share|improve this answer
    
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 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 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 at 8:32
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.