Home » SQL & PL/SQL » SQL & PL/SQL » how to delete all the rows from a table?
how to delete all the rows from a table? [message #39464] Thu, 18 July 2002 07:00 Go to next message
figen
Messages: 10
Registered: July 2002
Junior Member
Truncate or Delete? I know Delete might cause a lot of rollback and fire all the delete triggers. But I cannot use "Truncate" command from a procedure, because it is DDL command.

I am writng a Java script which will call Procedure and take the "table_name" as an argument and delete all the rows in that table.

Is there any way doing this from a Java Script other than Procedure or how should be the procedure?

Thanks in advance
Re: how to delete all the rows from a table? [message #39465 is a reply to message #39464] Thu, 18 July 2002 07:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
U can use a truncate inside stored procedure.
use Dynamic sql.
Re: how to delete all the rows from a table? [message #39467 is a reply to message #39464] Thu, 18 July 2002 07:55 Go to previous messageGo to next message
figen
Messages: 10
Registered: July 2002
Junior Member
Thank you so much Mahesh, but I have no idea what the dynamic SQL is, Can you give me a link or any resource that I can learn in a "very" short period of time?

And do you think is "truncate" better way to go?

Thanks again
Re: how to delete all the rows from a table? [message #39469 is a reply to message #39464] Thu, 18 July 2002 08:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
do something like this.
for example table EMP is truncated here.
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> create or replace procedure truncate_table
  2  (TName in varchar2)
  3  authid current_user
  4  as
  5  str varchar2(300);
  6  begin
  7  str:='Truncate table '||TName;
  8  execute immediate str;
  9  end;
 10  /

Procedure created.

SQL> exec truncate_table('EMP');

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

  COUNT(*)
----------
         0

Re: how to delete all the rows from a table? [message #39504 is a reply to message #39464] Fri, 19 July 2002 18:53 Go to previous message
figen
Messages: 10
Registered: July 2002
Junior Member
Thank you so much !
Previous Topic: outer join
Next Topic: How to modify a trigger
Goto Forum:
  


Current Time: Tue Apr 23 22:27:52 CDT 2024