0

I created a view in oracle toad using a select statement, but I need the select statement to accept a date parameter because I need the columns in the view to be sums over a date range.

Is that possible?

user1854438
  • 1,784
  • 6
  • 24
  • 30
  • A view is basically a canned query. What should this view as a whole contain if two users are accessing it with two different date parameters? – Michael Broughton Oct 27 '15 at 16:15
  • Just add a `where` clause when selecting from the view `select * from view where date_column = date '2015-10-27'` –  Oct 27 '15 at 16:15
  • Looks like he wants the column definitions to change based on the date - not just a simple filter. – Michael Broughton Oct 27 '15 at 16:17

1 Answers1

0

A view is a canned query that cannot be changed on the fly and does not accept parameters. You can "SELECT FROM myView WHERE..." to filter results, but that doesn't dynamically re-configure what is defined inside a column. So if the view can't be dynamic - it has to reference something else that can. Like, say, the contents of a table.... or package.

For example, have a package that holds the parameter with a public functions to set it and get it. Your view then includes a call to the package getter function to retrieve this value and use it in the query. Because packages instantiate once per user session each user can have a different value in their instance of the package, and so also into their own instance of the view.

Altenately, you could have a global temporary table referenced by the view that you pop the parameter into so the view can see it at runtime. Again, this allows each user to set their value within their session.

Of course, it means you have to go through certain steps for the view to work, and you have to have the view properly handle the cases where the user didn't set a value at all - or in the global temporary table accidentally put in TWO rows.

But it CAN be done.

Michael Broughton
  • 4,045
  • 14
  • 12