Issue of Concatenating DATE Variables into a Single String in the Dynamic SQL

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

The Correct ISNUMERIC Function in Oracle

1. In the function, I use REPLACE(UPPER()) to replace the 'E' with 'A', which fixes the issue with strings like '1234e2', otherwise Oracle considers it as a number, i.e. 123400
2. I prefer to return NUMBER instead of BOOLEAN, which allows me to use the function in DML SQL statements directly.
-----------------------------------------------------------------------------
FUNCTION ISNUMERIC (This_str IN VARCHAR2) RETURN NUMBER
IS
  v_dummy NUMBER;
BEGIN
  v_dummy := TO_NUMBER(REPLACE(UPPER(This_str),'E','A'));
  RETURN 0;
  EXCEPTION WHEN OTHERS THEN
    RETURN 1;
END;
/

However are Numbers and Characters Stored in Oracle

1. For integers others than those in tens (i.e. 10, 100...), 1-99 is stored using 2 bytes, 101~999 using 3 bytes...
2. For integers in tens, their trailing 0s are not used any extra bytes. E.g. 100000000 still uses 2 bytes. Not sure how does Oracle achieve it?
3. For characters, each character is stored using 1 bytes.
4. So if you need to store single digit like '0' or '1' indicator, better to store it as VARCHAR2(1), not NUMBER(1).

You can use vsize to verify.

How does DataStage Project Level Auto-Purge Job Logs Work

1. The job has to be run. I.e. if the job isn't run, its old logs will not be purged.
2. The job has to run successfully. E.g. if the job is aborted, its old logs will not be purged.

Someone mentioned to me that the project level auto-purge job logs only affects those jobs imported after auto-purge change. This doens't appear to be the behaviour with the version 8.5 I am working with.