HubSpot integration using REST API in Oracle APEX

HubSpot integration using REST API in Oracle APEX





Part 1: https://youtu.be/xNh7f8fOODc 
Part 5: https://youtu.be/ntlJ0Q0tWYg

Welcome to my blog post, where I’ll guide you step by step on how to integrate HubSpot with Oracle APEX using the REST API. The aim is to enable seamless data transfer between these two platforms. Here's what we'll cover:

  1. Creating a Token in HubSpot: Learn how to generate an authentication token in HubSpot, which is essential for API access.
  2. Setting Up a Procedure in Oracle APEX: Discover how to utilize the authentication token in Oracle APEX to make REST API calls via a procedure.
  3. Submitting Data Through a Form to HubSpot: Once the connection is established, I’ll show you how to send data directly into HubSpot using a form.
  4. Importing Data Back into Oracle APEX: Finally, I’ll demonstrate how to retrieve data stored in HubSpot and import it back into the Oracle APEX database.

Follow along with this comprehensive guide to effectively implement the integration!


Getting Started with Integrating HubSpot and Oracle APEX

Before we dive into the technical details, it is important that you set up both an Oracle APEX account and a developer account with HubSpot. Once you have signed up with HubSpot, it all starts with a simple click:

  1. Look for the button at the top right of the page and click on it.
  2. Then select “Profile & Settings” from the menu.





Setting up your private app in HubSpot

After clicking on “Profile & Settings”, a variety of options will appear on the left side. To get to the integration, follow these steps:

  1. Navigate to the Account Setup section.
  2. Under this menu item you will find the option “Integration”. Click on it.
  3. Then select “Private Apps”.

This is where the actual setup process begins for the private app that we will use for the REST API connection between HubSpot and Oracle APEX.





Configuring your individual apps for customized integration

In the “Private Apps” section, you have the option to configure your own apps to ensure integration between HubSpot and Oracle APEX.




Creating a new app in HubSpot

When you are in the Private Apps section, you will notice that there are no apps initially. We will create a new app. To do this, follow these simple steps:

  1. Click on “Create app” or “Add new app”.
  2. A configuration wizard will open, guiding you step by step through the entire app creation process.

Configuring the details of the new app

The next step is to provide the necessary detailed information.

  • Name: Enter a name.
  • Description: Provide a clear description of your app.







Defining API permissions in the “Areas” tab and setting up a test environment and app creation

A critical part of configuring your new app is the "Scopes" tab. In this section, you specify which permissions your app should have. These settings are crucial because they determine which data your app can access and which actions it can perform:

  • Read permissions: Allow the app to read data.
  • Write permissions: Enabling you to change or add data.

It is important to enable only the permissions that are absolutely necessary.


For an effective testing phase, I recommend enabling all permission checkboxes first. This will allow you to test all the API's features without being limited by permission errors. However, please note that this setting is for testing purposes only. Here are the steps:

  1. Enabling all permissions: Make sure you can fully test all API functionality.
  2. Create app: After you have entered all the necessary information and set the permissions for the test phase, click on the "Create app" button.

With these steps, your new app will be created in HubSpot and ready for integration with Oracle APEX.


Confirmation and completion of app creation

After you click on “Create app”, a confirmation prompt usually appears.

  • Confirmation of details: Checks all the information entered again and then confirms the creation of the app.



Receiving and securely storing your access token

After confirming the app creation, you will be shown an access token. This token plays a central role in authenticating your app during API calls:

  • Token security: It is extremely important to keep this token safe. Since it allows access to your data in HubSpot, you should protect it from unauthorized access.
  • use of the token: Use the token in your code to programmatically authenticate with the HubSpot API.




Monitoring API calls in your HubSpot account

Once your app is successfully set up in HubSpot, you can view it at any time in your account. A particularly useful aspect of this is the ability to get a detailed overview of the API calls. This offers the following advantages:

  • Monitoring of operations: You can monitor all API calls made.
  • analysis of data transmissions: Access to the history of API calls allows you to check what data was transferred and how the interactions took place.

These features are particularly valuable for ensuring that your integration is working correctly and for identifying and resolving potential problems early on.


