programing

json과 오라클에서 작업

minimums 2023. 6. 12. 21:16
반응형

json과 오라클에서 작업

오라클 내에서 JSON과 쉽게 작업할 수 있는 방법이 있습니까?나는 웹 서비스를 꽤 자주 호출하는 표준 절차가 있는데, JSON은 웹 개발 맥락에서 익숙한 형식이지만 저장 프로시저 내에서 json과 작업하는 가장 좋은 방법은 무엇입니까?예를 들어 URI에서 CLOB 응답을 가져와서 JSON 개체로 변환하고 값을 얻습니다.

참고로 URL을 가져오는 데 사용한 절차는 다음과 같습니다.

create or replace procedure macp_URL_GET(url_resp in out clob, v_url in varchar2) is
   req     Utl_Http.req;
   resp    Utl_Http.resp;
   NAME    VARCHAR2 (255);
   VALUE   VARCHAR2 (1023);
   v_msg   VARCHAR2 (80);
   v_ans clob;
--   v_url   VARCHAR2 (32767) := 'http://www.macalester.edu/';
BEGIN
   /* request that exceptions are raised for error Status Codes */
   Utl_Http.set_response_error_check (ENABLE => TRUE );
   /* allow testing for exceptions like Utl_Http.Http_Server_Error */
   Utl_Http.set_detailed_excp_support (ENABLE => TRUE );
   /*
   Utl_Http.set_proxy (
      proxy                 => 'www-proxy.us.oracle.com',
      no_proxy_domains      => 'us.oracle.com'
   );
   */
   req := Utl_Http.begin_request (url => v_url, method => 'GET');
   /*
    Alternatively use method => 'POST' and Utl_Http.Write_Text to
    build an arbitrarily long message
  */

  /*
   Utl_Http.set_authentication (
      r              => req,
      username       => 'SomeUser',
      PASSWORD       => 'SomePassword',
      scheme         => 'Basic',
      for_proxy      => FALSE      --this info is for the target Web server 
   );
   */

   Utl_Http.set_header (r => req, NAME => 'User-Agent', VALUE => 'Mozilla/4.0');
   resp := Utl_Http.get_response (r => req);
   /*
   DBMS_OUTPUT.put_line ('Status code: ' || resp.status_code);
   DBMS_OUTPUT.put_line ('Reason phrase: ' || resp.reason_phrase);
   FOR i IN 1 .. Utl_Http.get_header_count (r => resp)
   LOOP
      Utl_Http.get_header (r => resp, n => i, NAME => NAME, VALUE => VALUE);
      DBMS_OUTPUT.put_line (NAME || ': ' || VALUE);
   END LOOP;
   */
--test
   BEGIN
      LOOP
         Utl_Http.read_text (r => resp, DATA => v_msg);
         --DBMS_OUTPUT.put_line (v_msg);
         v_ans := v_ans || v_msg;
         url_resp := url_resp || v_msg;
      END LOOP;
   EXCEPTION
      WHEN Utl_Http.end_of_body
      THEN
         NULL;
   END;
--test
   Utl_Http.end_response (r => resp);


   --url_resp := v_ans;

EXCEPTION
   /*
    The exception handling illustrates the use of "pragma-ed" exceptions
    like Utl_Http.Http_Client_Error. In a realistic example, the program
    would use these when it coded explicit recovery actions.

    Request_Failed is raised for all exceptions after calling
    Utl_Http.Set_Detailed_Excp_Support ( ENABLE=>FALSE )
    And it is NEVER raised after calling with ENABLE=>TRUE
  */
   WHEN Utl_Http.request_failed
   THEN
      DBMS_OUTPUT.put_line (
         'Request_Failed: ' || Utl_Http.get_detailed_sqlerrm
      );
      url_resp :='Request_Failed: ' || Utl_Http.get_detailed_sqlerrm;
   /* raised by URL http://xxx.oracle.com/ */
   WHEN Utl_Http.http_server_error
   THEN
      DBMS_OUTPUT.put_line (
         'Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm
      );
      url_resp := 'Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm;
   /* raised by URL http://otn.oracle.com/xxx */
   WHEN Utl_Http.http_client_error
   THEN
      DBMS_OUTPUT.put_line (
         'Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm
      );
      url_resp := 'Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm;
   /* code for all the other defined exceptions you can recover from */
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
      url_resp := SQLERRM;
