This discussion is archived
1 2 3 Previous Next 31 Replies Latest reply: Nov 30, 2012 7:39 AM by Justin Cave Go to original post RSS
  • 15. Re: updating the data from one schema to another schema
    sb92075 Guru
    Currently Being Moderated
    966949 wrote:
    Hi All,

    Please excuse me..

    It's my understanding problem..
    post SQL & results that demonstrate this "problem"
  • 16. Re: updating the data from one schema to another schema
    Justin Cave Oracle ACE
    Currently Being Moderated
    OK. Why is that a requirement?

    It makes little sense to store the same data in multiple tables of the same database. You could build triggers on each table that manually replicated changes from one to the other. But the architecture is highly suspect-- if the same data is supposed to be in two different tables, one of those tables ought to be replaced with a view or a synonym or something else that doesn't require duplicating the data.

    Justin
  • 17. Re: updating the data from one schema to another schema
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    966949 wrote:
    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.
    Are you sure you need to duplicate the data in another table? That sounds like a bad design. This is what views are designed for.

    If you really do need to duplicate the value, write a trigger: AFTER INSERT OR UPDATE OR DELETE ON scott.emp FOR EACH ROW.
  • 18. Re: updating the data from one schema to another schema
    969952 Newbie
    Currently Being Moderated
    I am not expert in Oracle .. Can you please help me out to write a trigger and it's not like a duplicates right? both are in different schemas? and if it looks duplicate we can create a sysnonym in the another schema .. can you please give me brief idea on this and let me know the procee..

    Thanks.
  • 19. Re: updating the data from one schema to another schema
    969952 Newbie
    Currently Being Moderated
    yeah.. becasue the requirement is the data is updating in backend.. ifwe see the data for the same employee in Front-end it is not showing the updated values. It's showing the old values. SO want to write any PL/SQL Functionality..

    SO please help me out.

    Thanks.
  • 20. Re: updating the data from one schema to another schema
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    As Justin and I both said, replicating the same data in different tables in the same database is almost always a bad idea. I don't want to help you do things poorly, I'd rather help you do things well. I suspect copying the data from one table to another is a poor way to do whatever you need to do. If you can explain your business requriements, someone will help you find a good way to them, whether that's a trigger, a view, or anything else.

    If you really want to try writing a trigger, there are examples in the 2-Day Developer's Guide and other documentation. Any decent introductory PL/SQL book will also havce examples and explanations.
  • 21. Re: updating the data from one schema to another schema
    969952 Newbie
    Currently Being Moderated
    Please find my below requirement.

    I have EMP tale in SCOTT Schema.

    in some other schema XYZ i have a view called XYZ_VW based on the table ABC.

    in EMP( Scott Schema) and ABC ( XYZ Schema) some columns are equal.

    if you are updating any data in the EMP table, the data for the related columns in the table ABC also should update automatically. But here it's not happening.

    if I see the data in back end for EMP table I am able to see the updated data but am not able to see the latest data in ABC table in front end, means data is not getting updated.

    So now I want to see the latest data in EMP table (SCOTT Schema) and ABC Table (XYZ Schema) for the the same columns at a time once the modifications done. So please let me know how it will happen ?

    Hope you got my requirement. Please let me knwo still you require more info.

    Thanks.
  • 22. Re: updating the data from one schema to another schema
    Justin Cave Oracle ACE
    Currently Being Moderated
    Why is the system designed so that the same data exists in two different tables?

    The proper approach would be to store employee data in only one place. If we assume that is the EMP table in the SCOTT schema, then employee data should never be in the ABC table in the XYZ schema. If you want employee data in the XYZ_VW view, you should accomplish that by having the view join the data in XYZ.ABC to the data in SCOTT.EMP. That probably requires that the ABC table have an EMPLOYEE_ID column that matches the EMPLOYEE_ID primary key in the EMP table.

    Justin
  • 23. Re: updating the data from one schema to another schema
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    966949 wrote:
    Please find my below requirement.

    I have EMP tale in SCOTT Schema.

    in some other schema XYZ i have a view called XYZ_VW based on the table ABC.

    in EMP( Scott Schema) and ABC ( XYZ Schema) some columns are equal.

    if you are updating any data in the EMP table, the data for the related columns in the table ABC also should update automatically. But here it's not happening.
    Why is it necessary for the same data to be in 2 places? What business requirement does that meet? Why couldn't you do it some other way, for example, by including some data from EMP in XYZ_VW?
    if I see the data in back end for EMP table I am able to see the updated data but am not able to see the latest data in ABC table in front end, means data is not getting updated.
    Are you expecting it to be updated? Do you already have a trigger on EMP?
    So now I want to see the latest data in EMP table (SCOTT Schema) and ABC Table (XYZ Schema) for the the same columns at a time once the modifications done. So please let me know how it will happen ?

    Hope you got my requirement. Please let me knwo still you require more info.
    Sorry, no. You've sketched one possible way to meet some business requirement. I still don't know what that requirement is, so I don't know if the plan you've described will do what you need or not, or if there's a much better way to meet that requirement.
  • 24. Re: updating the data from one schema to another schema
    969952 Newbie
    Currently Being Moderated
    This is my business requirement. I didn't write any triggers... I have to write any triggers if required... so don't have any other options to explain..

    these are the options/requirements I have as of now?
  • 25. Re: updating the data from one schema to another schema
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    966949 wrote:
    This is my business requirement. I didn't write any triggers... I have to write any triggers if required... so don't have any other options to explain..

    these are the options/requirements I have as of now?
    Sorry, as long as I don't know what your requirements are, I can't help you meet them.
  • 26. Re: updating the data from one schema to another schema
    969952 Newbie
    Currently Being Moderated
    I don't have words to explain the requirement. This is the requirement I got...
  • 27. Re: updating the data from one schema to another schema
    Sg049 Explorer
    Currently Being Moderated
    Hi 966949 ,

    As you mentioned Scott.EMP table has Employee information and view in XYZ schema let's say XYZ_VIEW based on four table t1,t2,t3 and t4.

    If view not depend on EMP table then how would both table and view contains same data? View does not contain any data you can not update a view you have to update the underlying tables i.e t1,t2,t3 and t4.

    If you really want a help just post Create and Insert statements for SCHEMA1.EMP table and SCHEMA2.T1,SCHEMA2.T2,SCHEMA2.T3 and SCHEMA2.T4 and results you are expecting. Please paste your SQL code between
     tags                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 28. Re: updating the data from one schema to another schema
    969952 Newbie
    Currently Being Moderated
    Hi the view is depends on the other table called " EMLOYEES"

    in EMP table and EMPLOYEES we have some common columns. want to update those columns. Till now I didn't mentioned the target table Employees.

    SO it should compare the EMP and Employees table , based on that what are the common columns it is having it should update with the latest updated data.

    Thank you all for your patience. so now please guide me how to write it.. I tried to to write a Merge styatement but it is not working as expecting result.

    so I just want to write any trigger/Procedure/..any... if EMP table is updated then immediately Employees table also should update?
  • 29. Re: updating the data from one schema to another schema
    969952 Newbie
    Currently Being Moderated
    Hi I have EMP table in SCOTT schema.

    and VIEW XYZ_VW is created in another schema .let's take it as ABC.

    This view is created, based on the joining of 4 tables i.e T1,T2,T3,T4.

    Then this view data is importing into the table called TEMP1.

    so this table table TEMP1 is having some data. it's also having the column ( not all columns some of the columns) names as EMP table in SCOTT SCHEMA.

    So now I want to ,

    If I am doing any changes on EMP table in SCOTT Schema this should immediately affect in TEMP1 table columns (related columns ) in ABC as well.

    Hope you got my requirement.

    Please have a look and let me know How can I link these two schemas, how the data should be updated immediately.( i(f matched else keep the data as it is)
    ( do we need to create any Trigger/plsql code for this .. if required can you please show me the code as well).



    Thanks.

Legend

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