Re: Create database link from inside a stored procedure

In article <1143565857.296801_at_yasure.drizzle.com>, DA Morgan says...

>
> The system privilege to create a database link is granted as follows:
>
> GRANT create database link TO <schema_name>;
>

Thanks - I was under the impression that the explicit grant was already present - it was only after posting this that I did check that - apologies.

> However doing this within a stored procedure is a terrible idea by
> almost any definition. What is the business case that justifies
> such an activity in PL/SQL?
>

I am not surprised that the justification behind the request has been sought :)

It is to do with a kind of "meta data" that may be assembled in a test environment and that we then want to be able to deploy to another database using PL/SQL and a web interface. As the target database may not be known at the time of compile (it won't be), we will use dynamic SQL to create the database link and to then copy the relevant rows to the target database.

I am assuming you will now find all sorts of reasons why this simply should not be done this way. I'm listening .... ;)

-- 

jeremy