Oracle Apex - przechowywanie danych w tabeli Oracle w formacie JSON

Jeśli wykorzystujemy tabele bazy danych do przechowywania ustawień aplikacji, często dochodzimy do momentu kiedy ilość pól jest niewystarczająca i musimy dodać kolejne pole. A z drugiej strony mamy parametry które nie potrzebują takiej ilości pól. Mamy też sytuacje kiedy nigdy nie wiemy ile pól będziemy potrzebować dla jakiegoś parametru. Rozwiązaniem tego formatu jest JSON. W jednym polu możemy przechować praktycznie każdą strukturę, każde ustawienia.

Tworzenie w tabeli Oracle pola typu JSON i używanie JSON w celu przechowania informacji.

Oracle nie posiada gotowego pola typu JSON. Takim formatem może być VARCHAR2 (limit znaków) CLOB lub BLOB jeśli przechowujesz dane binarne i nie chcesz niepożądanej konwersji znaków. Tabelę możesz utworzyć używając SQL Developer, możesz użyć też opcji SQL Workshop w Oracle Apex i wklejając odpowiednie komendy SQL. Jednak Oracle Apex oferuje wygodne GUI ktore przeprowadzi Cie przez cały proces. Więcej, nie musisz zawracać sobie głowy indeksami, atutomumeracją, trigerami. Zacznij więc od stworzenia tabeli. Powiedzmy że będzie mieć pola:

ID: number
APP_PARAM: varchar2(50)
PARAM_VALUE: clob

Pole 'param_value' będzie przechowywać ustawienia w formacie JSON. Póki co to pole jest po prostu polem typu CLOB - przyjmie każdą wartość. My chcielibyśmy jednak by akceptowało tylko JSON - da to nam pewność że ciąg znaków który się tam znajdzie rzeczywiście jest formatu JSON. Poniższą komendą zapewnimy sprawdzenie formatu przed umieszczeniem danych w tabeli - IS JSON contraint. Wykonujemy tę komendę w SQL Workshop => SQL Commands:

alter table app_settings
add constraint param_value_json check ( param_value is json );

Sprawdzamy czy pole tabeli sprawdza poprawność składni JSON

Jeśli wejdziesz do menu SQL Workshop => Object browser i zechcesz dodać nowy rekord do tabeli, nie będziesz w stanie. Oracle Apex nie oferuje interfejsu do wstawiania danych typu CLOB. Musisz zrobić to z poziomu SQL Workshop => SQL Commands. Wykonaj następującą komendę by umieścić testowe dane:

INSERT INTO app_settings (app_param, param_value) VALUES ('param_2', '{"eur": 4.67}');

Oracle zwóci informację że rekord został umieszczony w tabeli. Dla testu wykonaj zmianę w 'param_value'... np usuń końcowy nawias z JSON i spróbuj umieścić te dane ponownie - wykonaj komendę raz jeszcze. Zobaczysz ze Oracle nie pozwoli na to ponieważ format JSON jest niepoprawny.

Zapisywanie do tabeli z aplikacji Oracle Apex

Ustawienia naszej aplikacja będą trywialne. Potrzebujemy aktualnego kursu euro. Bedziemy go wpisywac w formularzu i przechowywac w tabeli w formacie JSON. Łatwo możesz dodać więcej pól i elementów w tym JSON.

Pod przyciskiem 'Save settings' umieść Dynamic Action (Execute Server-side code => PL SQL) o następującej składni:

DECLARE
  l_exchange_rates_eur number(6,4);
BEGIN
    l_exchange_rates_eur := :P501_EXCHANGE_RATE_EUR_VALUE;
    UPDATE APP_SETTINGS SET param_value = '{"eur": '||l_exchange_rates_eur||'}' WHERE app_param = 'exchange_rates';
END;

Pamiętaj by przekazać wartość  pola P501_EXCHANGE_RATE_EUR_VALUE do procedury (Items to submit). W powyższej procedurze tworzymy bardzo prosty JSON poprzez złączenie stringów i zawartości pola. Nie ma powodu komplikowć jeśli Twoja aplikacja będzie mieć jedynie kilka pól z ustawieniami. Miej jednak na uwadze że ten przykład tylko obrazuje proces - w realnym świecie będziesz prawdopodobnie potrzebować zrobić bardziej zaawansowaną procedurę.

Odczytywanie ustawien z tabeli

Aby wyświetlić wartość "eur" z JSON znajdującego się w tabeli, należy odczytać zawartość rekordu a następnie sparsować pole 'param_value' jako JSON. Nasz JSON nie zawiera tablic więc będzie to banalnie proste. W sekcji Pre-Rendering tworzymy nowy proces pod After Header (Local Database, PL/SQL):

DECLARE
  l_eur            NUMBER(5,3);
  l_json           VARCHAR2(500);
BEGIN
   SELECT param_value INTO l_json FROM APP_SETTINGS WHERE app_param = 'exchange_rates';
   apex_json.parse(l_json);
   l_eur := apex_json.get_number(p_path => 'eur');
   apex_util.set_session_state('P501_EXCHANGE_RATE_EUR_VALUE', l_eur);
END;

Jeśli element byłby typu string, pobierasz jego wartość używając 'get_varchar2' (np: apex_json.get_varchar2(p_path => 'jakis_string') ). Gdyby Twoj JSON zawierał tablice kod mógłby wyglądać następująco (uwaga, to pseudo kod):

DECLARE
  sJsonIndex      APEX_JSON.t_values;
  l_response      CLOB;

  l_num_items     NUMBER;
  l_task_id       VARCHAR2(80);
  l_formkey       VARCHAR2(5);


BEGIN
    l_response := zrodlo naszego JSON...
    apex_json.parse(sJsonIndex, l_response);
    l_num_items := APEX_JSON.get_count(p_path => '.' , p_values => sJsonIndex);
  
    IF l_num_items > 0 THEN
      FOR i in 1 .. l_num_items LOOP
         l_task_id := apex_json.get_varchar2(p_path => '[%d].id', p0 => i, p_values => sJsonIndex);
         l_formkey := apex_json.get_varchar2(p_path => '[%d].formKey', p0 => i, p_values => sJsonIndex);     
      END LOOP;
    END IF;

 EXCEPTION
  WHEN OTHERS THEN
    RAISE;
 
END;