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;