Results 1 to 10 of 10
  1. #1
    Join Date
    May 2008
    Posts
    10

    Question Unanswered: How to validate data across two scehmas

    Hi,
    Could someone please help me with this situation.
    The oracle version I am using is 9i.

    I have a table called MYBOOKS (TITLE, AUTHOR) in schemaA, and a view ALLBOOKS_VW (TITLE, AUTHOR) which is a joint of three other tables, in schemaB. Both schemas are on the same instance.
    I have to set up a daily job, in order to check that the data records for each row in schemaA.MYBOOKS table match the ones in the schemaB.ALLBOOKS_VW view.
    And then write the log into schemaA.LOG table (LOG_DATE, JOB_NUM, OUTCOME).

    So, for example, if we have a row in schemaA.MYBOOKS with (TITLE = BANANA, and AUTHOR = SMITH), then when the job runs, it should check if a same record exists in the schemaB.ALLBOOKS_VW or not. In either case, it should then automatically write to the LOG table in schemaA with an appropriate outcome.

    I can't come up with a solution in my head. I assume I will have to create some kind of procedure or function and then set up a job to run that procedure daily. But unfortunately I have never done this before, and don't have much knowledge of it.

    Please help asap.... Many thanks in advance
    Last edited by Jagga; 05-20-08 at 12:49.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Check out the fine Oracle manual and read about the MINUS operator.
    .
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As data you need to check belong to different schemas, you'll have to do "something" in order to be able to compare them.

    One way would be to GRANT SELECT on a table that belongs to SchemaB to SchemaA; then you can reference it using either "schema_name.table_name" notation, or create a SYNONYM in SchemaA for a table in SchemaB.

    Query that does the job is rather simple - LKBrwn_DBA told you what to search for. Inserting a record into a log table is also easy.

    In order to do it daily, you'll have to schedule a job. It can be done using the DBMS_JOB package.

    Now that you know all relevant keywords, do the homework. Come back if you have a question about a specific problem (because you won't find someone to do it for you that easy).

  4. #4
    Join Date
    May 2008
    Posts
    10
    hi,
    Thanks a lot for your kind advice so far.

    I granted select on schemaB.ALLBOOKS_VW to schemaA and also created synonym.
    And thanks to LKBrwn_DBA for advice on MINUS operator. I am getting the desired rows back now with;

    SELECT TITLE, AUTHOR FROM MYBOOKS
    MINUS
    SELECT TITLE, AUTHOR FROM ALLBOOKS_VW

    I have been trying to figure out how to implement this method, in order to get the outcome automatically written to schemaA.LOG table (LOG_TIME, JOB_NUM, OUTCOME).

    I know how to set up jobs, but not sure if I need to create a procedure/package or a function which will go validate the data and write the results to the log table. I have never done anything similar in the past.

    Please advice on how to get this done...
    Many thanks in advance

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes, a procedure might be a good choice. It could look like this:
    Code:
    SELECT COUNT(*)
    INTO l_cnt
    FROM (SELECT * FROM mybooks
          MINUS
          SELECT * FROM allbooks
         );
    
    IF l_cnt > 0 THEN
       INSERT INTO log_table ('there is a difference!');
    END IF;
    Schedule such a procedure; that should do it.

  6. #6
    Join Date
    May 2008
    Posts
    10
    WOW, we are getting there. Thanks a lot for your help Littlefoot.

    I currently have following procedure which is valid, but only writes one entry into log table, whereas I need it to write as many entries as the invalid rows, providing the data in the log message.
    For example, if there are 3 rows with invalid data then, I need to have three log entries written to the log table, one for each row. And within the log message it needs to show the data that is wrong. So, if AUTHOR value varies for a specific TITLE, then the log should be something like this,

    "The author 'MYBOOKS.AUTHOR' of the book title 'MYBOOKS.TITLE' in schemaA does not match with the author 'ALLBOOKS.AUTHOR' for the book 'ALLBOOKS.TITLE' in schemaB".


    Current Code;

    CREATE OR REPLACE procedure test_prc is

    l_cnt number(5);

    begin
    SELECT COUNT(*)
    INTO l_cnt
    FROM (SELECT author, title FROM myBooks
    MINUS
    SELECT author, title FROM allBooks
    );

    IF l_cnt > 0 THEN
    INSERT INTO LOG values (sysdate, 2, 'data could not be validated');
    END IF;

    end;
    /

    I assume I could have another four varchar2 parameters for relevant four author/title fields and use them to display data in log message.
    But not sure how to do it for each row. Do I need some kind of loop? Please advise.

    Many thanks in adavnce

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Maybe something like this

    insert into log_file
    select sysdate,1,'missing book - '||author||'/'||title
    from
    (SELECT author, title FROM myBooks
    MINUS
    SELECT author, title FROM allBooks);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    May 2008
    Posts
    10
    Hi guys,

    I am going to be a pain in the A$$.
    firstly, Beilstwh, Littlefoot and LKBrwn_DBA thanks to all of you for providing help so far.
    In my previous message, I asked you guys for advice on how to get individual log entries written for all the invalid data rows. I thought this is what the original requirement was. But unfortunately, this was not the case. I actually need the data written to the log table in a one single field for all the invalid rows, and this one message/row should contain information from all the invalid rows.
    So, may be using a loop to generate an extended STRING with variables or something.
    i.e. (String = String + (VAR1 + '/' + VAR2))

    But guys, I have no idea on how to get this done. Please advise on this.

    Many thanks in advance...

    the current code for the procedure is as follows, and works fine but it writes 1000 rows into the log table for 1000 invalid rows, whereas I need one row to be written into the log table for everytime the procedure is executed and message should contain information on all the 1000 invalid rows.

    For example;

    LOG MESSAGE 'This data is wrong - title1\author1 , title2\author2 , ...'



    Current Code;

    CREATE OR REPLACE PROCEDURE TEST5_PRC IS

    l_cnt NUMBER(5); --An integer that holds the number of invalid rows.

    BEGIN

    --Assigning the total number of invalid rows to I_cnt.
    SELECT COUNT(*) INTO l_cnt
    FROM
    (SELECT AUTHOR, TITLE FROM MYBOOKS
    MINUS
    SELECT AUTHOR, TITLE FROM ALLBOOKS_VW);

    --if there are no invalid rows then write to the log table that data is valid.

    IF l_cnt = 0 THEN insert into MYBOOKS_log select sysdate,1,'Data is Valid.' from dual;
    commit;

    --if there is invalid data present in schemaA.MYBOOKS then write to the log table, the details of the invalid data.

    ELSIF l_cnt > 0 THEN
    insert into MYBOOKS_log
    select sysdate,2,'Data in schemaA.MYBOOKS is invalid - (AUTHOR \ Title) ( '||AUTHOR||' \ '||TITLE || ' ).'
    from
    (SELECT AUTHOR, TITLE FROM MYBOOKS
    MINUS
    SELECT AUTHOR_name, TITLE FROM ALLBOOKS_VW);
    commit;

    END IF;

    end;
    /

  9. #9
    Join Date
    May 2008
    Posts
    10
    Hi,

    Does anbody have any idea of how to get this done.
    please reply asap.
    Many thanks in advance...

  10. #10
    Join Date
    May 2008
    Posts
    10
    Hi,
    I have done some reading, and seems like I have to use/create an array and use a loop to extend the log message STRING.
    But I am still struggling with getting itto work.

    Could anybody advise please.

    Thanks a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •