This discussion is archived
1 2 3 5 Previous Next 66 Replies Latest reply: Mar 15, 2013 7:34 AM by BluShadow RSS

NOT NULL

894936 Newbie
Currently Being Moderated
I am creating a view which picks column from my master table.
so i want to make the solumn in view as " NOT NULL"
how do we create them.
CREATE OR REPLACE FORCE VIEW TEST
(
   BU_CODE ,
   BU_TYPE
  )
AS
     SELECT DISTINCT
            CAST (A.STO_NO AS VARCHAR2 (5 CHAR) )  AS BU_CODE, ------I Want to add "NOT NULL" 
            CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE       -----------------I Want this column to be " NOT NULL"
FROM TABLE A
  • 1. Re: NOT NULL
    jeneesh Guru
    Currently Being Moderated
    You have to make it in the base table as View is merely a stored SELECT statement..

    And BU_TYPE is already "NOT NULL" coz it is a constant value..

    And use NVL on BU_CODE to make it "not null"

    Edited by: jeneesh on Feb 18, 2013 2:06 PM
  • 2. Re: NOT NULL
    Rahul_India Journeyer
    Currently Being Moderated
    alter table A modify
    (
    STO_NO varchar2(100) not null
    )


    typo
    edited in response to jeenesh
  • 3. Re: NOT NULL
    BluShadow Guru Moderator
    Currently Being Moderated
    891933 wrote:
    I am creating a view which picks column from my master table.
    so i want to make the solumn in view as " NOT NULL"
    how do we create them.
    CREATE OR REPLACE FORCE VIEW TEST
    (
    BU_CODE ,
    BU_TYPE
    )
    AS
    SELECT DISTINCT
    CAST (A.STO_NO AS VARCHAR2 (5 CHAR) )  AS BU_CODE, ------I Want to add "NOT NULL" 
    CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE       -----------------I Want this column to be " NOT NULL"
    FROM TABLE A
    A view is an SQL query.
    Specifying NOT NULL for a projected column of an SQL query doesn't make sense, as the NOT NULL clause relates to columns on tables, not SQL projections, as it's purpose is to contrain the column when data is inserted. Yes, Oracle does permit inserts on views (with conditions), but those propagate down to the base table and the data is inserted there, so any contraints you require on such columns are required on the base table, not on the view itself.
  • 4. Re: NOT NULL
    jeneesh Guru
    Currently Being Moderated
    Rahul India wrote:
    alter table A modify column
    (
    STO_NO varchar2(100) not null
    )
    COLUMN key word is not required...
    alter table A modify 
    (
    STO_NO varchar2(100) not null
    );
  • 5. Re: NOT NULL
    sql_coder Newbie
    Currently Being Moderated
    BluShadow wrote:
    A view is an SQL query.
    Specifying NOT NULL for a projected column of an SQL query doesn't make sense, as the NOT NULL clause relates to columns on tables, not SQL projections, as it's purpose is to contrain the column when data is inserted. Yes, Oracle does permit inserts on views (with conditions), but those propagate down to the base table and the data is inserted there, so any contraints you require on such columns are required on the base table, not on the view itself.
    I would suggest to interpret a view like a virtual table rather then a stored sql query. And you could use WITH CHECK OPTION in the view definition, so you can exclude NULL values without changing the base table.

    Ikrischer
  • 6. Re: NOT NULL
    APC Oracle ACE
    Currently Being Moderated
    Ikrischer wrote:
    you could use WITH CHECK OPTION in the view definition, so you can exclude NULL values without changing the base table.
    In order for this to work the view would have to include a WHERE clause specifying the mandatory column(s). Something like ...
    CREATE OR REPLACE FORCE VIEW TEST
    (
       BU_CODE ,
       BU_TYPE
      )
    AS
         SELECT DISTINCT
                CAST (A.STO_NO AS VARCHAR2 (5 CHAR) )  AS BU_CODE,  
                CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE       
    FROM TABLE A
    where A.STO_NO is not null
    with check option
    Without the sub-query the WITH CHECK OPTION will not enforce anything.

    Of course the whole point is moot in the case of this view because of that DISTINCT. Any attempt to insert a row through the view will hurl ORA-01732 (data manipulation is not leagl on this view). For inserts to work we would need to create an INSTEAD OF trigger and then the WITH CHECK OPTION constraint is bypassed. [url http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8004.htm#i2117836]Find out more.
  • 7. Re: NOT NULL
    BluShadow Guru Moderator
    Currently Being Moderated
    Ikrischer wrote:
    BluShadow wrote:
    A view is an SQL query.
    Specifying NOT NULL for a projected column of an SQL query doesn't make sense, as the NOT NULL clause relates to columns on tables, not SQL projections, as it's purpose is to contrain the column when data is inserted. Yes, Oracle does permit inserts on views (with conditions), but those propagate down to the base table and the data is inserted there, so any contraints you require on such columns are required on the base table, not on the view itself.
    I would suggest to interpret a view like a virtual table rather then a stored sql query. And you could use WITH CHECK OPTION in the view definition, so you can exclude NULL values without changing the base table.
    I wouldn't, otherwise people who don't know better think they can do things with it that they can't.
    A materialized view on the other hand... I would treat like a virtual table, as it does actually store data... whereas a view doesn't. Matter of personal opinion really, but I prefer not to complicate things, especially for newbies who really don't understand even the basics of views.
  • 8. Re: NOT NULL
    sql_coder Newbie
    Currently Being Moderated
    BluShadow wrote:
    Ikrischer wrote:
    BluShadow wrote:
    A view is an SQL query.
    Specifying NOT NULL for a projected column of an SQL query doesn't make sense, as the NOT NULL clause relates to columns on tables, not SQL projections, as it's purpose is to contrain the column when data is inserted. Yes, Oracle does permit inserts on views (with conditions), but those propagate down to the base table and the data is inserted there, so any contraints you require on such columns are required on the base table, not on the view itself.
    I would suggest to interpret a view like a virtual table rather then a stored sql query. And you could use WITH CHECK OPTION in the view definition, so you can exclude NULL values without changing the base table.
    I wouldn't, otherwise people who don't know better think they can do things with it that they can't.
    A materialized view on the other hand... I would treat like a virtual table, as it does actually store data... whereas a view doesn't. Matter of personal opinion really, but I prefer not to complicate things, especially for newbies who really don't understand even the basics of views.
    I agree not to complicate things, but I can remember my first time in contact with views. They told me the same (view is a stored statement) and I was more confused with that concept, specially when I started to work with them. Do not laugh, but I really tried this out:

    view1 UNION ALL view2;

    Specially when it came to DML statements using a view it broke my mind. I find it way more easier to handle a view like what it is, a virtual table based on records of a sql statement, then I have a good guess how and where I can bring in my views in a sql statement. What you want to tell a more advanced sql user, the same that a view is a stored sql statement ?

    Ikrischer
  • 9. Re: NOT NULL
    BluShadow Guru Moderator
    Currently Being Moderated
    Ikrischer wrote:
    BluShadow wrote:
    Ikrischer wrote:
    BluShadow wrote:
    A view is an SQL query.
    Specifying NOT NULL for a projected column of an SQL query doesn't make sense, as the NOT NULL clause relates to columns on tables, not SQL projections, as it's purpose is to contrain the column when data is inserted. Yes, Oracle does permit inserts on views (with conditions), but those propagate down to the base table and the data is inserted there, so any contraints you require on such columns are required on the base table, not on the view itself.
    I would suggest to interpret a view like a virtual table rather then a stored sql query. And you could use WITH CHECK OPTION in the view definition, so you can exclude NULL values without changing the base table.
    I wouldn't, otherwise people who don't know better think they can do things with it that they can't.
    A materialized view on the other hand... I would treat like a virtual table, as it does actually store data... whereas a view doesn't. Matter of personal opinion really, but I prefer not to complicate things, especially for newbies who really don't understand even the basics of views.
    I agree not to complicate things, but I can remember my first time in contact with views. They told me the same (view is a stored statement) and I was more confused with that concept, specially when I started to work with them. Do not laugh, but I really tried this out:
    Well, on Ingres databases, which is what I first started on, Views are stored data, like a virtual table, and when you insert, update or delete the base tables then the view gets updated automatically (a bit like a materialized view with an automatic real time refresh). So, coming to Oracle, finding that it had two distictly different types of views... materialized and regular... made a refreshing change. Didn't take too much effort to understand a regular view is a stored select statement.
    view1 UNION ALL view2;
    Surely that as just from a lack of understanding of SQL, and I'm sure you figured it out quickly enough.
    Specially when it came to DML statements using a view it broke my mind.
    Not sure how considering it a virtual table would help in that case, as you'd soon learn about INSTEAD OF triggers and the way that the base table is the one that gets updated.
    I find it way more easier to handle a view like what it is, a virtual table based on records of a sql statement, then I have a good guess how and where I can bring in my views in a sql statement. What you want to tell a more advanced sql user, the same that a view is a stored sql statement ?
    If they're a more advanced sql user, they'll know what it is. ;)
    The problem can also be seen the other way... if you consider it a virtual table, then when you do DML on the base table, what mechanism is there that is magically doing the DML against the virtual table too? ... answer... there isn't because the virtual table isn't storing any data. Me personally, I think it's wrong to consider it as any sort of table, but whatever works for you is your choice.
  • 10. Re: NOT NULL
    sql_coder Newbie
    Currently Being Moderated
    BluShadow wrote:
    Ikrischer wrote:
    BluShadow wrote:
    Ikrischer wrote:
    BluShadow wrote:
    A view is an SQL query.
    Specifying NOT NULL for a projected column of an SQL query doesn't make sense, as the NOT NULL clause relates to columns on tables, not SQL projections, as it's purpose is to contrain the column when data is inserted. Yes, Oracle does permit inserts on views (with conditions), but those propagate down to the base table and the data is inserted there, so any contraints you require on such columns are required on the base table, not on the view itself.
    I would suggest to interpret a view like a virtual table rather then a stored sql query. And you could use WITH CHECK OPTION in the view definition, so you can exclude NULL values without changing the base table.
    I wouldn't, otherwise people who don't know better think they can do things with it that they can't.
    A materialized view on the other hand... I would treat like a virtual table, as it does actually store data... whereas a view doesn't. Matter of personal opinion really, but I prefer not to complicate things, especially for newbies who really don't understand even the basics of views.
    I agree not to complicate things, but I can remember my first time in contact with views. They told me the same (view is a stored statement) and I was more confused with that concept, specially when I started to work with them. Do not laugh, but I really tried this out:
    Well, on Ingres databases, which is what I first started on, Views are stored data, like a virtual table, and when you insert, update or delete the base tables then the view gets updated automatically (a bit like a materialized view with an automatic real time refresh). So, coming to Oracle, finding that it had two distictly different types of views... materialized and regular... made a refreshing change. Didn't take too much effort to understand a regular view is a stored select statement.
    this is wrong, a view is not a stored select statement, a view is a virtual table defined by a select statement. It is arguable if you want to simplifiy the concept of a view teaching that persons new to sql, but it is still wrong. And if you ask me, you would confuse more then help.
    The problem can also be seen the other way... if you consider it a virtual table, then when you do DML on the base table, what mechanism is there that is magically doing the DML against the virtual table too? ... answer... there isn't because the virtual table isn't storing any data. Me personally, I think it's wrong to consider it as any sort of table, but whatever works for you is your choice.
    Why is that a problem for you ? As you already mentioned yourself, there are two types of views in Oracle and regular views does not store their logical records, but get them on the fly from the base table(s) defined by the select statement. So it is no surprise, that when the base tables are changed, the view changed as well. And the other way, when you update the logical table, you change the base tables. A materialized view is the same, when you change the base(master) tables sooner or later the materialized view will be changed automatically, just that those records are stored physical and are not logical. if you think this works only for me, check out Oracle and Wikipedia.

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8004.htm
    http://en.wikipedia.org/wiki/View_%28database%29

    Ikrischer
  • 11. Re: NOT NULL
    BluShadow Guru Moderator
    Currently Being Moderated
    Ikrischer wrote:
    this is wrong, a view is not a stored select statement, a view is a virtual table defined by a select statement. It is arguable if you want to simplifiy the concept of a view teaching that persons new to sql, but it is still wrong. And if you ask me, you would confuse more then help.


    As you already mentioned yourself, there are two types of views in Oracle and regular views does not store their logical records, but get them on the fly from the base table(s) defined by the select statement.
    If it's not storing the select statement, how can it be getting data on the fly from base tables based on a select statement? You are contradicting yourself.
    When a view is created, the select statement is stored in the data dictionary, and it is that select statement that is used when you query from the view, and the optimizer can combine the stored select statement with queries that use it, just as it would optimize other sub queries in the main select statement.

    Look for yourself in the data dictionary...
    SQL> create view myemps as select * from emp;
    
    View created.
    
    SQL> select view_name, text from user_views where view_name = 'MYEMPS';
    
    VIEW_NAME                      TEXT
    ------------------------------ --------------------------------------------------------------------------------
    MYEMPS                         select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from emp
    Sure looks like it's stored the query to me.

    And if I query from that...
    SQL> select * from myemps;
    
         EMPNO ENAME                          JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ------------------------------ --------- ---------- -------------------- ---------- ---------- ----------
          7369 SMITH                          CLERK           7902 17-DEC-1980 00:00:00        800            20
          7499 ALLEN                          SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
          7521 WARD                           SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30
          7566 JONES                          MANAGER         7839 02-APR-1981 00:00:00       2975            20
          7654 MARTIN                         SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
          7698 BLAKE                          MANAGER         7839 01-MAY-1981 00:00:00       2850            30
          7782 CLARK                          MANAGER         7839 09-JUN-1981 00:00:00       2450            10
          7788 SCOTT                          ANALYST         7566 19-APR-1987 00:00:00       3000            20
          7839 KING                           PRESIDENT            17-NOV-1981 00:00:00       5000            10
          7844 TURNER                         SALESMAN        7698 08-SEP-1981 00:00:00       1500       0    30
          7876 ADAMS                          CLERK           7788 23-MAY-1987 00:00:00       1100            20
          7900 JAMES                          CLERK           7698 03-DEC-1981 00:00:00        950            30
          7902 FORD                           ANALYST         7566 03-DEC-1981 00:00:00       3000            20
          7934 MILLER                         CLERK           7782 23-JAN-1982 00:00:00       1300            10
    
    14 rows selected.
    
    Elapsed: 00:00:00.10
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3956160932
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    Well lookeee here... the optimizer has combined the views stored query with my query to make it a direct query on the emp table. The plan says nothing about selecting from the view as some 'virtual table'. If it was considered a virtual table, it would have no choice but to include it in the plan like other tables, but because it's a select statement it can be combined and optimized.
    So it is no surprise, that when the base tables are changed, the view changed as well. And the other way, when you update the logical table, you change the base tables.
    That's a special feature of views in Oracle, and it has it's limitations. If the view contains aggregated data or distinct data you wouldn't be able to update through it, but if it were considered a virtual table, then one could easily be misled into believing you should be able to update it regardless.
    A materialized view is the same, when you change the base(master) tables sooner or later the materialized view will be changed automatically
    Really? Some of them need a manual refresh depending on how they are configured.
    just that those records are stored physical and are not logical.
    Yes, a materialized view is just like a normal view, it stores the select statement and a physical copy of the data that select statement produces at a point in time (hence why Oracle used to call them snapshots)
    if you think this works only for me, check out Oracle and Wikipedia.

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8004.htm
    The documentation doesn't mention any "virtual" tables. It talks of a logical table. Logically, the view can act as a table, but it is not virtually a table at all. A table has a physical storage structure and stored data on it.
    http://en.wikipedia.org/wiki/View_%28database%29
    I wouldn't trust Wiki as far as I could throw it, and it proves nothing..

    >
    In database theory, a view consists of a query accessible as a virtual[clarify] table
    >

    So, as it says, a view consists of a query (the stored query)
    and the mention of "virtual" has be marked as requiring clarification, because it's innacurate.

    The only thing I would class as anywhere near a virtual table is a materialized view, as it is mostly like a table, able to have indexes on it etc. but cannot directly have the data manipulated on it with DML statements, so it is virtually a table, but not quite there. A normal view on the other hand is, and always will be, a stored select statement. It's there in black and white in the data dictionary.
  • 12. Re: NOT NULL
    Stew Ashton Expert
    Currently Being Moderated
    To quote the 11.2 Concepts Guide:
    view
    A custom-tailored presentation of the data in one or more tables. The views do not actually contain or store data, but derive it from the tables on which they are based.
    I cannot find the expressions "virtual table" or "stored select statement" in the Oracle documentation, at least in the context of views.

    I think of a view as an "interface" to the data in one or more tables.

    The interface is "defined" by the column list (including the datatypes) as presented in the DESC command or in the USER_TAB_COLS view. When we use views, they fit into our SELECT statements more like tables than subqueries.

    The interface is "implemented" by the stored select statement, as presented in the USER_VIEWS view. The text of the stored select statement is integrated into the main query, which is then processed by the database.

    To me, "virtual table" emphasizes the way views fit into our SELECT statements (although the documentation prefers the expression "logical table"), whereas "stored select statement" emphasizes how the view is implemented.

    The official definition seems to strike a balance between the two aspects.

    Edited by: Stew Ashton on Feb 18, 2013 4:53 PM
  • 13. Re: NOT NULL
    BluShadow Guru Moderator
    Currently Being Moderated
    Stew Ashton wrote:
    To quote the 11.2 Concepts Guide:
    view
    A custom-tailored presentation of the data in one or more tables. The views do not actually contain or store data, but derive it from the tables on which they are based.
    I cannot find the expressions "virtual table" or "stored select statement" in the Oracle documentation, at least in the context of views.
    No, it doesn't called it a "stored select statement", but does refer to it as the views "subquery":

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_8004.htm#SQLRF54780, and this subquery is stored as the defining query of the view.
    I think of a view as an "interface" to the data in one or more tables.
    Hmmm, never though of calling them an "interface" myself. I can see why you could use that, but to me (personally) an "interface" conjures up the concept of something that is doing the work inbetween two things... whereas a view, to me, just provides the definition and the work is done by the originating query that's using it.
    The interface is "defined" by the column list (including the datatypes) as presented in the DESC command or in the USER_TAB_COLS view. When we use views, they fit into our SELECT statements more like tables than subqueries.
    Well, the documentation calls them subqueries, and they get optimized into the main query as a subquery would, so I'd prefer to think of them as subqueries than any sort of table.
    The interface is "implemented" by the stored select statement, as presented in the USER_VIEWS view. The text of the stored select statement is integrated into the main query, which is then processed by the database.

    To me, "virtual table" emphasizes the way views fit into our SELECT statements, whereas "stored select statement" emphasizes how the view is implemented.

    The official definition seems to strike a balance between the two aspects.
    Interesting point, but I still don't see "virtual table" as emphasizing how a view fits into our select statement, as a table would not be optimized into the query in the same way a subquery would. The stored select statement (subquery) isn't just there as some plain old definition of the view, it's actually taken as a query and integrated into the main query by the optimizer, and I think that's a key factor in it being more a stored (sub)query than it being any sort of table.
  • 14. Re: NOT NULL
    Stew Ashton Expert
    Currently Being Moderated
    BluShadow wrote:
    I still don't see "virtual table" as emphasizing how a view fits into our select statement, as a table would not be optimized into the query in the same way a subquery would.
    I meant to say "how _we_ fit the view into our select statement" as opposed to what Oracle does after we submit the query.

    In the SQL Language Reference on SELECT, INSERT, DELETE and UPDATE statements, it states clearly that we can references tables, views and materialized views in the same places and with the same syntax (with restrictions on usage of course). This is the "interface" part.

    We can often put subqueries in the same place, but we have to put parentheses around them, so there is a syntactic difference. If we use a subquery, we have to know it's a subquery, but if we use a view we can think it's a table and the query will still work.
    The stored select statement (subquery) isn't just there as some plain old definition of the view, it's actually taken as a query and integrated into the main query by the optimizer, and I think that's a key factor in it being more a stored (sub)query than it being any sort of table.
    Agreed, but that's what I call "implementation". We don't have to know that in order to get the data we want, although I grant we are much better off with that knowledge than without it.

    How about "a view appears logically as a table (when you write SQL, you use it as you would a table); it is defined and implemented by a stored select statement that is integrated into your SQL statement as a subquery"?

    Or simply "a view looks like a table but acts like a subquery"?

    Edited by: Stew Ashton on Feb 18, 2013 5:33 PM
1 2 3 5 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points