truncate table command fails

[masiwan]

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

 

[hoinz]

Can you show a few more lines of your procedure?

 

[masiwan]

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;

 

[hoinz]

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;

 

[masiwan]

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?

 

[oradba101]

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

 

[masiwan]

thanx William it has compiled let me see if it does what I want I will update U.Uganda greets U

 

[BJCooperIT]

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