Title: Message
Thanks
for the help!
Ron
-Original Message-From: Jacques Kilchoer
[mailto:[EMAIL PROTECTED]] Sent: Thursday, February 06,
2003 5:49 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: Help with a truncate command in a
procedure
(pant pant) Will I
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
Title: Help with a truncate command in a procedure
Truncate is a DDL. So you can't just do
"truncate" like you would with delete which is a DML.
You
didn't say which version of Oracle you are running. If it's 8i,
do
execute immediate "truncate table
.";
Otherwise, look into
Title: RE: Help with a truncate command in a procedure
(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-
From: Smith, Ron L. [mailto:[EMAIL PROTECTED]]
I am
Look up info on using execute immediate
This will let you put non-DML-type statements in a PL/SQL block.
-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?
truncate is a sqlplus command(and DDL), so to do it, you need to use the
dbms_sql(or execute immediate) command, i think.
joe
Smith, Ron L. wrote:
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
Ron,
Trunacte is DDL not DML. The easiest was around this is to wrap it in an
execute immediate command:
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE LOT837_GLOBAL_TBL_KMG';
END;
/
Note that the semi-colon is outside the quotes - don't place a semi-colon
inside the quotes.
Regards,
Mark.
