This discussion is archived
4 Replies Latest reply: Feb 5, 2013 11:08 AM by 989017 RSS

Call APEX gurus

989017 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Joe,

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

    Jenny

Legend

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