Preparation for data transfer and form creation in Oracle APEX

After you have completed your settings in HubSpot, it is crucial to carefully note down the access token.

  1. Noting the token: Make sure you store the token securely as it is needed to authorize data transfer between systems.

Now switch to Oracle APEX to reach the next goal:

  1. Creating a form in Oracle APEX: After you have logged in, create a form. This form should contain various fields that are tailored to the data you want to collect.

Creating a new page in Oracle APEX with an interactive button

After logging into Oracle APEX, you can start creating a new page.

  1. Creating a new page: In the menu, go to the option to create a new page.

  2. Adding a button: On this new page, add a button. Configure the button so that it opens a pop-up window when clicked.

  3. Popup window configuration: The pop-up window should be designed to capture additional data.


Setting up a modal window for data integration in Oracle APEX

After the button is placed on your main page, clicking it will open a modal window. This modal window, which is defined as page number 10, is for the data collection process:

  1. setting up the window: Ensures that the window is set as page number 10 and opens automatically when the button is pressed.

  2. Adding the input fields: Add the necessary fields to capture the data you want to save later in HubSpot.

  3. objective of data collection: The data from these fields is sent directly to HubSpot as soon as it is entered and submitted.

These steps will save the data quickly and directly to HubSpot.


Form design on modal page number 10 in Oracle APEX

On the modal page number 10, you should set up a user-friendly form that contains the following elements:


Data transfer from Oracle APEX to HubSpot using PL/SQL

After a user has filled out the form on modal page number 10 and clicked "Save", the system automatically triggers a PL/SQL procedure.

BEGIN
    create_hubspot_contact(
        p_email    => :P10_EMAIL,
        p_firstname => :P10_FIRSTNAME,
        p_lastname => :P10_LASTNAME,
        p_website  => :P10_WEBSITE,
        p_company  => :P10_COMPANY,
        p_phone    => :P10_PHONE,
        p_address  => :P10_ADDRESS,
        p_city     => :P10_CITY,
        p_state    => :P10_STATE,
        p_zip      => :P10_ZIP
    );
END;

Oracle APEX package for communication with HubSpot

Within the Oracle APEX project, a developed package is used that contains the necessary code to interact with the HubSpot API. This package is central to the efficient and secure transfer of data between Oracle APEX and HubSpot.

create or replace PROCEDURE create_hubspot_contact (
    p_email    VARCHAR2,
    p_firstname VARCHAR2,
    p_lastname VARCHAR2,
    p_website  VARCHAR2,
    p_company  VARCHAR2,
    p_phone    VARCHAR2,
    p_address  VARCHAR2,
    p_city     VARCHAR2,
    p_state    VARCHAR2,
    p_zip      VARCHAR2
) AS
    l_response CLOB;
    l_url VARCHAR2(500) := 'https://api.hubapi.com/crm/v3/objects/contacts';
    l_body CLOB;
BEGIN
    -- Setzen der notwendigen Header für JSON Content-Type und Authorization
    apex_web_service.g_request_headers(1).name := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/json';
    apex_web_service.g_request_headers(2).name := 'Authorization';
    apex_web_service.g_request_headers(2).value := 'Bearer APEX-API-TOKEN';
   
    -- Erstellen des Body für den POST-Request mit Parametern
    l_body := '{
            "properties": {
                "email": "' || p_email || '",
                "firstname": "' || p_firstname || '",
                "lastname": "' || p_lastname || '",
                "website": "' || p_website || '",
                "company": "' || p_company || '",
                "phone": "' || p_phone || '",
                "address": "' || p_address || '",
                "city": "' || p_city || '",
                "state": "' || p_state || '",
                "zip": "' || p_zip || '"
            }
        }';

    -- Ausführen des POST-Requests
    l_response := apex_web_service.make_rest_request(
        p_url => l_url,
        p_http_method => 'POST',
        p_body => l_body
    );

    -- Optional: Antwort verarbeiten oder loggen
    dbms_output.put_line(l_response);

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error: ' || SQLERRM);
END;

Procedure if wallet is connected.

