Search code examples
dynamicplsqlvieworacle12cexecute-immediate

Creating a database view from a dynamic string (EXECUTE IMMEDIATE) in a PL/SQL package - Questions?

I want to create a dynamic view at runtime made up of string of columns and a where clause using EXECUTE IMMEDIATE on one database which will be queried on a second database using a db_link.

My question are the following.

  1. The view will be queried on another database using a database_link do I need to also GRANT privileges to the view (i.e. PUBLIC) and the SYNONYM (as PUBLIC) at the same time (if at all)? or does this only need to be created once?

  2. Can a package be INVALID if in the PL/SQL package there is a reference to an object on another database via a database link that doesn't exist, is INVALID or has changed in structure? Or does it compile regardless?

  3. I'm assuming I would need "CREATE OR REPLACE VIEW" in the the EXECUTE IMMEDIATE string as the second time I run this process the view will already exist on the database?

Thanks Guys in advance for any feedback on this.

Solution

  • First of all, I'd suggest you not to do that. In Oracle, objects are created once and used any time you want. What benefit do you expect from creating a view dynamically? (I'm not saying that you must not do it, just suggesting to think it over).

    Now, to answer your questions:

    1. You don't need GRANT because - in order to create a database link, you already know remote database's username and password
    2. If object in another database is invalid, then executing or compiling your procedure will fail
    3. Yes, as without or replace Oracle will complain that object with that name already exists.