END;

그럼 테스트해 보겠습니다.

begin
  macp_url_get(url_resp => :url_resp,
               'http://maps.googleapis.com/maps/api/geocode/json?address=55105&sensor=false');
end;

(구글 API가 xml 응답을 허용한다는 것은 알고 있지만 정기적으로 사용하는 다른 웹 API 중에는 JSON으로 기본 설정되어 있습니다.)

저는 이 라이브러리를 사용하기 시작했고, 유망해 보입니다: https://github.com/pljson/pljson

설치하기 쉽고 예제도 좋습니다.

예제에서 라이브러리를 사용하려면 절차에 이러한 변수를 추가합니다.

mapData     json;
results     json_list;
status      json_value;
firstResult json;
geometry    json;

....

그런 다음 응답을 json 개체로 조작할 수 있습니다.

-- convert the result from the get to a json object, and show some results.
mapData := json(v_ans);

-- Show the status of the request
status := mapData.get('status');
dbms_output.put_line('Status = ' || status.get_string());

IF (status.get_string() = 'OK') THEN
  results := json_list(mapData.get('results'));
  -- Grab the first item in the list
  resultObject := json(results.head);

  -- Show the human readable address 
  dbms_output.put_line('Address = ' || resultObject.get('formatted_address').to_char() );
  -- Show the json location data 
  dbms_output.put_line('Location = ' || resultObject.get('geometry').to_char() );
END IF;

이 코드를 실행하면 dbms 출력으로 출력됩니다.

Status = OK
Address = "St Paul, MN 55105, USA"
Location = {
  "bounds" : {
    "northeast" : {
      "lat" : 44.9483849,
      "lng" : -93.1261959
    },
    "southwest" : {
      "lat" : 44.9223829,
      "lng" : -93.200307
    }
  },
  "location" : {
    "lat" : 44.9330076,
    "lng" : -93.16290629999999
  },
  "location_type" : "APPROXIMATE",
  "viewport" : {
    "northeast" : {
      "lat" : 44.9483849,
      "lng" : -93.1261959
    },
    "southwest" : {
      "lat" : 44.9223829,
      "lng" : -93.200307
    }
  }
}

Oracle 12c부터는 JSON을 기본적으로 지원합니다.하지만 현재 형태에서는 다른 답변에 포함된 PLJSON과 같은 것만큼 유용하지 않다고 생각합니다.

피쳐를 사용하려면 BLOB, CLOB 또는 Varchar2 필드가 있는 테이블을 만들고 "column ISJSON"에 대한 제약 조건을 추가합니다.이렇게 하면 해당 열에서 JSON 구문 검사가 수행됩니다.

"IS JSON" 제약 조건이 있는 한 SQL의 점 표기법을 사용하여 JSON 값에 액세스할 수 있습니다.저에게는 PLJSON만큼 강력한 조작을 제공하지 않는 것 같습니다.XMLType을 만든 다음 JSON으로 변환할 수도 있습니다.

한 링크: 유한링크:

좋은 튜토리얼 및 예제
to JSONXML-JSON을한 자습서

저는 http://reseau.erasme.org/pl-sql-library-for-JSON?lang=en, 이라는 라이브러리를 썼고, 이것은 plsql 테이블에 json 응답을 가져오는 데 매우 효과적입니다.

Oracle 데이터를 추출하여 Json으로 변환하려는 경우 이 라이브러리는 "사용하기가 너무 무겁습니다"...나는 그것을 더 잘 그리고 더 빨리 하는 다른 코드를 제안할 수 있습니다:

create or replace package jsonfly as

procedure open_object(k varchar2 default null);
procedure close_object;
procedure open_array (k varchar2 default null);
procedure close_array;
procedure separation;
procedure member(k varchar2, v varchar2);
procedure member(k varchar2, n number);
procedure send;
end;
/

create or replace package body jsonfly as
--------------------------------------------------------------------------------
-- package pour générer du JSON, envoyé à la volé
--------------------------------------------------------------------------------
type tCache is table of varchar2(2000) index by binary_integer;

