0

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.

Shaun Kinnair
  • 495
  • 2
  • 10
  • 27
  • as long as you keep the same columns in your dynamic view you can propably use pipelined table functions to achive your goal – MarEll Feb 04 '20 at 12:15
  • MarEll when you say same columns, do you mean same number of columns or same datatype. Currently the dynamic cursor that I will use as a view will use either two base tables (for one type of run) or four base tables for another type of run. There are 10 columns for one type of run and 11 columns for another type. This run only happens every 3 months. – Shaun Kinnair Feb 04 '20 at 13:47
  • 1
    You do not need a dynamic view. You have 2DBs, one with the view (Source) and one requesting data over a link (Destination). Now the Destination must know which view format is needed and it must process the results differently it also must indicate to Source which to build. So use this knowledge and create 2 permanent views in Source. The Destination then just identifies which view it wants and sends the specific request over the same db link. Making the code of both DBs simpler. – Belayer Feb 04 '20 at 17:14
  • Could you put together a short example of what you expect to have deployed where? Perhaps with "Database A" and "Database B" and what is where. With regards to question 1, if a view is to be accessed over a DB link, the DB link user must have access to the view, either by granting to PUBLIC or granting to the user that the DB link connects as. – Tad Harrison Feb 05 '20 at 18:16

1 Answers1

0

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.
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • A little back ground to why I need a dynamic view. Currently a program runs that uses a dynamic created cursor which then processes the data in that dynamic cursor into flat files which are then moved to a directory where the second database can load them into there system. We want to get rid of the flat file and create a dynamic view that can be access using a db_link to get at the data. – Shaun Kinnair Feb 04 '20 at 13:41
  • Thanks for the feedback up to now Littlefoot – Shaun Kinnair Feb 04 '20 at 13:48