create or replace PROCEDURE create_hubspot_contact (
    p_email    VARCHAR2,
    p_firstname VARCHAR2,
    p_lastname VARCHAR2,
    p_website  VARCHAR2,
    p_company  VARCHAR2,
    p_phone    VARCHAR2,
    p_address  VARCHAR2,
    p_city     VARCHAR2,
    p_state    VARCHAR2,
    p_zip      VARCHAR2
) AS
    l_response UTL_HTTP.req;
    l_resp UTL_HTTP.resp;

    l_url VARCHAR2(500) := 'https://api.hubapi.com/crm/v3/objects/contacts';
    l_body CLOB;
BEGIN


    -- Wallet- und HTTPS-Konfiguration
    UTL_HTTP.set_wallet('file:/opt/oracle/oradata/wallet/hubapi', 'Sajjad786');
   
    -- Erstellen des Body für den POST-Request mit Parametern
    l_body := '{
            "properties": {
                "email": "' || p_email || '",
                "firstname": "' || p_firstname || '",
                "lastname": "' || p_lastname || '",
                "website": "' || p_website || '",
                "company": "' || p_company || '",
                "phone": "' || p_phone || '",
                "address": "' || p_address || '",
                "city": "' || p_city || '",
                "state": "' || p_state || '",
                "zip": "' || p_zip || '"
            }
        }';

    -- Ausführen des POST-Requests
    l_response := UTL_HTTP.begin_request(
        url => l_url,
        method => 'POST',
        http_version => 'HTTP/1.1'
    );

    -- Header für JSON und Authorization setzen
    UTL_HTTP.set_header(l_response, 'Content-Type', 'application/json; charset=utf-8');
    UTL_HTTP.set_header(l_response, 'Authorization', 'Bearer APEX-API-TOKEN');
    UTL_HTTP.set_header(l_response, 'Content-Length', LENGTH(l_body));

    -- JSON-Body senden
    UTL_HTTP.write_text(l_response, l_body);

   
    -- Response erhalten
    l_resp := UTL_HTTP.get_response(l_response);

     

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error: ' || SQLERRM);
END;
/




Process for creating a contact in HubSpot via Oracle APEX

When a user fills out the provided form in Oracle APEX and clicks the save button, the following process is triggered:

  1. Activation of the procedure: The procedure “CREATE_HUBSPOT_CONTACT” is started.

  2. Data collection: First, the procedure collects all the data that the user has entered into the form.

  3. Preparing the HTTP POST request: Within the procedure, an HTTP POST request is created. This request contains all the necessary information to register a new contact in HubSpot.

  4. Sending the request: The prepared request is sent to HubSpot via the secure API connection protected by HubSpot's authentication system.

  5. Entering the new contact: After the request has been successfully processed by HubSpot, the new contact is systematically added to HubSpot CRM. This completes the process and the contact is now part of the CRM system.

This entire process ensures that the data provided by users is integrated efficiently and securely into the HubSpot CRM.


Reviewing a newly created contact in HubSpot

After a new contact has been successfully created through the Oracle APEX frontend, the user can easily verify the integration and functionality:

  1. Sign in to HubSpot: First, the user logs into their HubSpot account.

  2. Access to the CRM module: Navigate in the HubSpot dashboard to the CRM module, which is central for managing contact data.

  3. Opening the contacts overview: Within the CRM module you will find the contacts overview. Here you can view all existing contacts.

  4. Finding the new contact: The new contact created via Oracle APEX should now be visible in the list. You can search for specific details of the contact to ensure that all information has been transferred correctly.

These steps demonstrate the effective and automated connection between Oracle APEX and HubSpot, enabling seamless contact management.


Mass import of existing contacts from Oracle into HubSpot

If you already have contacts in your Oracle database and want to transfer them to HubSpot, you can do this through an automated method that handles many contacts at once:

  1. Using a SELECT FOR LOOP: Larger data volumes can be processed by integrating a SELECT FOR LOOP around the existing procedure “CREATE_HUBSPOT_CONTACT”. This LOOP runs through all selected records in the Oracle database.

  2. Automating the upload process: For each contact in the LOOP, the procedure is called that sends an HTTP POST request to HubSpot to add the contact there. This happens sequentially for each contact in the loop.

