errors while Truncating tables from a stored procedure

vihar123
vihar123 used Ask the Experts™
on
Hi,

i tried to use a stored procedure,for truncating tables.
but it throws exceptions
-----------------------------------------------------------------------------
4/10     PLS-00103: Encountered the symbol "TABLE" when expecting one of
         the following:
         := . ( @ % ;
         The symbol ":= was inserted before "TABLE" to continue.

5/5      PLS-00103: Encountered the symbol "=" when expecting one of the
         following:
         constant exception <an identifier>
         <a double-quoted delimited-identifier> table LONG_ double ref
         char time timestamp interval date binary national character
         nchar
-----------------------------------------------------------------------------


stored procedure script:
Create or Replace Procedure Truncate_TBLS_temp
IS
BEGIN
Declare stmt varchar2(2000);
stmt:= 'TEMP_TABLE';
execute immediate 'Truncate table ' || stmt;
END;
/




any idea?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Information Technology Specialist
Commented:
You need to declare stmt as a variable before the begin and without the declare keyword
Create or Replace Procedure Truncate_TBLS_temp
IS
stmt varchar2(2000);
BEGIN
stmt:= 'TEMP_TABLE';
execute immediate 'Truncate table ' || stmt;
END;
/
Just to add to awking00's,
I would declare stmt as a varchar(30) , since a table_name cannot be longer than 30 characters.
awking00Information Technology Specialist

Commented:
If the procedure is as simple as you have expressed, which I suspect it is not, I would further simplify it to something like the following:
Create or Replace Procedure Truncate_TBLS_temp
IS
BEGIN
execute immediate 'Truncate table temp_table';
END;
/
Or, perhaps, modify it to be more generically useful for truncating any table like
Create or Replace Procedure Truncate_TBL(tbl_in in varchar2)
IS
BEGIN
execute immediate 'Truncate table '||tbl_in;
END;
/
Then execute as
exec truncate_tbl('temp_table')
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
hey i dont thing truncate is allowed in the procedure is it ????
Helena Markováprogrammer-analyst

Commented:
Execute_immediate can do it.
This is not allowed:
Truncate table my_table;

This is allowed:
execute_immediate 'Truncate table my_table';

Commented:
Declare stmt varchar2(2000);
should be outside begin
variable declaration is before begin.  awaking is right

Commented:
Hi,

There are two error on this procedure.

1.Variable should be define before BEGIN section.
2. Don't use DECLARE if you are using BEGIN.
3. You miss the ; at the end of variable.

This will work. Try it..

Create or Replace Procedure Truncate_TBLS_temp
IS
stmt varchar2(2000);

BEGIN
stmt:= 'TEMP_TABLE';
execute immediate 'Truncate table ' || stmt;
END;

Good luck.
awking00Information Technology Specialist

Commented:
Anyone else care to repeat everything I said in the beginning? :-)

Commented:
>>Anyone else care to repeat everything I said in the beginning? :-)

yes i do.
awking00Information Technology Specialist

Commented:
Good one, ram_0218. Then your answer will be right, too! LOL

Commented:
awkinq00/ram_0218

I have no intension to copy anyone. I have not seen your answer before I posted my reply. I am sorry , if I  have hurt anyone. Next time I will check all the replis before posting.

Thanks for your comment
Helena Markováprogrammer-analyst

Commented:
I think that points would go to awking00 (Id:19182391).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial