Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Gaurang.

Asked: May 11, 2020 - 3:45 pm UTC

Answered by: Connor McDonald - Last updated: May 20, 2020 - 11:22 pm UTC

Category: PL/SQL - Version: 11.2.03

Viewed 100+ times

You Asked

Hi - We'v following requirement for CCPA.

We need to extract all contacts (customer) information and send it to AWS data lake. Our company has many CRMs and other systems which keeps customer info and now due to new regulations we've to delete contact if the company hasn't connected them in 2-3 years depending on the law.

Our company is collecting all data in AWS data lake apply complex rules and send data that needs to be deleted to systems

We've Siebel CRM and here is how we're achieving this need.

- Write a stored procedure and concatenate contact information in CLOB
- load them as txt/csv content type using HTTP post program to AWS data lake.



Its working fine until here. Now we've 2 new requirement.

Encrypt data
Load them as Compress file



How is how're calling AWS data lake REST service using PL/SQL HTTP post.... Here is the code for

DECLARE
    req              utl_http.req;
    res              utl_http.resp;
   
    base_url         VARCHAR2(400) := 'http://datalake-staging-proxy.***.com/objects/v1/';
    end_url          VARCHAR2(400) := '?collection-id=XXXX';
    url              VARCHAR2(800):='http://datalake-staging-proxy.***.com/objects/v1/Testobject1?collection-id=XXXX';
    name             VARCHAR2(4000);
  --  buffer           VARCHAR2(4000);
    content          CLOB;
    content_blob     BLOB;
    content_length   NUMBER;
    respond          VARCHAR2(4000);
     my_clob clob;
    l_count number:=0;
  --  l_obj JSON_OBJECT_T;
  
    buffer          varchar2 (32767);
    amount          NUMBER := 25000;
    offset          NUMBER := 1;
    i number := 1;

BEGIN
   
      --  content := 'Welcome';
       delete from TEMP_LOB;
       COMMIT;
       dbms_lob.createtemporary(content, TRUE);
        for C1 in (select * from TEMP_AP_ENGAGEMENT_CCPA_VW where rownum< 25001)
        loop
         dbms_lob.append(content, i||','||C1.AFF_PER_ID||','||C1.PREFIX||','||C1.FIRST_NAME||','||C1.MIDDLE_NAME||','||C1.LAST_NAME||','||C1.EMAIL||','||C1.ADDR1||','||C1.ADDR2||','||C1.City||','||C1.State||','||C1.PostalCode||','||C1.Country||','||C1.ENGAGEMENT_DATE||','||C1.ENGAGEMENT_TYPE||','||C1.CUSTOMER_FLAG|| chr(13) || chr(10) );
         i := i + 1; 
      end loop;
       INSERT INTO TEMP_LOB VALUES (content);
       COMMIT;
          
        content_length := DBMS_LOB.getlength(content);
        dbms_output.put_line('Content Length :'||content_length);
        utl_http.set_response_error_check(enable => false);
        utl_http.set_detailed_excp_support(enable => true);
        req := utl_http.begin_request(url, 'PUT');
        utl_http.set_header(r => req, name => 'collection-id', value => 'XXXX');
        utl_http.set_header(r => req, name => 'x-api-key', value => '******');
        utl_http.set_header(r => req, name => 'Content-Type', value => 'text/csv');--'text/plain');
       -- UTL_HTTP.SET_BODY_CHARSET('UTF-8');
      
      
      
     --If Message data under 32kb limit
   if content_length<=32767 then
  
      -- UTL_HTTP.set_header (utl_req, 'Content-Length', req_length); 
       utl_http.set_header(req, 'Content-Length', content_length);
       utl_http.write_text(req, content);
     --  UTL_HTTP.write_text (utl_req, p_request_body);
   
   -- If Message data more than 32kb   
   elsif content_length > 32767 then

     UTL_HTTP.set_header (req , 'Transfer-Encoding', 'chunked');
   
       WHILE (offset < content_length)
       LOOP
       BEGIN
          DBMS_LOB.read (content,
                         amount,
                         offset,
                         buffer);
       --  dbms_output.put_line('Buffer  :' || buffer);                
        EXCEPTION
        WHEN OTHERS THEN
           dbms_output.put_line('IN CLOB BLOCK');
         --  dbms_output.put_line(' EXCE Buffer  :' || buffer);
            dbms_output.put_line(sqlerrm);
        END;    
        
          begin               
          UTL_HTTP.write_text (req, buffer);
          exception
          when others then
            dbms_output.put_line('IN BLOCK');
            dbms_output.put_line(sqlerrm);
          end;  
       --   dbms_output.put_line('WRITING :' ||offset ); 
          offset := offset + amount;
         
       END LOOP;
  
   end if;
              
          dbms_output.put_line('END WRITING');    
    --    utl_http.write_text(req, content);
        res := utl_http.get_response(req);
        utl_http.read_text(res, respond);
        utl_http.end_response(res);
        dbms_output.put_line(respond);
       -- dbms_output.put_line('Published: '||r1.item_number);
      --  l_count:=l_count+1;

EXCEPTION
    when utl_http.end_of_body then
        utl_http.end_response(res);
        dbms_output.put_line(sqlerrm);
    WHEN OTHERS THEN
        dbms_output.put_line(sqlerrm);
        utl_http.end_response(res);
               
END;







and we said...

Make you temporary content a BLOB not a CLOB.

Once you have written to it, you can use

  UTL_COMPRESS.lz_compress (src => content_blob,=> content_blob);


to compress it.

Encryption is a far bigger issue. We have the DBMS_CRYPTO package to provide most of the standard encryption facilities DES, 3DES, AES, RC4, 3DES_2KEY, etc but now you have a key distribution/management issue.

Tim Hall has some examples here https://oracle-base.com/articles/10g/database-security-enhancements-10g#dbms_crypto but you need to work out how you and the third party are going manage the keys etc.

and you rated our response

  (1 rating)

Reviews

Thanks

May 20, 2020 - 5:51 pm UTC

Reviewer: A reader

Thanks Connor. Your suggestion resolved my issue,
Connor McDonald

Followup  

May 20, 2020 - 11:22 pm UTC

glad we could help

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.