This approach allows you to import a larger number of contacts into HubSpot CRM quickly and without manual effort.


Steps to import contact data from HubSpot into Oracle APEX

After the contacts have been successfully created in HubSpot – in your case six contacts – it is time to test and demonstrate the import mechanism:

  1. Preparing the import process: Make sure that there is a suitable procedure in Oracle APEX to retrieve the data from HubSpot.

  2. Implementation of the import: Activate the procedure to import the data from HubSpot into your Oracle APEX database.

  3. Checking the imported data: After the data has been imported into Oracle APEX, verify that all information has been transferred correctly and completely. Compare the data in Oracle APEX with the original data in HubSpot to ensure there are no discrepancies.

By implementing these steps, you can ensure that the data import from HubSpot works correctly and that the synchronization between the two systems runs effectively and error-free.


Setting up an Oracle database table for HubSpot contact data

To effectively integrate the contacts imported from HubSpot into your Oracle database, it is crucial to prepare a suitable table. This table serves as a central place for storing and organizing the imported data:

CREATE TABLE HUBSPOT_CONTACTS
(
    ID VARCHAR2(100),
    CREATEDATE VARCHAR2(255),
    EMAIL VARCHAR2(255),
    FIRSTNAME VARCHAR2(255),
    LASTNAME VARCHAR2(255),
    COMPANY VARCHAR2(255),
    PHONE VARCHAR2(255),
    WEBSITE VARCHAR2(255),
    ADDRESS VARCHAR2(255),
    CITY VARCHAR2(255),
    STATE VARCHAR2(255),
    ZIP VARCHAR2(100),
    COUNTRY VARCHAR2(255),
    JOB_TITLE VARCHAR2(255),
    LINKEDIN_BIO CLOB,
    TWITTER_HANDLE VARCHAR2(255),
    HS_OBJECT_ID VARCHAR2(100),
    LASTMODIFIEDDATE VARCHAR2(255),
    ARCHIVED VARCHAR2(100),
    CREATED_ON TIMESTAMP(6) DEFAULT SYSDATE
);


With a well-designed table in your Oracle database, you can efficiently manage the contacts imported from HubSpot.

Creating a Classic Report in Oracle APEX for HubSpot Contact Data

After you have successfully created the hubspot_contacts table in your Oracle database, you can set up a Classic Report in Oracle APEX to display all existing contacts. To do this, you will need a simple SQL script that queries all relevant data from the table. Follow these steps to create the report:

select ID,
       CREATEDATE,
       EMAIL,
       FIRSTNAME,
       LASTNAME,
       COMPANY,
       PHONE,
       WEBSITE,
       ADDRESS,
       CITY,
       STATE,
       ZIP,
       COUNTRY,
       JOB_TITLE,
       LINKEDIN_BIO,
       TWITTER_HANDLE,
       HS_OBJECT_ID,
       LASTMODIFIEDDATE,
       ARCHIVED,
       to_Char(CREATED_ON, 'DD.MM.YYYY HH24:MI:SS') as CREATED_ON
  from HUBSPOT_CONTACTS  
  order by CREATED_ON DESC

By creating this Classic Report in Oracle APEX, you and your team members will get a clear and effective overview of the contacts stored in the database.


Extending your Oracle APEX page with additional features

After the hubspot_contacts table has been successfully created in your Oracle database, you can extend your Oracle APEX page to not only display the existing contacts, but also enable additional interactions. This includes importing data from HubSpot and deleting data in the APEX table:

  1. Add Classic Report: As already described, add a Classic Report to visualize all data from the hubspot_contacts table.

  2. Add "Import from HubSpot" button

    • Placement of the button: Add the "Import from HubSpot" button above or below the Classic Report. This button allows users to import new data directly from HubSpot.
    • Define Action: Link the button to an action that triggers a procedure or process in APEX to retrieve data from HubSpot and import it into the hubspot_contacts table.
  3. Add "Clear Apex Table" button

    • Placement of the button: This button should also be placed near the report and allows you to delete all data in the hubspot_contacts table.
    • Define Action: The button should be linked to a SQL script that deletes all entries in the table.
      sql
      DELETE FROM hubspot_contacts;
    • Add confirmation dialog: To avoid accidental deletion, it is advisable to include a confirmation dialog that appears before the deletion process is carried out.

By adding these buttons, you increase the interactivity of your Oracle APEX page and provide users with convenient tools for managing the data in the hubspot_contacts table.


Preparing your Oracle APEX page for initial display
At this point, the APEX frontend page will still appear empty because no contacts have been imported yet.

Setting up the “Import from Hubspot” process on the APEX side

Next, we set up a process on the APEX side that is activated by the "Import from Hubspot" button. This process executes a PL/SQL call that communicates directly with the HubSpot API to retrieve contact data. When the user clicks "Import from Hubspot," the process initiates the retrieval of the contact information and its import into our Oracle database. This ensures a seamless transfer of the data directly into the system and enables efficient updating of our contact list.

DECLARE
    l_ws_response_clob CLOB;
    l_ws_url VARCHAR2(500) := 'https://api.hubapi.com/crm/v3/objects/contacts';
BEGIN

    apex_web_service.g_request_headers(1).name := 'Accept';
    apex_web_service.g_request_headers(1).value := 'application/json; charset=utf-8';
    apex_web_service.g_request_headers(2).name := 'Content-Type';
    apex_web_service.g_request_headers(2).value := 'application/json; charset=utf-8';
    apex_web_service.g_request_headers(3).name := 'Authorization';
    apex_web_service.g_request_headers(3).value := 'Bearer APEX-API-TOKEN';

    l_ws_response_clob := apex_web_service.make_rest_request(p_url => l_ws_url, p_http_method => 'GET');

    FOR r IN (
        SELECT j.*
        FROM JSON_TABLE(
            l_ws_response_clob, '$.results[*]'
            COLUMNS (
                id VARCHAR2(100) PATH '$.id',
                createdate VARCHAR2(255) PATH '$.properties.createdate',
                email VARCHAR2(255) PATH '$.properties.email',
                firstname VARCHAR2(255) PATH '$.properties.firstname',
                lastname VARCHAR2(255) PATH '$.properties.lastname',
                company VARCHAR2(255) PATH '$.properties.company',
                phone VARCHAR2(255) PATH '$.properties.phone',
                website VARCHAR2(255) PATH '$.properties.website',
                address VARCHAR2(255) PATH '$.properties.address',
                city VARCHAR2(255) PATH '$.properties.city',
                state VARCHAR2(255) PATH '$.properties.state',
                zip VARCHAR2(100) PATH '$.properties.zip',
                country VARCHAR2(255) PATH '$.properties.country',
                job_title VARCHAR2(255) PATH '$.properties.jobtitle',
                linkedin_bio CLOB PATH '$.properties.linkedinbio',  
                twitter_handle VARCHAR2(255) PATH '$.properties.twitterhandle',  
                hs_object_id VARCHAR2(100) PATH '$.properties.hs_object_id',
                lastmodifieddate VARCHAR2(255) PATH '$.properties.lastmodifieddate',
                archived VARCHAR2(10) PATH '$.archived'
            )
        ) j
    )
    LOOP
        INSERT INTO hubspot_contacts (id, createdate, email, firstname, lastname, company, phone, website, address, city, state, zip, country, job_title, linkedin_bio, twitter_handle, hs_object_id, lastmodifieddate, archived)
        VALUES (r.id, r.createdate, r.email, r.firstname, r.lastname, r.company, r.phone, r.website, r.address, r.city, r.state, r.zip, r.country, r.job_title, r.linkedin_bio, r.twitter_handle, r.hs_object_id, r.lastmodifieddate, r.archived);
    END LOOP;
END;


Procedure if wallet is connected.

DECLARE
    l_response UTL_HTTP.REQ;
    l_resp UTL_HTTP.RESP;
    l_ws_response_clob CLOB;
    l_ws_url VARCHAR2(500) := 'https://api.hubapi.com/crm/v3/objects/contacts';
