Oracle APEX PL/SQL, escape apostrophe character from a field

Question

I have two text fields that the user can sometimes add an apostrophe character.

So on my page I have a P2003_JOB and P2003_PATIENT_NAME items that contains this character.

How can I escape this character in my query?

SELECT PATIENT_NAME into :P2003_PATIENT_NAME 
FROM PATIENTS
WHERE PATIENT_JOB = :P2003_JOB;

:P2003_RSLT := 'Patient: &P2003_PATIENT_NAME. job is &P2003_JOB.'

Answer 1

Another alternative would be to build your string with bind variables.

:P2003_rslt := apex_string.format('Patient: %s job is %s'
                                 ,:p2003_patient_name
                                 ,:p2003_job);