Some of the Many Quirks of Oracle

Oracle is a huge database system, one that I have used many times over my career. It has its good points. It has also been likened to a beast on more than one occasion by the various multitudes.

These quirks are in no certain or consistent order (just like the results from a SELECT statement in Oracle).

Booleans in PL/SQL

PL/SQL allows you to use a boolean type. Oracle's query language implementation does not. You can't do this:

FUNCTION IllustrateBooleanQuirk()
                RETURN BOOLEAN
IS
    v_result BOOLEAN := FALSE; 
BEGIN
    SELECT ( MOD(QuirkSequence.NEXTVAL, 2) = 1 )
    INTO   v_result
    FROM   DUAL;

    RETURN V_RESULT;
END;

But you can get around it by performing a comparison in the RETURN for this function:

FUNCTION IllustrateBooleanQuirk()
                RETURN BOOLEAN
IS
  v_result VARCHAR2 (5) := 'FALSE';
BEGIN
    SELECT CASE
             WHEN MOD(QuirkSequence.NEXTVAL, 2) = 1 THEN 'FALSE'
             ELSE 'TRUE'
           END
    INTO   v_result
    FROM   DUAL;

    RETURN ( V_RESULT = 'TRUE' );
END;

Applying "parameters" to a query

Sometimes, you just want to define a parameter or constant for a query without having to use PL/SQL. In my case, it is because I want the results of the query to be displayed in dbForge in a results grid. You can use the DEFINE command to set a replacement value. Oracle will take all of the DEFINEs executed prior to your DML/DDL, and replace each one as if you had typed in the quoted value in your query.

DEFINE SomeParameterish = "'12345'";

SELECT &SomeParameterish AS FakeColumn 
FROM   DUAL;

would execute as:

SELECT '12345' AS FakeColumn 
FROM   DUAL;