TRUNCATE TABLE inside a package giving Compilation Error [message #323029] |
Tue, 27 May 2008 02:20 |
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 #323058 is a reply to message #323040] |
Tue, 27 May 2008 03:54 |
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 #323269 is a reply to message #323195] |
Wed, 28 May 2008 00:20 |
Frank
Messages: 7901 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 |
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 #405037 is a reply to message #404921] |
Tue, 26 May 2009 02:39 |
Frank
Messages: 7901 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
|
|
|