calling dbms_redefinition via a stored procedure.

Hi,

I'm trying to create a stored procedure function which is a wrapper to the dbms_redefintion function.

I have allocated EXECUTE ON DBMS_REDEFINITION and DBA rights

if I create a script that grants these rights, and logs on as the user I grant the rights to, and then call the dbms_redefintion.start_redef_table this works ok.

If I create a stored procedure eg ESP_ConvertTable which takes three parameters
p_OldTable, p_NewTable, and p_ColumnDefintion which I pass to the dbms_redefinition functions, and then call exactly the same script as previously but making a call to the stored procedure instead I get an insufficient privilges error.

I have read/seen things about invoker rights - does calling a stored procedure from a stored procedure pass through the privileges of the stored procedures caller/owner?

Any advice would be greatly appreciated. Having to use the dbms_redefintion as we're moving to unicode converting clobs to nclobs in Oracle 9iR2 which means a insert into... doesn't work (it does in 10g)...

Kind Regards

Rich Received on Sun Apr 09 2006 - 12:24:31 CDT