1 2 Previous Next 28 Replies Latest reply: Mar 21, 2013 3:43 AM by BluShadow RSS

    Need Query

    982895
      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
          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
            Hi,

            Cant we do it in the same table?
            • 3. Re: Need Query
              982895
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              Ok I accept with you all. Please suggest me which would be the best ..creating a view ?
                              • 12. Re: Need Query
                                jeneesh
                                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
                                  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
                                    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