4 Replies Latest reply: Feb 5, 2013 1:08 PM by 989017 RSS

    Call APEX gurus

    989017
      I am calling APEX gurus and need your help on this. I'm currently developing a page to display some data from an Oracle table.

      SSN on the page currently is a "Display Only". SSN data in the table is encrypted. We developed a package to encrypt/decrypt it by using DBS_OBFUSCATION_TOOLKIT.
      The package looks like p_info.push(SSN) which means encrypt; p_info.pop(SSN) which means decrypt. On APEX page, in Source section,
      Source Used - Only when current value in session state is null; Source Type - SQL Query (return single value); Source value or expression -
      select p_info.pop(SSN) from table_A where seqno =:P2_seqno. It works very well.

      However, now we need to change SSN on the page from "Display Only" to be able to EDIT. So, I tried to change it "Display Only" to "Text field". Source Used - Always, replacing
      any existing value in session state; Source Type and Source value or expression are as same as above. But, when ran the page, after making changes on SSN and hit "Apply Change" button, SSN didn't get changed. Is there anything I'm missing?

      Your help will be truly appreciated.
        • 1. Re: Call APEX gurus
          Joe Upshaw
          Obviously, you can't simply store the unencrypted value, which is what is being displayed on the page back into the encrypted field. You need to create a computation On Submit to perform the pop operation prior to saving.

          What might be smoother for you is to consider adding a BEFORE INSERT trigger on the table that contains the encrypted value and perform the pop operation there. That should make it more seamless from the front end as no special processing would then be required.

          -Joe

          Edited by: Joe Upshaw on Feb 4, 2013 5:20 PM
          • 2. Re: Call APEX gurus
            989017
            Joe, you are right that I can't simply store the unencrypted value. What I need to do is like this --

            Some of records have SSN, which are encrypted in the table. On APEX page, they are displayed as unencrypted value, which I used Source value or expression -
            select pinfo.pop(SSN) from table_A where seqno = :P2_seqno; but now, user allows to UPDATE SSN. You are right that I need to create a computation On Submit to
            perform the PUSH operation (encrypt data) prior to saving SSN in the table. How?

            Adding a BEFORE INSERT trigger on the table is an option. However, is there a way I can do on the APEX level? Please specify it.

            Thanks,
            Jenny
            • 3. Re: Call APEX gurus
              Joe Upshaw
              Option 1: The Trigger....this really is better because your data will work no matter how the table is updated, i.e. via APEX or SQL*Plus or whatever...
              CREATE OR REPLACE TRIGGER BRIU_MYSSN 
              BEFORE INSERT OR UPDATE
              ON MYSSN 
              REFERENCING NEW AS new OLD AS old
              FOR EACH ROW
              DECLARE
                 lc_EncryptedSSN CHAR(32);
              BEGIN
                 
                 lc_EncryptedSSN := pinfo.pop(:new.SSN);
              
                 :new.SSN := lc_EncryptedSSN;
              EXCEPTION
                 WHEN OTHERS
                 THEN
                    -- Consider logging the error and then re-raise
                    RAISE;
              END;
              Option 2: APEX

              On you page, under Page Processing, right-click on Computations. Pick Item on this Page. Pick the page item corresponding to the SSN. Pick After Submit for you Computation Point. Pick PL/SQL for the function body.
              BEGIN
                 RETURN info.pop(:P8_SSN);
              END;
              -Joe
              • 4. Re: Call APEX gurus
                989017
                Joe,

                I chose option 2 and it works now. Thank you for your help!

                Jenny