HubSpot integration using REST API in Oracle APEX
Part 1: https://youtu.be/xNh7f8fOODc
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:
- Creating a Token in HubSpot: Learn how to generate an authentication token in HubSpot, which is essential for API access.
- 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.
- 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.
- 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:
|
|
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:
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:
- Click on “Create app” or “Add new app”.
- 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.
|
|
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:
- Enabling all permissions: Make sure you can fully test all API functionality.
- 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.
|
|
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:
|
|
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.
- 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:
- 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.
Creating a new page: In the menu, go to the option to create a new page.
Adding a button: On this new page, add a button. Configure the button so that it opens a pop-up window when clicked.
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:
setting up the window: Ensures that the window is set as page number 10 and opens automatically when the button is pressed.
Adding the input fields: Add the necessary fields to capture the data you want to save later in HubSpot.
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.
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.
Procedure if wallet is connected.
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:
Activation of the procedure: The procedure “CREATE_HUBSPOT_CONTACT” is started.
Data collection: First, the procedure collects all the data that the user has entered into the form.
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.
Sending the request: The prepared request is sent to HubSpot via the secure API connection protected by HubSpot's authentication system.
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:
Sign in to HubSpot: First, the user logs into their HubSpot account.
Access to the CRM module: Navigate in the HubSpot dashboard to the CRM module, which is central for managing contact data.
Opening the contacts overview: Within the CRM module you will find the contacts overview. Here you can view all existing contacts.
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:
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.
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:
Preparing the import process: Make sure that there is a suitable procedure in Oracle APEX to retrieve the data from HubSpot.
Implementation of the import: Activate the procedure to import the data from HubSpot into your Oracle APEX database.
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:
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:
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:
Add Classic Report: As already described, add a Classic Report to visualize all data from the hubspot_contacts table.
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.
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.
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.
Procedure if wallet is connected.
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.
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:
Initiating the import process
Data query at HubSpot
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.