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 DEFINE
s 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;