Using truncate command inside stored procedures

Hi All,

This is my first post here . Here is my question..

I have created a table in oracle and inserted data into it. Now i created a stored procedure which truncates the table.

Here is the command

TRUNCATE TABLE tablename

I am working on the same schema so i am the owner for all these objects.

I am getting the following error when i try to compile the stored procedure from TOAD.


PLS-00103: Encountered the symbol "TABLE" when expecting one of the following:

:= . ( @ % ;
The symbol ":= was inserted before "TABLE" to continue.


And i am not an expert in oracle, so kindly be as descriptive as possible .

Thanks for your help in advance.

Silverdream

I tried assigning the truncate statement to a string and performed an EXECUTE IMMEDIATE.

and it works

Have another question

I need to copy huge amount of data from table 'a' to table 'b'.
Is there any quick way to do something like this?

Thanks

silverdream

You can copy data from one table to another by using one of following method, I assume that table structure of both tables are identical and thier datatypes as well or atleast should be compatible.

Method 1:
------------
as long as you are not tryinh to copy long or bfile datatypes and the database is same, the following command suppose to work. <schemaname> is optional, if you are copying data of one table to another in the same schema.

SQL>insert into table b (select * from schemaname.a)
/

Method 2:
------------
If your databses are different then first create the databse link to make use <Method 1>, all restriction will be applied.

SQL>create public database link scott.com
2>connect to scott identified by tiger
3>using 'ORCL'
/
If you dont want to make a public database link, omitt the public keyword from the above command.
now use the <method1> as follows:

SQL>insert into table b (select * from a@scott.com)

OR

SQL>COPY FROM scott/tiger@ORCL -
TO scott/tiger@ORCL2 -
REPLACE b -
USING SELECT * FROM a
/
copy command will replace the table if it is already exist and keep the column names and thier data types identical to source table.


Regards,


<copy> command create the table in your schema itself, and keep the datatype of each column identical



SQL>Copy