CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
samg
samg

Reputation: 333

PLSQL Procedure to Truncate and Re-populate table

I have some tables and views in my schema and I am trying to create a stored procedure that will take in 2 parameters (table_name, view_name) to Truncate a table and re-populate it from a view.

Here is the code I have for the procedure: 

CREATE OR REPLACE 
PROCEDURE PROC_NAME (TABLE_NAME IN VARCHAR2, VIEW_NAME IN VARCHAR2) 
IS 
BEGIN 
EXECUTE IMMEDIATE 'TRUNCATE TABLE TABLE_NAME'; 

EXECUTE IMMEDIATE 'INSERT INTO TABLE_NAME
                   SELECT * FROM VIEW_NAME';
END; 
/

Now when I run the following code: 

BEGIN
    PROC_NAME('SOME_TABLE', 'SOME_VIEW');
END;
/

I get the following error: 

ORA-00942: table or view does not exist
ORA-06512: at "SCHEMA.PROC_NAME", line 4
ORA-06512: at line 2
00942. 00000 -  "table or view does not exist"

What do you guys think is the issue?

Thanks in advance!

Upvotes: 1

Views: 6652

Answers (1)

Unoembre
Unoembre

Reputation: 555

Try:

CREATE OR REPLACE 
PROCEDURE PROC_NAME (TABLE_NAME IN VARCHAR2, VIEW_NAME IN VARCHAR2) 
IS 
BEGIN 
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||TABLE_NAME; 

EXECUTE IMMEDIATE 'INSERT INTO '||TABLE_NAME||'
                   SELECT * FROM '||VIEW_NAME;
END; 
/

Your basic problem is that you had passed the parameters correctly but had not used them in the procedure. The fix was to used the the concatenation operaterator || in the strings used by EXECUTE IMMEDIATE to combine the the parameters into the string being executed.

An additional option is to use DELETE FROM rather than TRUNCATE TABLE. When Oracle first implemented Materialised Views, which is a grown up version of what you are trying to achieve, they made the same mistake. TRUNCATE TABLE is very quick but in the Oracle implementation it is a DDL (Data Definition Language) statement which means it will complete with an implicit commit. Therefore, for a period of time until the INSERT completes (and is committed), your table will be empty. If Oracle thought it important enough to change their underlying technique, then you should consider doing the same.

If you do not change to the DELETE technique then you should adding a COMMIT at the end of your procedure. The use of TRUNCATE TABLE will guarantee the removal of the data is committed, so if your INSERT succeeds then you should also commit that statement.

My reference to Materialised Views is relevant as it is a potential a built-in replacement for what you are trying to write for yourself. The problem with it is that it has so many bells and whistles that it is difficult to find an article on how to use it in your simple use case. I would welcome a comment referencing such an article.

Upvotes: 2

Related Questions