Oracle stored procedure with case when in the where cause

Question

The following stored procedure complied with errors. It seems the errors are in the case statement in the where cause. How do I fix it?

create or replace PROCEDURE APPEND_HIST_TBLS_PROC AS 
CurTerm varchar2(4) := '1222';
PS_ACAD_PROG_HISTORY varchar2(35) := 'PS_ACAD_PROG_HISTORY_' || CurTerm;
Begin
execute immediate 'insert into ' || PS_ACAD_PROG_HISTORY
|| '(select sysdate as date_created,
EFFDT, 
ADMIT_TERM, 
EXP_GRAD_TERM, 
CAMPUS
from ERP_ACAD
where CASE WHEN SUBSTR(ADMIT_TERM,4,1)= '6' THEN SUBSTR(ADMIT_TERM,1,3) || '9' ELSE ADMIT_TERM END = '||CurTerm ||'
)';
END APPEND_HIST_TBLS_PROC;

Answer 1

Dynamic SQL is difficult to maintain and debug. The fact that procedure compiled successfully tells nothing about the dynamic statement itself - it just says that there were no errors in the procedure "as is".

It is a good habit to compose the statement and store it into a local variable and then display its contents; once you verify it is OK, then execute it.

Also, as you have to escape single quotes (sometimes it becomes nasty, with several consecutive single quotes which do what you need), use the q-quoting mechanism which lets you write "normal" statements (single quotes really are single, then).

Something like this:

SQL> CREATE OR REPLACE PROCEDURE append_hist_tbls_proc
  2  AS
  3     curterm               VARCHAR2 (4) := '1222';
  4     ps_acad_prog_history  VARCHAR2 (35) := 'PS_ACAD_PROG_HISTORY_' || curterm;
  5     l_str                 VARCHAR2 (4000);
  6  BEGIN
  7     -- Compose the INSERT statement into a VARCHAR2 local variable so that you'd be able
  8     -- to check whether you did it right or not.
  9     -- Use the q-quoting mechanism as it helps with consecutive single quotes issues
 10     l_str :=
 11           'INSERT INTO '
 12        || ps_acad_prog_history
 13        || q'[ SELECT sysdate as date_created,
 14                      effdt,
 15                      admit_term,
 16                      exp_grad_term,
 17                      campus
 18        FROM erp_acad
 19        WHERE CASE WHEN SUBSTR(admit_term, 4, 1) = '6' THEN
 20                        SUBSTR(admit_term, 1, 3) || '9'
 21                   ELSE ADMIT_TERM
 22              END = ]'
 23        || curterm;
 24
 25     -- FIRST check the command you're about to execute
 26     DBMS_OUTPUT.put_line (l_str);
 27
 28     -- When you verified that it is correct, then comment DBMS_OUTPUT.PUT_LINE call
 29     -- and uncomment EXECUTE IMMEDIATE
 30     -- EXECUTE IMMEDIATE l_str;
 31  END append_hist_tbls_proc;
 32  /

Procedure created.

Let's try it:

SQL> SET SERVEROUTPUT ON
SQL> EXEC append_hist_tbls_proc;
INSERT INTO PS_ACAD_PROG_HISTORY_1222 SELECT sysdate as date_created,

effdt,
                    admit_term,
                    exp_grad_term,

campus
      FROM erp_acad
      WHERE CASE WHEN SUBSTR(admit_term, 4, 1) = '6'
THEN
                      SUBSTR(admit_term, 1, 3) || '9'
                 ELSE
ADMIT_TERM
            END = 1222

PL/SQL procedure successfully completed.

SQL>

Output looks ugly (that's the price of having it pretty in the procedure), but - if you format it - it looks like this:

INSERT INTO PS_ACAD_PROG_HISTORY_1222
   SELECT SYSDATE AS date_created,
          effdt,
          admit_term,
          exp_grad_term,
          campus
     FROM erp_acad
    WHERE CASE
             WHEN SUBSTR (admit_term, 4, 1) = '6'
             THEN
                SUBSTR (admit_term, 1, 3) || '9'
             ELSE
                ADMIT_TERM
          END = 1222

So, if you can really execute it (I can't, I don't have your tables), then uncomment execute immediate and use the procedure. Otherwise, fix the statement.