Re: Drop a table via synonym

hpuxrac wrote:
> nicetom786_at_yahoo.com wrote:
> > Thanks for yor reply.
> > As per design,The procedure should drop the table and create the table
> > each time when run.
> >
> > drop table A
> > create table A as select * from table B where 1=2;
> >
> > So a synonym will not help in this case......
> > Pls advise.
> >
>
> Why would you want to drop it and then re-create it?
>
> Do you understand what truncate table will do?
>
> "As per design" is not an answer. It sounds like a bad design to begin
> with. It's time for you to understand how many things you can hose up
> when you drop a table.
>
> I answered your original question ... yes you can write your procedure
> in schema y to do a drop table x.table_name if you grant appropriate
> permissions. But you probably don't want to proceed like that in the
> first place.
>
> Or you can create a procedure in schema x ( drop_my_table ) and have
> procedure in schema x do an " execute x.drop_my_table ".
>
> But you probably don't want to do that either.

In order to drop a table owned by another user from a stored procedure the owner of the procedure should be the table owner or have the drop any table privilege. Dynamic SQL would have to be used to issue the DDL statement from within pl/sql. From SQLPlus only the proper privileges are necessary.

Hpuxrac is right to question the design. Generally if only one job will be updating the table that multiple users will be accessing then permanent tables should be used and a truncate issued as part of the reload job. If there are multiple updaters then the design is even more suspect.

HTH -- Mark D Powell -- Received on Fri Dec 01 2006 - 01:44:19 CET