Below you’ll find a basic example of Oracle’s Execute Immediate statement. This statement allows executing dynamic queries or DDL statements from within PL/SQL.

In the example we count how many records there are in a specific table with a certain value for a field.

The table, field and value are not known upfront and passed as parameters of the stored procedure. The number of found records is returned as a result.

PROCEDURE DYNAMIC_GET_VALUE
(
	P_TABLE		IN  VARCHAR,
	P_FIELD		IN  VARCHAR,		
	P_VALUE		IN  VARCHAR,
	P_RESULT 	OUT  NUMBER
)
AS
	V_RESULT	NUMBER;
BEGIN
	EXECUTE IMMEDIATE
		'SELECT COUNT('||P_FIELD||')'||
		' FROM  '||P_TABLE||
		' WHERE '||P_FIELD||' = '||P_VALUE 
	INTO V_RESULT;

	P_RESULT = V_RESULT;

	RETURN P_RESULT;
END;