Execute Immediate statement

[vapdba]

I'm looking at some code a colleague wrote. It involves using execute immediate in a procedure to grab the source code of procedures/functions from a second database with a database link. It builds a string "Create or replace..." and then the execute immediate parses and executes the statement to create the proc/func in local database. Here is the problem: the procedure returns an "Insufficient privileges" error when trying to "CREATE" the procedure or function from the code it retrieved with the db link. However, if the object already exists locally, then the procedure using execute immediate to "REPLACE" it completes successfully. Anyone know why it fails to create if it doesn't exist, but succeeds in changing a procedure or function if it already exists? It has something to do with execute immediate - just not sure what!

Thanks.

[akkerend]

If the procedures/functions are not created in the schema of the owner of the procedure that creates them, the owner needs the system privileges CREATE ANY PROCEDURE (to create) and ALTER ANY PROCEDURE (to replace) granted directly (not through a role). Maybe the owner doesn't have the CREATE ANY PROCEDURE privilege.

[vapdba]

That was the problem. The Create/Replace Procedure privileges had been granted through a role rather than directly. Once those system privileges were granted directly to the user the procedure would complete successfully. Thanks for the reply!