BEGIN

      -- Wallet- und HTTPS-Konfiguration
    UTL_HTTP.set_wallet('file:/opt/oracle/oradata/wallet/hubapi', 'Sajjad786');

     -- Request initialisieren
    l_response := UTL_HTTP.begin_request(l_ws_url, 'GET', UTL_HTTP.HTTP_VERSION_1_1);

        -- Header für JSON und Authorization setzen
    UTL_HTTP.set_header(l_response, 'Content-Type', 'application/json; charset=utf-8');
    UTL_HTTP.set_header(l_response, 'Authorization', 'Bearer APEX-API-TOKEN');

        -- Response erhalten
    l_resp := UTL_HTTP.get_response(l_response);

        -- Response-Body lesen
    UTL_HTTP.read_text(l_resp, l_ws_response_clob);
    UTL_HTTP.end_response(l_resp);

    FOR r IN (
        SELECT j.*
        FROM JSON_TABLE(
            l_ws_response_clob, '$.results[*]'
            COLUMNS (
                id VARCHAR2(100) PATH '$.id',
                createdate VARCHAR2(255) PATH '$.properties.createdate',
                email VARCHAR2(255) PATH '$.properties.email',
                firstname VARCHAR2(255) PATH '$.properties.firstname',
                lastname VARCHAR2(255) PATH '$.properties.lastname',
                company VARCHAR2(255) PATH '$.properties.company',
                phone VARCHAR2(255) PATH '$.properties.phone',
                website VARCHAR2(255) PATH '$.properties.website',
                address VARCHAR2(255) PATH '$.properties.address',
                city VARCHAR2(255) PATH '$.properties.city',
                state VARCHAR2(255) PATH '$.properties.state',
                zip VARCHAR2(100) PATH '$.properties.zip',
                country VARCHAR2(255) PATH '$.properties.country',
                job_title VARCHAR2(255) PATH '$.properties.jobtitle',
                linkedin_bio CLOB PATH '$.properties.linkedinbio',  
                twitter_handle VARCHAR2(255) PATH '$.properties.twitterhandle',  
                hs_object_id VARCHAR2(100) PATH '$.properties.hs_object_id',
                lastmodifieddate VARCHAR2(255) PATH '$.properties.lastmodifieddate',
                archived VARCHAR2(10) PATH '$.archived'
            )
        ) j
    )
    LOOP
        INSERT INTO hubspot_contacts (id, createdate, email, firstname, lastname, company, phone, website, address, city, state, zip, country, job_title, linkedin_bio, twitter_handle, hs_object_id, lastmodifieddate, archived)
        VALUES (r.id, r.createdate, r.email, r.firstname, r.lastname, r.company, r.phone, r.website, r.address, r.city, r.state, r.zip, r.country, r.job_title, r.linkedin_bio, r.twitter_handle, r.hs_object_id, r.lastmodifieddate, r.archived);
    END LOOP;
END;


Setting up the “Clear Apex Table” process on the APEX side

In addition, we establish a process that takes care of deleting the imported records in our Oracle database. This process is activated by the "Clear Apex Table" button. Clicking this button will remove all entries from the hubspot_contacts table.

delete from HUBSPOT_CONTACTS;

Designing the page layout in Oracle APEX Page Designer

After completing the configuration, the layout of your APEX page in the Page Designer should look like this:


Automatic import of contact data using the 'Import from Hubspot' button

When the 'Import from Hubspot' button is pressed in your Oracle APEX application, the following process should be triggered automatically:

  1. Initiating the import process

  2. Data query at HubSpot

  3. Data transfer and storage

This automated process ensures that contacts are transferred quickly and error-free from HubSpot to your Oracle APEX application.


Access to the test area and download of the application

The entire setup of your Oracle APEX application can be fully tested in the APEX Workspace. You can access the testing area via the following link:


For additional flexibility and further analysis, both the application itself and all associated SQL and PL/SQL scripts are available for download. These resources allow you to set up, customize and test the application and scripts in detail in your own environment.

By accessing these materials, you can not only verify functionality in test mode, but also ensure that the integration and data processing meet your specific requirements.



Please Select Embedded Mode To Show The Comment System.*

Previous Post Next Post

نموذج الاتصال