Consuming Content Server webservices from an Oracle database

I recently had a requirement to consume Content Server webservices from an APEX application.  I opted to do this in the database and build a package to call the rest webservice API’s provided by Content Server.  The APEX_WEB_SERVICE package worked great for most of the requests that needed to be made (authenticating and receiving a token, getting node id’s and creating folders).

Keep in mind if the webservice you are calling uses SSL, you will need to setup an Oracle Wallet to store the certificates.  The root and any intermediate certificates need to be included.  In this example, since the package resides in the database the wallet needs to be created on the database servers rather than the application server.

You will need to set the following headers prior to calling APEX_WEB_SERVICE.make_rest_request:

 apex_web_service.g_request_headers(1).name := 'Content-Type';
 apex_web_service.g_request_headers(1).value := 'application/x-www-form-urlencoded;';

The first call you will need to make to Content Server is to authenticate and receive a token.  Use the APEX_JSON package to parse the response to get the token.  For this call you will need pass the username and password in the p_username and p_password parameters (this will not be required for subsequent calls since a ticket is passed in the header).

 

  l_clob := APEX_WEB_SERVICE.make_rest_request(
    p_url => '<your Content Server URL>/api/v1/auth',
    p_http_method => 'POST',
    p_wallet_path => <path to Oracle wallet>,
    p_wallet_pwd => <Oracle wallet password>,
    p_username => <Content Server username>,
    p_password => <Content Server password>,
    p_parm_name => APEX_UTIL.string_to_table('username:password'),
    p_parm_value => APEX_UTIL.string_to_table(<Content Server username>||':'||<Content Server password>)
 );

apex_json.parse(l_clob);

All subsequent calls to the API will need to include an additional header to pass the OTCS ticket obtained in the authentication API call:

 apex_web_service.g_request_headers(2).name := 'otcsticket';
 apex_web_service.g_request_headers(2).value := <your ticket>;

Depending on the API being called, most calls should follow a format similar to the example below and return a clob that can be parsed with apex_json.  This is an example of request to get the node_id of a folder or document:

 l_clob := APEX_WEB_SERVICE.make_rest_request(
   p_url => '<your Content Server URL>/api/v1/nodes/'||<parent node id>||'/nodes',
   p_http_method => 'GET',
   p_wallet_path => <path to Oracle wallet>,
   p_wallet_pwd => <Oracle wallet password>,
   p_parm_name => APEX_UTIL.string_to_table('where_name'),
   p_parm_value => APEX_UTIL.string_to_table(<node name>)
 );

apex_json.parse(l_clob);

However, I could not get APEX_WEB_SERVICE to work when uploading a document as this required a multipart/form-data request to be made.  Nick Buytaert at APEXPLAINED has a great solution for handling multipart/form-data requests that helped me solved my dilemma:  UTL_HTTP and a multipart/form-data request body — APEXPLAINED

One thing to note is that even though the request body variable is defined as a clob, Oracle will treat it as a varchar2 if you do string concatenations.  If you will be passing data greater than 32k, you will need to use dbms_lob to build up your clob.  In my particular situation, I had to break the request body up into three sections and write them to the request separately because the actual file payload needed to be sent as blob:

 while l_offset < l_request_body_length_pre loop
   dbms_lob.read(l_request_body_pre_file, l_amount, l_offset, l_buffer);
   utl_http.write_text(l_http_request, l_buffer);
   l_offset := l_offset + l_amount;
 end loop;
 
 l_offset := 1;
 l_buffer := null;
 
 while l_offset < l_request_body_length_blob loop
   dbms_lob.read(p_file_blob, l_amount, l_offset, l_buffer);
   utl_http.write_raw(l_http_request, l_buffer);
   l_offset := l_offset + l_amount;
 end loop;
 
 l_offset := 1;
 l_buffer := null;
 
 while l_offset < l_request_body_length_post loop
   dbms_lob.read(l_request_body_post_file, l_amount, l_offset, l_buffer);
   utl_http.write_text(l_http_request, l_buffer);
   l_offset := l_offset + l_amount;
 end loop;

Developing Dynamic Tabular Forms Part 5 – Performing calculations

The final piece of my project is to calculate a score based on the answers to the questionnaire.  This calculation will be done using the calculation SQL text field we setup in Part 1.

In my database package, I created a calculation function that will return the score.  This function simply loops through a query that joins the questionnaire table with the answer table and performs an “EXECUTE IMMEDIATE” to execute the calculation SQL text and keeps a running total.

FUNCTION calculate_score RETURN NUMBER IS
  CURSOR get_answers_c IS
  SELECT score_calculation, answers.answer_id
  FROM answers, questions
  WHERE answers.question_id = questions.question_id
  AND score_calculation IS NOT NULL
  AND answers.answer IS NOT NULL; 

  v_score_temp NUMBER;
  v_score NUMBER;
BEGIN
  v_score_temp := 0;
  v_score := 0;
 
  FOR rec IN get_answers_c LOOP
    BEGIN
      EXECUTE IMMEDIATE rec.score_calculation INTO v_score_temp USING rec.answer_id;
      v_score := v_score + v_score_temp;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      CONTINUE;
    END;
  END LOOP;

  RETURN v_score;
END;