RTE AutoSave PL/SQL Ajax Callback PL/SQL Procedure

save_note_bg.sql

PROCEDURE save_note_bg
  (p_note_id       IN cnnt_notes.note_id%TYPE,
   p_old_checksum  IN VARCHAR2,
   p_note_array    IN wwv_flow_global.vc_arr2) IS

  l_new_checksum      VARCHAR2(100);
  l_current_checksum  VARCHAR2(100);
  l_note_content      cnnt_notes.note_content%TYPE;

BEGIN

  -- Get the Current saved Checksum for the note (before the change).
  SELECT dbms_crypto.hash(NVL(note_content,'X'),1) INTO l_current_checksum
  FROM   cnnt_notes
  WHERE  note_id = p_note_id;

  IF l_current_checksum <> p_old_checksum THEN
    -- If the Checksum has changed it means someone else changed the note.
    --  Return JSON to the calling JavaScript code including a message that can
    --  be displayed to the user.
    apex_json.open_object;
    apex_json.write('status_code', 'E');
    apex_json.write('status_msg', APEX_LANG.MESSAGE('ANOTHER_USER_CHANGED_NOTE'));
    apex_json.close_object; 

  ELSE

    -- Call a procedure to backup the note to another table before saving it.
    cnnt_utl_pk.backup_note (p_note_id => p_note_id);

    -- Get the Note Content from the chunked array.
    l_note_content := empty_clob();
    dbms_lob.createtemporary(l_note_content, true);
    FOR i IN 1..p_note_array.count LOOP   
      l_note_content := l_note_content || TO_CLOB(p_note_array(i));
    END LOOP;

    -- Save the latest note content to the notes table.
    UPDATE cnnt_notes
    SET    note_content = l_note_content
    WHERE  note_id      = p_note_id;

    -- Calculate a new checkum for the note content.
    l_new_checksum := dbms_crypto.hash(l_note_content,1);

    -- Return JSON to the JavaScript code letting it know all is OK.
    -- Include the updated checksum so the JS code can set it in a page item for next time.
    apex_json.open_object;
    apex_json.write('status_code', 'S');
    apex_json.write('status_msg', 'Note ID ['||p_note_id|| '] AutoSaved');
    apex_json.write('new_checksum', l_new_checksum);
    apex_json.close_object; 

  END IF;

EXCEPTION WHEN OTHERS THEN
  -- Let the calling JS code know If something unexpected happens.
  apex_json.open_object;
  apex_json.write('status_code', 'E');
  apex_json.write('status_msg', SQLERRM);
  apex_json.close_object; 
END save_note_bg;