declare v_sql varchar2(200) := 'select <expression> from employees where employee_id = :id'; v_string_data varchar2(4000); begin v_sql := replace(v_sql, '<expression>','last_name'); execute immediate v_sql into v_string_data using 101; dbms_output.put_line(v_string_data); end;
In some circumstances, you will need to craft dynamic SQL statements. This example shows some of the features available when using EXECUTE IMMEDIATE to run a SQL query contained in a string. The INTO clause is only relevant with a SELECT statement. This is due to the fact that DML statements cannot return values. The <expression> portion of the string above can be replaced with any valid single column or even an expression (e.g. first_name, to_char(salary,''$999,999.00'')).
This example also demonstrates the use of id as a bind variable. (The use of a bind variable is a frequently cited Oracle "best practice". A bind variable allows the database to recognize common query statements that differ only in values present in bind variables. This benefits application performance). The using clause is used to populate the bind variable (:id).