codemonkey2480
asked on
Truncate tables in Other Schema Oracle Stored Procedure
I have a stored procedure and would like to truncate another table from different schema from the current schema (of stored proc).
How do I do this?
How do I do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>> If you already have been given explicit permission to truncate the other schema's table
there is no "truncate table" permission that can be granted
the closest you can get is "drop any table" which is NOT recommended
also, small mistake above, I left out the key word "PROCEDURE"
create or replace procedure trunc_table(p_tablename in varchar2)
is
begin
execute immediate 'truncate table ' || p_tablename;
end;
there is no "truncate table" permission that can be granted
the closest you can get is "drop any table" which is NOT recommended
also, small mistake above, I left out the key word "PROCEDURE"
create or replace procedure trunc_table(p_tablename in varchar2)
is
begin
execute immediate 'truncate table ' || p_tablename;
end;
Wasn't implying a 'truncate table' permission. Just stating that if they already had permission to issue a truncate command...
If you already have been given explicit permission to truncate the other schema's table, just add the schema to the truncate command:
execute immediate 'truncate table schema.table';