When you embed an INSERT, UPDATE, DELETE, or SELECT SQL statement directly in your PL/SQL code, PL/SQL turns the variables in the WHERE and VALUES clauses into bind variables automatically. Oracle can reuse these SQL statement each time the same code is executed. To run similar statements with different variable values, you can save parsing overhead by calling a stored procedure that accepts parameters, then issues the statements with the parameters substituted in the appropriate places.
You do need to specify bind variables with dynamic SQL, in clauses like WHERE and VALUES where you normally use variables. Instead of concatenating literals and variable values into a single string, replace the variables with the names of bind variables (prefixed by a colon) and specify the corresponding PL/SQL variables with the USING clause. Using the USING clause, instead of concatenating the variables into the string, reduces parsing overhead and lets Oracle reuse the SQL statements.
Although the above is the best practice from Oracle, it seems an issue of concatenating DATE variables into a single string in the dynamic SQL - NOT an issue of concatenating NUMBER variables though. Using a bind variable to pass in the DATE variable worked.
The following illustrates the issue, although there is no need to use dynamic SQL there:
SQL> CREATE OR REPLACE PROCEDURE TEST_PROC(This_customer_id IN NUMBER, This_date IN DATE) IS
2 num_rows NUMBER(9);
3 my_date DATE;
4 BEGIN
5 EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM ORDERS WHERE CUSTOMER_ID
= ' || This_customer_id || ' AND ORDER_DATE >= :my_date' INTO num_rows USING This_date;
6 DBMS_OUTPUT.PUT_LINE(num_rows);
7 END TEST_PROC;
8 /
Procedure created.
SQL> EXEC TEST_PROC(101,SYSDATE-1000);
81005
PL/SQL procedure successfully completed.
SQL> EXEC TEST_PROC(101,'24-SEP-09');
81005
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE PROCEDURE TEST_PROC(This_customer_id IN NUMBER, This_date IN DATE) IS
2 num_rows NUMBER(9);
3 --my_date DATE;
4 BEGIN
5 EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM ORDERS WHERE CUSTOMER_ID = ' ||
This_customer_id || ' AND ORDER_DATE >= ' || This_date INTO num_rows;
6 DBMS_OUTPUT.PUT_LINE(num_rows);
7 END TEST_PROC;
8 /
Procedure created.
SQL> EXEC TEST_PROC(101,SYSDATE-1000);
BEGIN TEST_PROC(101,SYSDATE-1000); END;
*
ERROR at line 1:
ORA-00904: "SEP": invalid identifier
ORA-06512: at "ABC.TEST_PROC", line 5
ORA-06512: at line 1
