Re: Truncating Within Procedures

In article <6vk7n7$49i$1_at_nnrp1.dejanews.com>,   jflipse_at_spacestar.com wrote:
> I am able to truncate tables within a stored procedure by passing the
> table name (mytable) to a procedure which performs the truncate
> :
> :
> I have no problems when the table name is explicitly called out, but
> run into problems when the table name is a synonym. I've tried to
> bind the synonym to a variable and pass this to the procedure...

John,

the basic problem is that you can not use "truncate table" on a synonym. To get around this you could always convert the synonym name into the actual table name by checking ALL_SYNONYMS. Bear in mind that there may be further complications if the synonym "points" to another synonym, or a view, or a table that is owned by another user.

If the truncate is being done in a stored procedure you will also have to ensure that the owner of the procedure has the required privs to truncate the table. Unless the table owned by the same user, these need to be granted directly rather than through a role.

--
Regards
Yuri McPhedran

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Oct 09 1998 - 07:57:14 CDT