Home » SQL & PL/SQL » SQL & PL/SQL » TRUNCATE TABLE inside a package giving Compilation Error
TRUNCATE TABLE inside a package giving Compilation Error [message #323029] Tue, 27 May 2008 02:20 Go to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
I am trying to use TRUNCATE TABLE <table_name> inside a package and it is giving me the following error message:

PLS-00103: Encountered the symbol "TABLE" when expecting one of
the following:
:= . ( @ % ;
The symbol ":= was inserted before "TABLE" to continue.


But when i substitute it with
DELETE FROM <table_name>
it works fine.

What seems to be the issue ?
Re: TRUNCATE TABLE inside a package giving Compilation Error [message #323040 is a reply to message #323029] Tue, 27 May 2008 02:57 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
TRUNCATE is DDL (data definition language). You cannot perform DDL from within PL/SQL. That is, you cannot directly but you can through dynamic SQL. I don't recommend it though.

Are you sure you want to truncate tables through a package?

MHE
Re: TRUNCATE TABLE inside a package giving Compilation Error [message #323058 is a reply to message #323040] Tue, 27 May 2008 03:54 Go to previous messageGo to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
Maaher wrote on Tue, 27 May 2008 13:27
TRUNCATE is DDL (data definition language). You cannot perform DDL from within PL/SQL. That is, you cannot directly but you can through dynamic SQL. I don't recommend it though.

Are you sure you want to truncate tables through a package?

MHE


Is there any problem with using a truncate table inside a package ?

I thing using Dynamic is an option but will only increase overhead and instead i use
DELETE FROM <table_name>
Re: TRUNCATE TABLE inside a package giving Compilation Error [message #323061 is a reply to message #323058] Tue, 27 May 2008 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any problem with using a truncate table inside a package ?

Break transaction.
System locks.
Statement Failure.
A couple that come into mind.

Quote:
I thing using Dynamic is an option but will only increase overhead

Why? Did you benchmark this?

Regards
Michel
Re: TRUNCATE TABLE inside a package giving Compilation Error [message #323065 is a reply to message #323058] Tue, 27 May 2008 04:06 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Is there any problem with using a truncate table inside a package ?
Obviously, there is. You've run into it. Wasn't that proof enough?

Quote:
I thing using Dynamic is an option but will only increase overhead and instead i use DELETE FROM <table_name>
Hm, OK. However, DELETE may take a lot of time (just imagine you are deleting millions of records; Oracle has to store that information in case you want to perform rollback. Or, a "complicated" WHERE clause might also be time demanding) so - dynamic SQL overhead may be "nothing" if compared to lengthy DELETE.
Re: TRUNCATE TABLE inside a package giving Compilation Error [message #323195 is a reply to message #323065] Tue, 27 May 2008 12:00 Go to previous messageGo to next message
mandeepmandy
Messages: 79
Registered: May 2008
Location: USA
Member

Use This

EXECUTE IMMEDIATE 'TRUNCATE TABLE <tablename>';

Thanks
Mandeep
Re: TRUNCATE TABLE inside a package giving Compilation Error [message #323269 is a reply to message #323195] Wed, 28 May 2008 00:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
mandeepmandy wrote on Tue, 27 May 2008 19:00
Use This

EXECUTE IMMEDIATE 'TRUNCATE TABLE <tablename>';

Thanks
Mandeep

Did you care to read the whole thread? The part where Maaher tells the original poster that he can do it using dynamic SQL, yet he does not recommend? The part where the original poster answers that?
Re: TRUNCATE TABLE inside a package giving Compilation Error [message #404909 is a reply to message #323029] Mon, 25 May 2009 07:13 Go to previous messageGo to next message
suresbala
Messages: 2
Registered: November 2006
Location: bangalore
Junior Member
The "truncate table" command and all other DDL statements, are not supported directly in standard (static) PL\SQL. You can use those statements in dynamic PL\SQL if the user either is the owner of the table, or has the appropriate privilege(s). If you have Oracle8.1.5 or higher, you can use the "execute immediate..." syntax for dynamic PL\SQL. If you have an older version, you need the slower and more complex syntax of the procedures/functions in the dbms_utl package.

Here is an example from one of my procedures to do a truncate using "execute immediate":
execute immediate ('truncate table disabled_triggers');

Note that this works if the PL\SQl procedure is in the same schema as the table owner. Otherwise the procedure owner may need the "alter any table" system privilege and that is not a privilege you want many users to have.
Re: TRUNCATE TABLE inside a package giving Compilation Error [message #404910 is a reply to message #404909] Mon, 25 May 2009 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"execute immediate" is a statement not a function, you must not put parenthesis.

To be able to truncate others table the privilege is DROP ANY TABLE not "alter any table".

Regards
Michel
Re: TRUNCATE TABLE inside a package giving Compilation Error [message #404921 is a reply to message #404910] Mon, 25 May 2009 08:25 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
8:48:59 SQL> desc item2
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ITEM_NO                                   NOT NULL NUMBER(20)
ITEM_NAME                                 NOT NULL VARCHAR2(2000)
ITEM_CATEGORY                                      VARCHAR2(128)
ITEM_CAEGORY_ID                                    NUMBER(32)


18:49:06 SQL>  select count(*) from ITEM2;

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


18:49:36 SQL> insert into ITEM2  select * from item;

1 row created.

18:50:09 SQL> commit;

Commit complete.


18:50:11 SQL> select count(*) from ITEM2;

  COUNT(*)
----------
         1

 CREATE OR REPLACE PROCEDURE sp_dd_table ( itable_name VARCHAR2) IS
  l_table VARCHAR2(255);
 BEGIN
   l_table := 'truncate table ' || itable_name;
   dbms_utility.exec_ddl_statement(l_table);
 dbms_output.put_line(itable_name||' '||'Truncated');
  END sp_dd_table;
Procedure created.

18:46:00 SQL> exec sp_dd_table('ITEM2');
ITEM2 Truncated

PL/SQL procedure successfully completed.


18:46:05 SQL> select count(*) from ITEM2;

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



Regards
sriram
Re: TRUNCATE TABLE inside a package giving Compilation Error [message #405037 is a reply to message #404921] Tue, 26 May 2009 02:39 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And here is why you don't want to do this:
SQL> create table tab1 (id number);

Table created.

SQL> create table tab2 (id number);

Table created.

SQL> insert into tab1 values (1);

1 row created.

SQL> insert into tab2 values (1);

1 row created.

SQL> 
SQL> create or replace procedure sp_dd_table
  2  ( itable_name varchar2
  3  ) as
  4    l_table varchar2(255);
  5  begin
  6    l_table := 'truncate table ' || itable_name;
  7    dbms_utility.exec_ddl_statement(l_table);
  8  end sp_dd_table;
  9  /

Procedure created.

SQL> 
SQL> insert into tab1 values (2);

1 row created.

SQL> insert into tab2 values (2);

1 row created.

SQL> exec sp_dd_table('TAB1');

PL/SQL procedure successfully completed.

SQL> 
SQL> rollback;

Rollback complete.

SQL> 
SQL> select * from tab1;

no rows selected

SQL> select * from tab2;

        ID
----------
         1
         2
Previous Topic: how to avoid maximum open cursors exceeded
Next Topic: lpad
Goto Forum:
  


Current Time: Thu Dec 08 16:38:01 CST 2016

Total time taken to generate the page: 0.07379 seconds