This discussion is archived
1 2 3 Previous Next 31 Replies Latest reply: Nov 30, 2012 7:39 AM by Justin Cave RSS

updating the data from one schema to another schema

969952 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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