1 2 3 Previous Next 31 Replies Latest reply: Nov 30, 2012 9:39 AM by JustinCave RSS

    updating the data from one schema to another schema

    969952
      Hi All,

      I have schema called SCOTT. In this I have a table Called EMP. The structure of EMP is as follows.
      Create table EMP
      (
      ENO   Number,
      ID      Varchar2(20),
      PAN    VARCHAr2(20),
      name  varchar2(20),
      location  varchar2(20),
      zip    number (8),
      mailid   number(8),
      Apartment varchar2(20),
      roomno    number(8),
      corierid  number(8),
      corier_person_name  varchar2(30),
      Status  varchar2(1)
      );
      i have a view EPM_VW in some other schema SYS which is created on 5 tables having related data to EMP table. Now my requirement is if am doing any modifications like INSERT/Update/Delete or any thing on EMP table in SCOTT it should be effectting immediately on the VIEW. Can you please give me your ideas? and don't want to see any NULL values for any columns.. we can define defalut values instead of null.

      Thanks.
        • 1. Re: updating the data from one schema to another schema
          Frank Kulash
          Hi,
          966949 wrote:
          Hi All,

          I have schema called SCOTT. In this I have a table Called EMP.
          It's best not to create your own objects in Oracle-supplied schemas, like SCOTT. Why not create your own schema for your own objects?
          The structure of EMP is as follows.
          Create table EMP
          (
          ENO   Number,
          ID      Varchar2(20),
          PAN    VARCHAr2(20),
          name  varchar2(20),
          location  varchar2(20),
          zip    number (8),
          mailid   number(8),
          Apartment varchar2(20),
          roomno    number(8),
          corierid  number(8),
          corier_person_name  varchar2(30),
          Status  varchar2(1)
          );
          i have a view EPM_VW in some other schema SYS which is created on 5 tables having related data to EMP table. Now my requirement is if am doing any modifications like INSERT/Update/Delete or any thing on EMP table in SCOTT it should be effectting immediately on the VIEW. Can you please give me your ideas?
          That's exactly how views work. If you COMMIT changes in emp, and then, a split second later, somebody else queries the view, the changes you just made will be visible in the view results.
          and don't want to see any NULL values for any columns.. we can define defalut values instead of null.
          Do you mean do don't want to see NULLs in the view, even though there may be NULLs in the table, or do you mean there shouldn;t ever be NULLs in the table?

          You can use NVL in the view, to change NULLs into some default value. For example:
          CREATE OR REPLACE VIEW  view_x
          AS
          SELECT  NVL ( emp.eno
                      , 0
                      )        AS eno
          ...
          If emp.eno is NULL, view_x.eno will be 0.

          If you want to make sure the table never has NULLs in a given column, use a NOT NULL constraint. You can also define a default value, so that if somebody INSERTs a row and doesn't mention that column, it will automatically get inserted with the default value. (The column should still be defined as NOT NULL, however.)
          Thanks.
          • 2. Re: updating the data from one schema to another schema
            969952
            Here for example I have given as examples. Have created the Table and view in my schemas.

            Yeah for Null values I will use NVL function.

            But my real requirement is like if we do any modifications on a table in One schema should effect the view which is created on Multiple tables in another view immediately.

            Please share your ideas.

            Thanks.
            • 3. Re: updating the data from one schema to another schema
              Frank Kulash
              Hi,
              966949 wrote:
              ... But my real requirement is like if we do any modifications on a table in One schema should effect the view which is created on Multiple tables in another view immediately.
              That's how views always work.
              When I run a query on the view, the results I get will be based on the state of the tables at the moment the query began. If you COMMITted some changes to one or more of the tables even a fraction of a second before I started my query, I will see those changes reflected in the view. It doesn't matter what schemas are involved, or how many schemas.
              • 4. Re: updating the data from one schema to another schema
                969952
                But am not getting how to link it and update the data at the same time.

                so please help me out.

                Thanks.
                • 5. Re: updating the data from one schema to another schema
                  JustinCave
                  You don't have to do anything.

                  If you have a view
                  CREATE VIEW emp_vw
                  AS
                  SELECT *
                    FROM emp
                  and you change the data in the EMP table, the view will automatically, transparently, without any intervention on your part, always reflect the data in the EMP table. A view is just a stored query. It does not contain any data. It does not maintain a separate copy of the data that can or should be modified.

                  Justin
                  • 6. Re: updating the data from one schema to another schema
                    Frank Kulash
                    Hi,
                    966949 wrote:
                    But am not getting how to link it and update the data at the same time.
                    Sorry, I don't understand the problem.

                    It would help it you posted a test script that would show the problem.
                    You already posted a CREATE TABLE statement. Post a CREATE VIEW statement that references that table. You only need to include 1 or 2 columns. Does it matter if the view references other tables? If so, post CREATE TABLE statements for the other tables, as few as possible.
                    Include some INSERT statements to populate the tables. Say exactly when these INSERTs are COMMITted.
                    Post a query on the view, and the exact results you expect to see from that query given the sample data you posted. Be sure to mention the exact time when the query on the view begins.
                    • 7. Re: updating the data from one schema to another schema
                      969952
                      Hi,

                      Yeah thanks.

                      As per my knowledge View doesn't contain ant data.

                      But the view is created in anoter Schema named it as " XYZ" like
                      Create or replace View XYX_VW as
                      
                      select col1,col2,col3 from t1
                      union all
                      select col1,col2,col3 from t2
                      Union All
                      select col1,col2,col3 from t3
                      UInion 
                      select col1,col2,col3 from t4
                      like this view has been created in XYZ Schema.


                      but I have created EMP table in SCOTT schema ( for my convenience mentioned Scott but created in my Schema)

                      here if I updated the columns COL1,COL2,COL3 in EMP Table immediately

                      the columns in the VIEW ( which is there in XYZ Schema) COL1.COL2,COL3 should also be updated automatically. because these 3 columns in SCOTT SCHEMA (EMP Table) are having same data types.

                      so If I update these 3 columns in the EMP table in the SCOTT schema the same columns in XYZ_VW (in XYZ schema) should update automatically.

                      Please let me know still you require more info.

                      Thanks.
                      • 8. Re: updating the data from one schema to another schema
                        JustinCave
                        Assuming that `EMP` is one of the four tables referenced in the view definition (it is confusing if you talk about an EMP table and then post a view definition that selects from four different tables T1, T2, T3, and T4), queries against the view will see the data in the EMP table as soon as those changes are committed. There is nothing you can, should, or need to do. A view is just a stored query.

                        Justin
                        • 9. Re: updating the data from one schema to another schema
                          Frank Kulash
                          Hi,
                          966949 wrote:
                          Hi,

                          Yeah thanks.

                          As per my knowledge View doesn't contain ant data.

                          But the view is created in anoter Schema named it as " XYZ" like
                          Create or replace View XYX_VW as
                          
                          select col1,col2,col3 from t1
                          union all
                          select col1,col2,col3 from t2
                          Union All
                          select col1,col2,col3 from t3
                          UInion 
                          select col1,col2,col3 from t4
                          like this view has been created in XYZ Schema.


                          but I have created EMP table in SCOTT schema ( for my convenience mentioned Scott but created in my Schema)
                          If it's in schema ABC, why say it's in SCOTT? Why not say it's in ABC? If you don't want to mention the real schema name ABC for some reason, make up something, like SCHEMA_2.
                          here if I updated the columns COL1,COL2,COL3 in EMP Table immediately

                          the columns in the VIEW ( which is there in XYZ Schema) COL1.COL2,COL3 should also be updated automatically. because these 3 columns in SCOTT SCHEMA (EMP Table) are having same data types.
                          The view in your example is based on tables T1, T2, T3 and T4. It has nothing to do with the EMP table.
                          If you mean
                          Create or replace View XYX_VW as
                          select col1,col2,col3 from ABC.EMP ...
                          then why say T1? Why not say EMP?
                          The purpose of the code is to show what you're doing. Don't use it to show whay you're not doing.
                          so If I update these 3 columns in the EMP table in the SCOTT schema the same columns in XYZ_VW (in XYZ schema) should update automatically.
                          Yes, that's what views are designed to do. Try it. Start 2 separate sessions (they can be in separate windows on the same client.) In one session, make changes in the base table(s). In the other session, query the view. You'll see all the changes as soon as they are COMMITted.
                          • 10. Re: updating the data from one schema to another schema
                            969952
                            1. In Scott Schema we have EMP table. and it is having ENO,ENAME,JOb,SAL,NAME .... columns)

                            2. The view is having ENO,ENAME,PHONE num,... columns) view name is XYZ_VW ( from the 4 tables view is retrieving ENO,ENAME,JOB) values. ( expect)

                            3. FOr example if you are updating EMP Table for ENO,ENAME,JOB Columns immeately the changes should effect View as well. that means if you are updating EMP table for ENO,ENAME columns immediately values of XYZ_VW should also be updated as EMP table.

                            3. if you are running the query select * from EMP;


                            before updating the data
                            o/p will be
                            ENO ENAME  JOB
                            1        A        X
                            2        B        Y
                            3        C        Z
                            same data will be existed in the view also
                            ENO ENAME  SAL
                            1        A       100
                            2        B       200
                            3        C       300
                            after updating the EMP table like
                            update EMP set ENO=4 where ENO=1;
                            
                            Update EMP set ENO=8  WHERE ENO= 2;
                            then the output of the EMP is
                            ENO ENAME  JOB
                            4        A        X
                            8        B        Y
                            3        C        Z
                            now the data in the view should also be
                            ENO ENAME  SAL
                            4        A       100
                            8        B       200
                            3        C       300
                            Hope you got it.

                            Thanks.
                            • 11. Re: updating the data from one schema to another schema
                              JustinCave
                              And what we're telling you is that this is exactly how views work. There is nothing you can do. The view will always display the updated data (providing, of course, that the updates are visible to the current session).

                              Justin
                              • 12. Re: updating the data from one schema to another schema
                                969952
                                Yeah .. But the values are not getting updated properly.

                                Is there any possibilities to write any trigger/package/... anything for this?
                                • 13. Re: updating the data from one schema to another schema
                                  JustinCave
                                  Then, as Frank has suggested, you need to show us exactly what you are seeing. It is not possible for a view not to return different data when the data in the underlying table changes. Because it is not possible for a view to return incorrect data, there is no way to create a trigger (or any other object) to update the view since there is nothing to update.

                                  One possibility is that you are modifying the data in session 1, querying the data from the view in session 2, and session 1 is not committing the changes. That will cause session 2 to continue to see the original data until session 1 completes its transaction. There are other possibilities but we'd need to see the sort of test case Frank asked for to help you figure out what the problem is.

                                  Justin
                                  • 14. Re: updating the data from one schema to another schema
                                    969952
                                    Hi All,

                                    Please excuse me..

                                    It's my understanding problem.. it's not a view... it's is some other table in other Schema..

                                    Now the requirement is if the columns in EMP table in SCOTT schema got updated/Inserted/deleted then immediately the other table XYZ in Other schema will automatically updated with the same data which is available in EMP table for those columns.Pleae let me know the functionality o this.

                                    Thanks.

                                    Edited by: 966949 on Nov 29, 2012 12:09 PM
                                    1 2 3 Previous Next