Skip to Main Content
  • Questions

  • Stored procedure to truncate multiple tables not doing anything

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, kuldeep.

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

Last updated: October 22, 2021 - 3:16 am UTC

Version: 12c

Viewed 10K+ times! This question is

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 Chris 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?

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 14, 2020 - 3:46 pm UTC

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

kuldeep, March 15, 2020 - 5:28 am UTC

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

A reader, March 16, 2020 - 5:57 am UTC 


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

kuldeep, March 16, 2020 - 7:50 am UTC

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
March 16, 2020 - 10:19 am UTC

Glad you worked it out.

kuldeep, March 16, 2020 - 5:17 pm UTC

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
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?

Asking for resolution

David Baldwin, October 21, 2021 - 2:18 pm UTC

Hello,

This is my first comment on asktom. I was just wondering if someone can comment to help us find the resolution to kuldeep's final comment on the thread here.
Connor McDonald
October 22, 2021 - 3:16 am UTC

Welcome!

Apologies

David Baldwin, October 21, 2021 - 2:19 pm UTC

I see now that kuldeep is the one who did not reply.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library