Skip to Main Content
  • Questions
  • Stored procedure to truncate multiple tables not doing anything

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, kuldeep.

Asked: March 13, 2020 - 7:04 am UTC

Answered by: Chris Saxon - Last updated: March 17, 2020 - 10:01 am UTC

Category: SQL Developer - Version: 12c

Viewed 1000+ times

You Asked

Hello,

I have written a stored procedure to truncate the multiple tables. When running the procedure no error but when checking by select statement still I can see that in the table. This means the procedure is not doing anything. Any help much appreciated. What I am missing here.

create or replace PROCEDURE PROCEDURE2 (ov_v_output OUT VARCHAR2)
IS
BEGIN
 FOR i IN (SELECT table_name FROM user_tables WHERE table_name IN ('Detailed_REPORT_AGENT','Summary_REPORT_AGENT'))

 LOOP
      EXECUTE immediate 'TRUNCATE TABLE ' || i.table_name;

  END LOOP;
 ov_v_output := 'Success';
END PROCEDURE2;


Regards,
Kuldeep Sharma

and we said...

The truncate will only execute if

SELECT table_name FROM user_tables WHERE table_name IN ('Detailed_REPORT_AGENT','Summary_REPORT_AGENT')


returns rows.

You've checked this query returns what you expect, right?

and you rated our response

  (5 ratings)

Reviews

March 14, 2020 - 3:46 pm UTC

Reviewer: A reader

I checked the query result it is not returning anything but when running select count(*) from table name i am getting the result. What code should i use

March 15, 2020 - 5:28 am UTC

Reviewer: kuldeep from United Kingdom

Hi,

When I running the Sql query SELECT table_name FROM user_tables
not seeing my table name in the results but table exist in the DB. Could you help out?

Check the table name

March 16, 2020 - 5:57 am UTC

Reviewer: A reader


The table names should be in UPPER CASE unless these were created in the mixed case using double quotes.

Try querying using the table names in UPPER CASE. 

SELECT table_name FROM user_tables WHERE table_name IN ('Detailed_REPORT_AGENT','Summary_REPORT_AGENT'

** If the above does not return any rows then:

SELECT table_name FROM user_tables WHERE UPPER(table_name) IN ('DETAILED_REPORT_AGENT','SUMMARY_REPORT_AGENT');

Cheers

March 16, 2020 - 7:50 am UTC

Reviewer: kuldeep from United Kingdom

Thank you. By running the below query I can see the table name and procedure is also working.
SELECT table_name FROM user_tables WHERE UPPER(table_name)
IN ('DETAILED_REPORT_BGO_AGENT','SUMMARY_REPORT_BGO_AGENT')
Connor McDonald

Followup  

March 16, 2020 - 10:19 am UTC

Glad you worked it out.

March 16, 2020 - 5:17 pm UTC

Reviewer: kuldeep from United Kingdom

But I have created 5 tables, only showing 3 tables name. Still struggling why the other 2 tables' names are missing. Any idea?
Chris Saxon

Followup  

March 17, 2020 - 10:01 am UTC

What exactly is the DDL for the missing tables and how are you searching for them in user_tables?

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.