asked on

errors while Truncating tables from a stored procedure

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?

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

paquicuba

Just to add to awking00's,
I would declare stmt as a varchar(30) , since a table_name cannot be longer than 30 characters.

awking00

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')

asaeed

hey i dont thing truncate is allowed in the procedure is it ????

Helena Marková

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

This is allowed:
execute_immediate 'Truncate table my_table';

asaeed

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

sahmad00

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.

awking00

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

ram_0218

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

yes i do.

awking00

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

sahmad00

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á

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