This is it
create or replace PROCEDURE
truncate_table(nom_de_table in varchar2)
AUTHID DEFINER
is
total INTEGER ;
trouve EXCEPTION ;
cursor_name INTEGER ;
ret INTEGER ;
trun VARCHAR2(30) := 'truncate table ' ||
nom_de_table ;
BEGIN
select 1
into total
from user_tables
where table_name = upper(nom_de_table) ;
Raise TROUVE ;
EXCEPTION
When TROUVE
then
cursor_name := DBMS_SQL.OPEN_CURSOR ;
DBMS_SQL.PARSE(cursor_name, trun, DBMS_SQL.native) ;
ret := DBMS_SQL.EXECUTE(cursor_name) ;
DBMS_SQL.CLOSE_CURSOR(cursor_name) ;
When others
then
raise_application_error(-20011,'*** La table '||nom_de_table||' n a pas d
autorisation ! ***');
END ;
/
-----Message d'origine-----
De: Smith, Ron L. [mailto:[EMAIL PROTECTED]]
Date: vendredi 7 février 2003 15:09
À: Multiple recipients of list ORACLE-L
Objet: RE: Help with a truncate command in a procedure
Thanks for the help!
Ron
-----Original Message-----
Sent: Thursday, February 06, 2003 5:49 PM
To: Multiple recipients of list ORACLE-L
(pant pant)
Will I be the first to say that you need to use dynamic SQL?
dbms_sql package in Oracle version < 8.1
execute immediate in Oracle version >= 8.1
-----Original Message-----
I am not a coder but I received this from one of our developers. I can't
find anything about this anywhere. Can someone tell me how to make the
truncate work?
Thanks!
R.Smith
In a DB procedure, I wanted to include the following SQL:
BEGIN
TRUNCATE TABLE LOT837_GLOBAL_TBL_KMG;
END;
Error messages:
PLS-00103: Encountered the symbol "TABLE" when expecting one of the
following:
:= . ( @ % ;
The symbol ":= was inserted before "TABLE" to continue.
(It does not like it, if you take out TABLE, either.)
So, I had to settle for the following SQL:
BEGIN
DELETE LOT837_GLOBAL_TBL_KMG;
END;
Do you know why I can not use the TRUNCATE command?
If you are not the intended recipient of this e-mail message, any use,
distribution or copying of the message is prohibited. Please let me know
immediately by return e-mail if you have received this message by mistake,
then delete the e-mail message. Thank you.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bernard, Gilbert
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
