Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1774
  • Last Modified:

Truncate Table in a Procedure

hi all,
 
How do I run a truncate command in a Procedure. the user running the procedure will own the table I will be truncating.
 
thanks
 
shankar
0
rxs0005
Asked:
rxs0005
1 Solution
 
slightwv (䄆 Netminder) Commented:
Not sure of the exact requirements but truncating a table in a procedure may not be the best approach.  For example, you will need to guarantee that  the procedure is only executed one at a time.  Again, not knowing the requirements, have you looked into global temporary tables?

All that said (untested but should work):

begin
...
execute immediate 'truncate table table_name';
...
end;
0
 
helpneedCommented:
hi

for all ddl commends to execute in procedures u have to use execute immediate with them

called dynamic sqls

regards
0
 
plamen73Commented:
The main advise is to carefully revise the requirements about this truncate statement.
The only option to use in a procedure is the dynamic execute immediate already suggested.
However, if you have foreign keys to this table, you might face the following error:

------------------------------------------
ORA-02266 unique/primary keys in table referenced by enabled foreign keys
Cause: An attempt was made to drop or truncate a table with unique or
primary keys referenced by foreign keys enabled in another table.
Action: Before dropping or truncating the table, disable the foreign key
constraints in other tables. You can see what constraints are referencing a table
by issuing the following command:
select constraint_name, table_name, status
from user_constraints
where r_constraint_name in (
select constraint_name from user_constraints where table_name
=’tabnam’);
-------------------------------------------
Pay attention it does not metter if there are actually childs or not, it is enough to have a enabled foreign key and your statement will fail.
0
 
kripa_odbaCommented:
Using Execute Immediate you can run ddl commands inside a pl/sql procedure....


eg.. execute immediate 'truncate table table_name';


Or you can do this in other way using oracle supplied dbms_sql package.......
-------------------------------------------------------------------------------------

create or replace procedure procName
crsor integer;
rval  integer;
begin
 crsor := dbms_sql.open_cursor;
 dbms_sql.parse(crsor, 'truncate table tblname REUSE STORAGE ,dbms_sql.v7);
 rval := dbms_sql.execute(crsor);
 dbms_sql.close_cursor(crsor);
end;
 /


Thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now