DECLARE dyn_stmt VARCHAR2(200);
b1 boolean;
FUNCTION f(x INTEGER) RETURN boolean AS
BEGIN
RETURN FALSE;
END f;
BEGIN
dyn_stmt := 'BEGIN :b := f(5); END;';
EXECUTE IMMEDIATE dyn_stmt USING OUT b1; --line 9
b1:=f(5); --line 10
END;
Commenting line 10 throws error whereas Commenting line 9 works fine. Why ? According to error "expressions have to be of SQL types" and AFAIK PL/SQL supports boolean data type. Then, what's the problem here ?
As per the Oracle Documentation:
This specifically applies to dynamic PL/SQL (and SQL too):
At run time, bind arguments replace corresponding placeholders in the dynamic string. Every placeholder must be associated with a bind argument in the USING clause and/or RETURNING INTO clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals (TRUE, FALSE, and NULL). To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls to Dynamic SQL".
This does not apply to Dynamic PL/SQL (only SQL), but continuing for completeness:
Dynamic SQL supports all the SQL datatypes. For example, define variables and bind arguments can be collections, LOBs, instances of an object type, and refs. Dynamic SQL does not support PL/SQL-specific types. For example, define variables and bind arguments cannot be BOOLEANs or index-by tables. The only exception is that a PL/SQL record can appear in the INTO clause.
