OracleBlog

I was recently asked by a former colleague "Can you insert data in an Oracle table through a View?"

In some cases, yes you can, and it actually serves as a handy technique to manage security on your data. Here's what I mean: you can restrict access to a base table, and then create a view containing only rows and columns that you wish to be accessible. That is just one of the many handy uses of views. Quick hint: read up on the WITH CHECK OPTION to prevent changes to base table rows to which you're trying to restrict access.

Now why did I say "in some cases?" Because inserting/updating data in a View depends on the View. Why? Well a View is just a stored query against some base tables. When you're executing DML operations on a View, Oracle actually executes those DML operations appropriately on the base tables. And there are some cases where it is impossible for Oracle to do that. Let's look at one simple example.

-- Create our data
CREATE TABLE BaseTable (field1 NUMBER(8), field2 NUMBER(8));
INSERT INTO BaseTable (field1, field2) VALUES (5, 10);
CREATE OR REPLACE VIEW UpdateViewYes 
AS SELECT field1, field2 FROM BaseTable;
CREATE OR REPLACE VIEW UpdateViewNo 
AS SELECT field1 + field2 addfield FROM BaseTable;

-- This won't work
SQL> UPDATE UpdateViewNo SET addfield = 16;
UPDATE UpdateViewNo SET addfield = 16
                        *
ERROR at line 1:
ORA-01733: virtual column not allowed here

-- This should work
UPDATE UpdateViewYes SET field1 = 6 WHERE field1 = 5;
SQL> UPDATE UpdateViewYes SET field1 = 6 WHERE field1 = 5;

1 row updated.

-- Look at our data now
SQL> SELECT * FROM BaseTable;

    FIELD1     FIELD2
---------- ----------
         6         10

In this example, UpdateViewNo.addfield is the addition of two columns. How would Oracle know how to divide the new value we assigned? This argument would hold for any situation where the View had a pseudocolumn or expression column, like this example, or perhaps one created through the use of DECODE. Of course, these rows could still be deleted.

The Oracle SQL Reference details other situations where your View couldn't be updated. For example, and along the same lines of reasoning, if the View contains a SET, DISTINCT, GROUP BY, GROUP, ORDER BY, CONNECT BY, START WITH, then Oracle can't perform any update or insert operations on the View. Same goes if you have a collection expression or a subquery in a SELECT list.

USER_UPDATABALE_COLUMNS

Not sure if your View can be updated or not? Well you can simply try and see, but there is another way. Find your View in the USER_UPDATABLE_COLUMNS table and see for yourself.

SQL> SELECT table_name, column_name, updatable
  2  FROM user_updatable_columns
  3  WHERE table_name LIKE 'UPDATEVIEW%';

TABLE_NAME                COLUMN_NAME               UPD
------------------------- ------------------------- ---
UPDATEVIEWNO              ADDFIELD                  NO
UPDATEVIEWYES             FIELD1                    YES
UPDATEVIEWYES             FIELD2                    YES

WITH READ ONLY

What if you don't want anyone to update obase tables using your View? You can explicitly prevent users from modifying the base table through your View by creating it with the WITH READ ONLY clause. That signals to Oracle that your View is meant for querying only.

Join Views

Thus far I've been talking only about cases where the View is based on a single base table. What about when the View joins one or more base tables? This is referred to as a Join View.

Updating join views is a whole different ball of wax. Simply put, you may be able to update one base table through the view, provided your join clause uses a unique index. Otherwise, there are workarounds. To learn more on this, read Norman Dunbar's article in Jonathan Lewis' Oracle User's Co-Operative FAQ.

Briefly put, you can create a update trigger on that view to update the data in the base tables appropriately. There is also an example on Building Complex Updatable Views Using Triggers in the Application Developer's Guide, Chapter 15.

For more information on Views, including a list of what disqualifies a view from being updateable, a description of Join Views, details of the WITH READ ONLY clause, examples and much more, consult the Oracle SQL Reference, Chapter 16.

Pivot queries (also known as crosstab queries) are a special kind of query that can turn rows into columns for special reporting needs. For example, if you have a table/view with three columns (employee name, year, and salary), and you need a view that shows employee name and salary over the years (as a single row), then a pivot query is what you want. Essentially, you want to "pivot" the year column from being rows to being a column.

I presented a simple, introductory example last September that used Canadian Football League teams and their points per season as an example. That example worked fine at the time, but what happened at the end of the CFL season? You entered some new rows of data to reflect the results of the 2005 season, but the query is hard-coded to show only 2002-2004 seasons. What should you do now? Re-write your query after every season? Fortunately, there is a better way. That is why today I would like to expand on this example to show you how to write a pivot query in the common case where you have an undefined number of columns (in this case, seasons/years).

First of all, let's add the 2005 season to our data. Also remember why I like to name my columns when I insert (or call stored procedures):
INSERT INTO CFL (season, team, points) VALUES (2005, 'Argonauts', 22);
INSERT INTO CFL (season, team, points) VALUES (2005, 'Alouettes', 20);
INSERT INTO CFL (season, team, points) VALUES (2005, 'Renegades', 14);
INSERT INTO CFL (season, team, points) VALUES (2005, 'Tiger-Cats', 10);

Our problem is that we don't know how many columns we need when we write the query. We only know how many columns we'll need at the time the query is executed. That sounds like a job for dynamic SQL which, along with REF CURSORs, can build an appropriate query at the time we call it. That technique is described in detail by Tom Kyte in his book Expert One-on-One Oracle, in Chapter 12 on Analytic Functions. I will apply this technique to our specific problem in an upcoming article.

