Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

truncate table command fails

Status
Not open for further replies.

masiwan

Vendor
Feb 20, 2006
37
UG
I have written a procudure where I want to use the truncate table command, in order to permanently delete records from a table; using SQL*PLUS the command executes well but inside the procedure oracle does not even compile and flags the following error: Line # = 16 Column # = 14 Error Text = PLS-00103: Encountered the Symbol "TABLE" when expecting one of the following: := . ( @ % ;

NB.
the truncate command is:
truncate table DESTINATION2
 
as
cursor recs is
SELECT class, stream from CLASSSTREAM;
recs3 recS%ROWTYPE;

begin
TRUNCATE TABLE CLASSSTREAMTABLE;
Insert Into CLASSSTREAMTABLE
(class, stream, outof)
Select class, stream, outof
From classstream
Where class = 'S1'
or class = 'S2';

TRUNCATE TABLE AGGREGATE;
TRUNCATE TABLE DESTINATION2;
TRUNCATE TABLE TEMPTABLE;

INSERT INTO AGGREGATE
(Admno, total, class, Stream)
SELECT TOTALMARKSS1S2.ADMNO,
TOTALMARKSS1S2.totalmark,
TOTALMARKSS1S2.Class,
TOTALMARKSS1S2.STREAM
FROM TOTALMARKSS1S2;

open recs;
Loop
fetch recs
into recs3;
exit when recs%NOTFOUND;

Insert into Temptable
(class, stream, Admno, average, total, outof)
SELECT class, stream, Admno, average, total, outof
from aggregate
where (class = recs3.class) AND (stream = recs3.stream);

POSITION2;
COPYTODESTINATION2;
end loop;
close recs;
WRITEAGGREGATES;

end;
 
Ok, my first guess was that there might be some syntax error in the lines above 'truncate', but I can't see any.

With a bit more thinking about it, it seems that 'truncate' is DDL, and therefore PL/SQL does not like it.
But try this instead:
execute immediate 'TRUNCATE TABLE my_table'

hope this helps;

 
It has not helped however I have another question How do you log on to Oracle Management Server, I'm trying to learn how to back up and I'm told I must log on to (OMS) what Parameters /credantials do I have to use?
 
Hi, masiwan

Try this:

EXECUTE IMMEDIATE('TRUNCATE TABLE my_table') ;

This works in several procedures and packages I have written.

Regards,


William Chadbourne
Oracle DBA
 
thanx William it has compiled let me see if it does what I want I will update U.Uganda greets U
 
You cannot use DDL (create table, drop view, truncate etc.) directly inside of PL/SQL. These require the execute_immediate as shown above.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top