g_openBrace         constant varchar2(2) := '{ ';
g_closeBrace        constant varchar2(2) := ' }';
g_openBracket       constant varchar2(2) := '[ ';
g_closeBracket      constant varchar2(2) := ' ]';
g_stringDelimiter   constant varchar2(1) := '"';
g_Affectation       constant varchar2(3) := ' : ';
g_separation        constant varchar2(3) := ', ';
g_CR                constant varchar2(1) := Chr(10); -- used to indent the JSON object correctly
g_spc               constant varchar2(2) := '  ';     -- used to indent the JSON object correctly
g_js_comment_open   constant varchar2(20) := '/*-secure-\n'; -- used to prevent from javascript hijacking
g_js_comment_close  constant varchar2(20) := '\n*/';          -- used to prevent from javascript hijacking

--isObjectOpened  boolean := false;
--isArrayOpened   boolean := false;
t tCache;
i number := 1;

--------------------------------------------------------------------------------
-- 
--------------------------------------------------------------------------------
procedure p(s  varchar2) is
begin
    t(i) := s;
    i := i + 1;
end;
--------------------------------------------------------------------------------
-- 
--------------------------------------------------------------------------------
function encap (s varchar2) return varchar2 is
begin
    return g_stringdelimiter || s || g_stringdelimiter;
end;

