This discussion is archived
1 2 Previous Next 28 Replies Latest reply: Mar 21, 2013 1:43 AM by BluShadow RSS

Need Query

982895 Newbie
Currently Being Moderated
Hi All,

I have a table "PATIENT" ..In that there is a column called FIRSTNAME. I want to mask that column. This is just to differentiate between UAT and PROD data. I can do an update but the problem here is that there will be lakhs of records. SO the DB may hang. So, I need an alternate for the same. Please assist me..

For Eg:

FIRSTNAME
DEEPTI
Vikas
....
...

My Expected Output should be:

FIRSTNAME
DEEPTI|X
vikas|X
...
...

The delimiter can be anything..

Thanks in advance
  • 1. Re: Need Query
    user503635 Explorer
    Currently Being Moderated
    Try this
    - create a view
    - create a synonym to link the view to synonym patient
    create view patient_v as select FIRSTNAME||'|'||'X' firstname, ....(list your remaining fields of table patient) from patient
    /
    
    CREATE PUBLIC SYNONYM patient FOR patient_v
    /
    
    select * from patient
    /
    Edited by: user503635 on Mar 15, 2013 12:57 AM

    Edited by: user503635 on Mar 15, 2013 12:58 AM
  • 2. Re: Need Query
    982895 Newbie
    Currently Being Moderated
    Hi,

    Cant we do it in the same table?
  • 3. Re: Need Query
    982895 Newbie
    Currently Being Moderated
    Hi,

    My Boss is asking me to create a Procedure in a way that it updates the table directly.

    Please assist me :)
  • 4. Re: Need Query
    user503635 Explorer
    Currently Being Moderated
    Don't understand what you mean ? can you give some example ?

    The synonym on top of the view has the same name as your original table. It also behaves the same as your original table, you can insert/update/delete/select it, and further you do not need to change your application codes.

    In my previous reply, I forgot one step to rename the table, so the SELECT * from paitent may still point to the original table.

    Below is the test script
    create table tst 
    (firstname VARCHAR2(10), notes varchar2(10));
    
    insert into tst values ('MARY', 'TEST');
    
    rename tst to tst_old
    /
    
    create or replace view tst_v 
    as select firstname||'|'||'X' fistname, notes 
    from tst_old
    /
    
    CREATE PUBLIC SYNONYM tst FOR tst_v
    /
    
    select * from tst
    /
    Edited by: user503635 on Mar 15, 2013 1:16 AM
  • 5. Re: Need Query
    Paul Horth Expert
    Currently Being Moderated
    979892 wrote:
    Hi,

    My Boss is asking me to create a Procedure in a way that it updates the table directly.

    Please assist me :)
    Why do you need a procedure? Why not just do a simple update? Why do you think the DB will hang doing something a standard as that?

    BTW: this is an international forum, most people do not know what a lakh is. Please use standard terms when referring to numbers.

    Edited by: Paul Horth on Mar 15, 2013 8:16 AM
  • 6. Re: Need Query
    982895 Newbie
    Currently Being Moderated
    Hi Paul,

    We tried using update but it got strucked many a times. So,he needs a procedure so that it commits for every few records like 1000 or 10000 using bulk update or so..
    I guess bulk update is not at all introduced. Using bulk select and using some limit, we can do it. But I forgot the syntax/the way to proceed. Can anyone help me in doing the same.
  • 7. Re: Need Query
    jeneesh Guru
    Currently Being Moderated
    979892 wrote:
    Hi Paul,

    We tried using update but it got strucked many a times.
    So,he needs a procedure so that it commits for every few records like 1000 or 10000 using bulk update or so..
    BULK processing cannot be faster than plain UPDATE statement..
    And Lakhs of rows is not very large for any oracle database..

    You need to identify and fix the actual issue, instead of using BUL processing

    Read FAQ: {message:id=9360003}


    And FYI, UPDATE can be done with FORALL
    declare
     type t_n is table of number;
     t_n1 t_n;
    begin
     select deptno
     bulk collect into t_n1
     from dept;
     forall i in 1..t_n1.count
       update emp
       set sal = null
       where deptno = t_n1(i);
    end;
    Edited by: jeneesh on Mar 15, 2013 2:55 PM
  • 8. Re: Need Query
    982895 Newbie
    Currently Being Moderated
    Hi jeenesh,

    Your Query is to update all records. But my requirment is to commit the records for evry 1000 records.
    Please modify the query and send..
  • 9. Re: Need Query
    Karthick_Arp Guru
    Currently Being Moderated
    979892 wrote:
    Hi jeenesh,

    Your Query is to update all records. But my requirment is to commit the records for evry 1000 records.
    Please modify the query and send..
    Why do you want to do it like that. Frequent commit only slows down the process. Its not of any good. Also it will lead to ORA-1555 error which could be nasty.
  • 10. Re: Need Query
    BluShadow Guru Moderator
    Currently Being Moderated
    979892 wrote:
    Hi Paul,

    We tried using update but it got strucked many a times. So,he needs a procedure so that it commits for every few records like 1000 or 10000 using bulk update or so..
    I guess bulk update is not at all introduced. Using bulk select and using some limit, we can do it. But I forgot the syntax/the way to proceed. Can anyone help me in doing the same.
    Doing updates in batches in a loop with commits is WRONG.... completely WRONG. It is not transactionally safe, and can lead to things like a "snapshot too old" error, as well as slowing down performance, and at the same time increasing the use of server resources (spawning off more writer processes unnecessarily).

    Commit should be done at the end of a transaction, not in the middle of it, and certainly not in a loop for a measly 1000 or 10000 records. Oracle is an enterprise RDBMS server capable of handling millions of rows of data efficiently. Attempting to do what you are suggesting is just a way of trying to break the database and introduce errors.

    You boss clearly doesn't understand the technical side of Oracle and shouldn't be making technical decisions about how to implement the update. If he disagrees, please get him to come onto these forums and explain why he believes this is the way it should be done, so that people can correct his misunderstanding.
  • 11. Re: Need Query
    982895 Newbie
    Currently Being Moderated
    Ok I accept with you all. Please suggest me which would be the best ..creating a view ?
  • 12. Re: Need Query
    jeneesh Guru
    Currently Being Moderated
    979892 wrote:
    Hi jeenesh,

    Your Query is to update all records. But my requirment is to commit the records for evry 1000 records.
    Please modify the query and send..
    Did you read my post completely?

    BULK Processing is NOT required for your scenario..

    That code was just a sample - since you commented as UPDATE with BULK COLLECT is not possible..
  • 13. Re: Need Query
    982895 Newbie
    Currently Being Moderated
    The other team might access this table. So, if we create a view and synonym for that, will the records be updated in the main table whenever we query the view?

    Please clarify my doubts. So, that I can suggest the same to my boss..
  • 14. Re: Need Query
    BluShadow Guru Moderator
    Currently Being Moderated
    979892 wrote:
    The other team might access this table. So, if we create a view and synonym for that, will the records be updated in the main table whenever we query the view?

    Please clarify my doubts. So, that I can suggest the same to my boss..
    No, the view will not update the base tables, it just presents the data from the base tables depending on what the subquery of the view is written to show.

    If you really need to update the base table data, the quickest and simplest way is to just do an SQL UPDATE statement. If this is to obfuscate (or differentiate) data between UAT and PROD then it's surely a one off process to your test data. (Or are you saying that the PROD data is regularly copied to UAT? If so, how is that done?)
1 2 Previous Next

Legend

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