Oracle Tip – EXECUTE IMMEDIATE With Bind Variable Input In Dynamic SQL

Caution: If there is no need to use a dynamic SQL, you should always use regular non-dynamic SQL. This is a bad usage of dynamic SQL and I am only doing so to illustrate the use of bind variables.

The task:

Again, I just wanted to provide a basic example that shows the usage of EXECUTE IMMEDIATE with bind variable output in dynamic SQL.

The bind variable is prefixed with colon (:) in the dynamic SQL statement. I don’t think it needs much explanation

Test Function

------------------------------------------
CREATE OR REPLACE FUNCTION fnc_Get_Table_Count
------------------------------------------
  (
   p_Owner IN VARCHAR2
  )
  RETURN NUMBER
IS
  v_Return   NUMBER;
  v_SQL    VARCHAR2(255);

BEGIN

  v_SQL := '
   SELECT COUNT(1)
   FROM DBA_TABLES t
   WHERE t.owner = :v_Owner';

  EXECUTE IMMEDIATE v_SQL
  INTO v_Return USING p_Owner;

  RETURN v_Return;
END fnc_Get_Table_Count;
/

Usage:

SELECT fnc_Get_Table_Count('SYS') AS SYS_Table_Count
FROM DUAL;

Returns:

SYS_TABLE_COUNT
---------------
1273

Clean up!

DROP FUNCTION fnc_Get_Table_Count;

SQL Injection – Dont’s!

DO NOT try to piece the whole string together without bind variables like this:

  v_SQL := '
   SELECT COUNT(1)
   FROM DBA_TABLES t
   WHERE t.owner = ''' || p_Owner || '''';

This would open the code up for SQL injection where people can pass in malicious values for p_Owner.