--------------------------------------------------------------------------------
-- 
--------------------------------------------------------------------------------
function encode_string(p_string varchar2) return varchar2 is
begin
    return replace(replace(replace(replace(replace(replace(replace(replace(p_string, 
        '\', '\\'), 
        '"', '\"'), 
        '/', '\/'), 
        chr(8), '\b'), 
        chr(9), '\t'), 
        chr(10), '\n'), 
        chr(12), '\f'), 
        chr(13), '\r');
end;

--------------------------------------------------------------------------------
-- 
--------------------------------------------------------------------------------
procedure open_object(k varchar2 default null) is
begin    
    if ( k is null ) then 
        p(g_openbrace);
    else 
        p( encap(k) || g_affectation || g_openbrace);
    end if; 
end;

--------------------------------------------------------------------------------
-- 
--------------------------------------------------------------------------------
procedure close_object is
begin
    if (t(i-1) = g_separation) then
        i := i - 1;
    end if; 
    p(g_closebrace);
    separation();
end;

--------------------------------------------------------------------------------
-- 
--------------------------------------------------------------------------------
procedure open_array (k varchar2 default null) is
begin    
    if ( k is null ) then 
    p(g_openbracket);
    else 
        p( encap(k) || g_affectation || g_openbracket);
    end if; 
end;

--------------------------------------------------------------------------------
-- 
--------------------------------------------------------------------------------
procedure close_array is
begin
    if (t(i-1) = g_separation) then
        i := i - 1;
    end if; 
    p(g_closebracket);
    separation();
end;

--------------------------------------------------------------------------------
-- 
--------------------------------------------------------------------------------
procedure separation is
begin
    p(g_separation);
end;

--------------------------------------------------------------------------------
-- 
--------------------------------------------------------------------------------
procedure key(k varchar2) is
begin
   p( encap(k) || g_affectation);
end;

--------------------------------------------------------------------------------
-- 
--------------------------------------------------------------------------------
procedure value(v varchar2) is
begin
   p(v);
end;

--------------------------------------------------------------------------------
-- 
--------------------------------------------------------------------------------
procedure member(k varchar2, v varchar2) is
begin
    p( encap(k) || g_affectation || encap(encode_string(v)));
    p(g_separation);
end;

--------------------------------------------------------------------------------
-- 
--------------------------------------------------------------------------------
procedure member(k varchar2, n number) is
begin
    p( encap(k) || g_affectation || n );
    p(g_separation);
end;

--------------------------------------------------------------------------------
-- 
--------------------------------------------------------------------------------
procedure send is
begin
    if (t(i-1) = g_separation) then
        t.delete(i-1);
    end if; 

    i := t.first;
    while (i is not null) loop
        htp.p(t(i));
        i := t.next(i);
    end loop;
end;


end jsonfly;
/

Oracle 12c는 다음과 같은 기본 JSON 지원을 제공합니다.

Oracle Database는 트랜잭션, 인덱싱, 선언형 쿼리 및 보기를 포함한 관계형 데이터베이스 기능을 통해 JSON(JavaScript Object Notification) 데이터를 기본적으로 지원합니다.

JSON 데이터와 XML 데이터는 Oracle Database에서 유사한 방식으로 사용할 수 있습니다.관계형 데이터와 달리 데이터를 정의하는 스키마 없이 둘 다 저장, 인덱싱 및 쿼리할 수 있습니다.Oracle Database는 트랜잭션, 인덱싱, 선언형 쿼리 및 보기를 포함한 관계형 데이터베이스 기능과 함께 JSON을 기본적으로 지원합니다.

JSON 데이터는 Oracle NoSQL Database 및 Oracle Berkeley DB와 같은 NoSQL 데이터베이스에 저장되는 경우가 많습니다.이를 통해 스키마를 기반으로 하지 않는 데이터를 저장하고 검색할 수 있지만 관계형 데이터베이스의 엄격한 일관성 모델을 제공하지는 않습니다.

이러한 단점을 보완하기 위해 관계형 데이터베이스가 NoSQL 데이터베이스와 병렬로 사용되기도 합니다.그런 다음 NoSQL 데이터베이스에 저장된 JSON 데이터를 사용하는 응용 프로그램은 데이터 무결성 자체를 보장해야 합니다.

Oracle Database에서 JSON을 기본적으로 지원하므로 이러한 해결 방법이 없습니다.트랜잭션, 인덱싱, 선언형 쿼리 및 보기를 포함하여 JSON과 함께 사용할 수 있는 관계형 데이터베이스 기능의 모든 이점을 제공합니다.

Oracle Database 쿼리는 선언적입니다.JSON 데이터를 관계형 데이터와 결합할 수 있습니다.또한 JSON 데이터를 관계형으로 투영하여 관계형 프로세스 및 도구에 사용할 수 있습니다.데이터베이스 내에서 외부 테이블의 데이터베이스 외부에 저장된 JSON 데이터를 쿼리할 수도 있습니다.

OCI, .NET 및 JDBC를 사용하여 다른 데이터베이스 데이터에 액세스하는 것과 동일한 방법으로 데이터베이스에 저장된 JSON 데이터에 액세스할 수 있습니다.

SQL 데이터 유형 XMLType을 사용하여 저장되는 XML 데이터와 달리 JSON 데이터는 SQL 데이터 유형 VARCHAR2, CLOB 및 BLOB를 사용하여 Oracle Database에 저장됩니다.열 값이 유효한 JSON 인스턴스인지 확인하려면 항상 is_json 검사 제약 조건을 사용하는 것이 좋습니다.

Oracle APEX 5.0은 APEX_JSON 패키지를 사용하는 JSON을 지원합니다.저는 그것을 사용해 본 적이 없지만, 흥미로워 보여서 우리 팀에게 그것을 탐색해 달라고 요청했습니다.우리의 사용 사례는 JSON 데이터를 nodejs 애플리케이션에서 저장 프로시저에 입력 매개 변수로 전달할 수 있는 것입니다.

인생은 행복합니다. 이것을 시도해 보세요.

CLOB 또는 20000000자 JSON

with data as
  ( select 
    xmlelement(e,regexp_replace('{"name":"'||colname||'"}', '[[:cntrl:]]', ''),',') col1
    from tblname
  )
  select
        rtrim(replace(replace(replace(xmlagg(col1).getclobval(),'&'||'quot;','"'),'<E>',''),'</E>',''),',')
        as very_long_json
  from data;

언급URL : https://stackoverflow.com/questions/7985841/work-with-json-in-oracle

반응형

'programing' 카테고리의 다른 글

C에서 **를 사용할 때의 차이점  (0) 2023.06.12
업데이트된 수명 주기 후크의 Vuex 상태 업데이트  (0) 2023.06.12
Firestore - 컬렉션에 문서를 추가한 후 문서 ID를 가져오는 방법  (0) 2023.06.12
Vuejs를 사용하여 mapAction 메서드에 액세스하는 방법은 무엇입니까?  (0) 2023.06.12
Reactjs, Typescript - 속성이 하위 구성 요소에 없습니다.  (0) 2023.06.12

'programing'의 다른글

  • 현재글json과 오라클에서 작업

관련글

댓글

티스토리툴바