Tom describes another way to get around this issue on his famous AskTom website. You can leave the seasons as rows (allowing you to have as few or as many as you need), and pivot the non-season columns instead. His solution leads us to another way of tackling this "I don't know how many columns I'll need" problem. Make season/points a single column.

Solution #1: Leave Seasons as rows and pivot the Team column.

CREATE OR REPLACE TYPE Teams AS OBJECT
(team VARCHAR2(16), points NUMBER(3));

CREATE OR REPLACE TYPE Seasons AS TABLE OF Teams;

SELECT c1.season,
CAST(MULTISET(SELECT c2.team, sum(c2.points) tot_points
FROM CFL c2 WHERE c1.season = c2.season
GROUP BY c2.team) AS Seasons) Standings
FROM CFL c1
GROUP BY season;

SEASON
----------
STANDINGS(TEAM, POINTS)
--------------------------------------------------------------------------------
2002
SEASONS(TEAMS('Alouettes', 27), TEAMS('Argonauts', 16), TEAMS('Renegades', 10), TEAMS('Tiger-Cats', 15))

2003
SEASONS(TEAMS('Alouettes', 26), TEAMS('Argonauts', 18), TEAMS('Renegades', 14), TEAMS('Tiger-Cats', 2))

2004

SEASON
----------
STANDINGS(TEAM, POINTS)
--------------------------------------------------------------------------------
SEASONS(TEAMS('Alouettes', 28), TEAMS('Argonauts', 21), TEAMS('Renegades', 10), TEAMS('Tiger-Cats', 19))

2005
SEASONS(TEAMS('Alouettes', 20), TEAMS('Argonauts', 22), TEAMS('Renegades', 14), TEAMS('Tiger-Cats', 10))

Solution #2: Pivot Seasons, just like we did before, and create an array for seasons and points.

CREATE OR REPLACE TYPE SeasonResult AS OBJECT
(season NUMBER(4), points NUMBER(3));

CREATE OR REPLACE TYPE TeamResult AS TABLE OF SeasonResult;

SELECT c1.team,
CAST(MULTISET(SELECT c2.season, sum(c2.points) tot_points
FROM CFL c2 WHERE c1.team = c2.team
GROUP BY c2.season) AS TeamResult) Results
FROM CFL c1
GROUP BY team;

TEAM
----------------
RESULTS(SEASON, POINTS)
--------------------------------------------------------------------------------
Alouettes
TEAMRESULT(SEASONRESULT(2002, 27), SEASONRESULT(2003, 26), SEASONRESULT(2004, 28), SEASONRESULT(2005, 20))

Argonauts
TEAMRESULT(SEASONRESULT(2002, 16), SEASONRESULT(2003, 18), SEASONRESULT(2004, 21), SEASONRESULT(2005, 22))

Renegades

TEAM
----------------
RESULTS(SEASON, POINTS)
--------------------------------------------------------------------------------
TEAMRESULT(SEASONRESULT(2002, 10), SEASONRESULT(2003, 14), SEASONRESULT(2004, 10), SEASONRESULT(2005, 14))

Tiger-Cats
TEAMRESULT(SEASONRESULT(2002, 15), SEASONRESULT(2003, 2), SEASONRESULT(2004, 19), SEASONRESULT(2005, 10))

There are two consequences of this approach that are less than ideal. The query itself is a little bit complicated, involves creating types and, secondly, if we made a view from this query, its harder to use. (Example: Query that view to get me the season record for each team). Fortunately there is a simple change we can make that gives us a query as simple as our original example, and where we can create a view that can be more easily used for other queries.

Before I tell you what it is, observe one thing about Tom's solution (#1). In the simpler example where we knew how many columns (seasons) we'd need, we pivoted the seasons. In this example, where we didn't know how many seasons we had, we pivoted the other columns (team, points).

We are in the fortunate position where we know how many teams we have. Why not pivot "team" column, and then do it largely like our simpler example?

CREATE OR REPLACE VIEW CFLBySeason AS
SELECT season,
MAX(DECODE(team, 'Argonauts', points, NULL)) Argonauts,
MAX(DECODE(team, 'Alouettes', points, NULL)) Alouettes,
MAX(DECODE(team, 'Renegades', points, NULL)) Renegades,
MAX(DECODE(team, 'Tiger-Cats', points, NULL)) TigerCats
FROM CFL
GROUP BY season;

SELECT * FROM CFLBySeason;

    SEASON  ARGONAUTS  ALOUETTES  RENEGADES  TIGERCATS
---------- ---------- ---------- ---------- ----------
      2002         16         27         10         15
      2003         18         26         14          2
      2004         21         28         10         19
      2005         22         20         14         10

Not only do we have a simpler query, but we can leverage the power of views to keep things simple. By turning this query into a view (called, for example, CFLBySeason), we can address other requirements very easily.

Example:
1. Show me the most points any team has had in any one season. And it would be simple enough to change this example to show total and/or average season performance as well.

SELECT MAX(Argonauts) Argonauts,
MAX(Alouettes) Alouettes,
MAX(Renegades) Renegades,
MAX(TigerCats) TigerCats
FROM CFLBySeason;

2. Show me how lopsided the division was each season

SELECT Season,
(Argonauts + Alouettes) TorMtl,
(Renegades + TigerCats) OttHam
FROM CFLBySeason;

3. I only care about Toronto. I want to see their season-by-season point totals.

SELECT Season, Argonauts FROM CFLBySeason;

I will admit that pivot queries can get complicated, but given how often they are the easiest solution to complex requirements, it is worth the investment to understand them.