Being fooled by Oracle

2007-01Jan-25
PL/SQL is a programming language that is quite close to Visual FoxPro. Many string functions have the same syntax as in Visual FoxPro, such as SUBSTR() or are pretty close like PADR() that becomes RPAD() in Oracle. However, with concatenation there's a huge difference in Oracle. To combine strings you use the "||" operator instead of "+". That would be fine, if the plus operator wouldn't work with strings, too:
 

SELECT '23'+'45' FROM DUAL;



SELECT 'A'+'B' FROM DUAL;

ORA-00900: invalid SQL statement


In a real world application, though, you won't use such values hard coded in a SELECT statement. Rather, you have a complex PL/SQL stored procedure and the error message comes up in a huge combined INSERT FROM SELECT statement. If you are used to a language like Visual FoxPro that uses plus for strings, too, you might not immediately spot this error.