Create Stored Procedure in Oracle 11g pl/sql using IF and THEN

Question

I was having an issue creating this stored procedure and had some errors when executing. Errors consisted of SQL statement ignored and SQL command not properly ended. I thought all the code was pretty clean.

Returns the total records and inserts it into the RECORD_COUNT variable from TABLE1 table.
Condition to see if RECORD_COUNT is greater than zero and dumps data to clear out TABLE1 table.
Condition to see if RECORD_COUNT equals zero in order to insert into TABLE1 table from the EXTERNAL_TABLE table.

Please assist.

    CREATE OR REPLACE PROCEDURE sp_INSERT
    (RECORD_COUNT OUT NUMBER)
    IS
    BEGIN   

         SELECT COUNT(*) 
             INTO RECORD_COUNT
             FROM TABLE1;   

         IF RECORD_COUNT > 0 THEN 
             EXECUTE IMMEDIATE 'TRUNCATE TABLE TABLE1'
         END IF;     

        IF RECORD_COUNT = 0 THEN 
        INSERT INTO TABLE1
            (
                JOB_ID,                         
                NUM_SP1,              
                NUM_SP2,              
                NUM_SP3,              
                NUM_SP4,                         
            )
            (SELECT JOB_ID,               
                NUM_SP1,              
                NUM_SP2,              
                NUM_SP3,              
                NUM_SP4,              
            FROM EXTERNAL_TABLE)
        COMMIT;
        END IF;
    END;

Hi, the error messages should help - they give you line numbers. You're missing a number of semi-colons and TRUNCATE is DDL and therefore cannot be preformed in PL/SQL outside of dynamic SQL. — Ben, Oct 01 '13 at 17:16

score 3 · Accepted Answer · edited May 23 '17 at 12:08

First: You need ; in the end of any instrauction in your procedure.
Second: TRUNCATE is a DDL command, DDL commands are not valid in plsql, you may use DELETE command instaed or use EXECUTE IMMEDIATE Statement:
Third: For insert-select statement see here and here.

CREATE OR REPLACE PROCEDURE sp_INSERT
    (RECORD_COUNT OUT NUMBER)
    IS
    BEGIN   
         SELECT NVL(COUNT(*),0) 
             INTO RECORD_COUNT
             FROM TABLE1;    

         IF RECORD_COUNT > 0 THEN 
             EXECUTE IMMEDIATE 'TRUNCATE TABLE TABLE1';
         END IF; 
         -- or
         IF RECORD_COUNT > 0 THEN 
             DELETE TABLE1;
         END IF; 


        IF RECORD_COUNT = 0 THEN 
        INSERT INTO TABLE1
            (
                JOB_ID,                         
                NUM_SP1,              
                NUM_SP2,              
                NUM_SP3,              
                NUM_SP4                         
            )
            SELECT JOB_ID,               
                NUM_SP1,              
                NUM_SP2,              
                NUM_SP3,              
                NUM_SP4              
            FROM EXTERNAL_TABLE;
        COMMIT;
        END IF;
    END;

/*
 VAR N NUMBER;
     EXCE SP_INSERT(:N);
        PRINT N;


             N
         ----------
             0

          INSERT INTO TABLE1 VALUES(1,1,1,1,1);
      INSERT INTO TABLE1 VALUES(1,1,1,1,1);
     INSERT INTO TABLE1 VALUES(1,1,1,1,1);

     COMMIT;
       VAR N NUMBER;
        EXCE SP_INSERT(:N);
        PRINT N;

             N
          -------
             3

      */

Your input helped me the best. – Fabe Dole Oct 01 '13 at 19:19 — Fabe Dole, Oct 01 '13 at 19:19

score 1 · Answer 2 · answered Oct 01 '13 at 17:24

You're missing a semicolon after your SELECT statement. You'd need something like

SELECT COUNT(*) 
  INTO RECORD_COUNT
  FROM TABLE1;

You also can't put DDL (like TRUNCATE) in PL/SQL as static SQL. You'd need to use dynamic SQL. You'll also need a semicolon at the end of that statement.

EXECUTE IMMEDIATE 'TRUNCATE TABLE table1';

Posting the exact error stack (including the line numbers) is always helpful-- compilers are generally much better at detecting syntax errors than humans reading forum posts.

score 1 · Answer 3 · answered Oct 01 '13 at 19:01

Both answers goes in good direction.
Here is another alternative - see if volume in table makes no problem for DELETE command:

/* just preparation for test  here */ 
create table TABLE1( JOB_ID int, num_sp1 int, num_sp2 int, num_sp3 int, num_sp4 int );
create table external_table as select * from TABLE1; 

CREATE OR REPLACE PROCEDURE sp_INSERT ( RECORD_COUNT OUT NUMBER )
 IS
BEGIN   

  delete TABLE1; 
  record_count := SQL%rowcount; 

  INSERT INTO TABLE1
       ( JOB_ID, NUM_SP1, NUM_SP2, NUM_SP3, NUM_SP4  )
  SELECT JOB_ID, NUM_SP1, NUM_SP2, NUM_SP3, NUM_SP4 
  FROM EXTERNAL_TABLE;
  COMMIT;
END;
/

show errors

And example output:

SQL> var n number
SQL> exec sp_insert( :n )

PL/SQL procedure successfully completed.

SQL> print n

         N                                                                      
----------                                                                      
         0                                                                      

SQL> insert into TABLE1 values ( 1, 1, 1, 1, 1 ) ;

1 row created.

SQL> insert into TABLE1 values ( 1, 1, 1, 1, 1 ) ;

1 row created.

SQL> insert into TABLE1 values ( 1, 1, 1, 1, 1 ) ;

1 row created.

SQL> commit;

Commit complete.

SQL> exec sp_insert( :n )

PL/SQL procedure successfully completed.

SQL> print n

         N                                                                      
----------                                                                      
         3

score 0 · Answer 4 · answered Aug 06 '14 at 12:57

CREATE OR REPLACE PROCEDURE EMP_INSERT (RECORD_COUNT OUT NUMBER) IS BEGIN

     SELECT COUNT(*) 
         INTO RECORD_COUNT
         FROM EMPEE1;   
          IF RECORD_COUNT > 0 THEN 
         EXECUTE IMMEDIATE 'TRUNCATE TABLE EMPEE1';
     END IF; 

     SELECT COUNT(*) 
         INTO RECORD_COUNT
         FROM EMPEE1;  

    IF RECORD_COUNT = 0 THEN 
    INSERT INTO EMPEE1
        (
            ENO,                         
            ENAME,              
            DOJ,              
            DEPTNO,              
            SAL,
            AGE                         
        )
        (SELECT ENO,               
            ENAME,              
            DOJ,              
            DEPTNO,              
            SAL,
            AGE              
        FROM EMPEE);

    COMMIT;
    END IF;
    END;

Create Stored Procedure in Oracle 11g pl/sql using IF and THEN

4 Answers4