--- title: Integrating NetSuite with Oracle APEX description: I recently worked on a project which involved generating a sales dashboard in APEX, for which the source data resided in NetSuite. There is a fair amount of documentation out there that deals with NetSuite APIs as well as, of course, restful services in Oracle, but a definitive guide to combining the two was not immediately forthcoming. I thought it would be useful to share my findings on this subject. image: https://content.dsp.co.uk/hubfs/APEX_NETSUITE_IMAGE2.png --- [![DSP From Ground to Cloud](https://content.dsp.co.uk/hubfs/Logos/DSP%20From%20Ground%20to%20Cloud/DSP-Logo-Colour-From-Ground-to-Cloud.svg "DSP From Ground to Cloud")](https://dsp.co.uk) [![DSP From Ground to Cloud](https://content.dsp.co.uk/hs-fs/hubfs/2023/Logos/DSP/DSP-Logo-2019-White-600px.png?width=70&height=72&name=DSP-Logo-2019-White-600px.png "DSP From Ground to Cloud")](https://dsp.co.uk) - Services - Consulting Services - [Oracle](https://www.dsp.co.uk/oracle-consultancy) - [Microsoft](https://www.dsp.co.uk/sql-server-consultancy) - [Google](https://www.dsp.co.uk/google-cloud-consultancy) - [Architecture](https://www.dsp.co.uk/solution-design-and-architecture) - Managed Services - [Oracle Managed Services](https://www.dsp.co.uk/oracle-managed-services) - [SQL Server Managed Services](https://www.dsp.co.uk/sql-server-managed-services) - [OCI Managed Services](https://www.dsp.co.uk/oracle-cloud-infrastructure-managed-services) - [Azure Managed Services](https://www.dsp.co.uk/azure-managed-services) - [On Premise Apps Managed Services](https://www.dsp.co.uk/application-managed-services) - [Cloud Apps Managed Services](https://www.dsp.co.uk/oracle-cloud-applications-services) - Licensing and SAM - [Oracle Licensing](https://www.dsp.co.uk/oracle-licensing-services) - [Software Asset Management](https://www.dsp.co.uk/oracle-software-asset-management-sam) - Application Services - [Application Development](https://www.dsp.co.uk/software-development) - [Application Modernisation](https://www.dsp.co.uk/legacy-software-modernisation) - [Oracle Forms to Oracle APEX](https://www.dsp.co.uk/oracle-forms-to-oracle-apex) - [Application Extensions](https://www.dsp.co.uk/software-customisation) - [Archiving Solutions](https://www.dsp.co.uk/application-archiving-solution) - [Application Hosting](https://www.dsp.co.uk/oracle-apex-hosting-services) - [Development Training](https://www.dsp.co.uk/oracle-apex-training) - Data Science - [AI Strategy](https://www.dsp.co.uk/ai-strategy) - [Responsible AI and Governance](https://www.dsp.co.uk/responsible-ai) - [Agentic AI](https://www.dsp.co.uk/agentic-ai) - [Data Management for AI](https://www.dsp.co.uk/data-management) - [Power BI](https://www.dsp.co.uk/power-bi-services) - [Microsoft Fabric](https://www.dsp.co.uk/microsoft-fabric-services) - [Machine Learning](https://www.dsp.co.uk/oci-machine-learning) - Cloud Migration and Management - [Cloud Readiness and Design](https://www.dsp.co.uk/oracle-cloud-readiness-assessment) - [Migration to Fusion and Cloud Applications](https://www.dsp.co.uk/oracle-cloud-application-modernisation) - [Migration to Cloud Infrastructure](https://www.dsp.co.uk/oracle-cloud-migration) - [Private Cloud](https://www.dsp.co.uk/oracle-private-cloud-services) - [Database@ Hyperscaler](https://www.dsp.co.uk/multicloud-management) - [FinOps and Optimisation](https://www.dsp.co.uk/optimise-oracle-cloud-infrastructure) - Integration and APIs - [API Management](https://www.dsp.co.uk/api-development-services) - [GoldenGate](https://www.dsp.co.uk/oracle-goldengate-services) - Disaster Recovery and Security - [Disaster Recovery](https://www.dsp.co.uk/oracle-database-disaster-recovery-solutions) - [Security Compliance](https://www.dsp.co.uk/data-security) - [Cloud Security](https://www.dsp.co.uk/cloud-security)   ![Oracle Partner, Microsoft Partner and Google Partner](https://content.dsp.co.uk/hubfs/logo-2021/Partner-logos/multiple-vendor/rectangle-2022/Partner-logos-rectangle-Nov21.svg) #### Getting to know us - [About us](https://www.dsp.co.uk/about) - [What we do](https://www.dsp.co.uk/what-we-do) - [Leadership](https://www.dsp.co.uk/dsp-product-leads) - [Careers](https://www.dsp.co.uk/careers) - Technologies - Oracle Cloud Infrastructure - [Oracle Cloud Infrastructure](https://www.dsp.co.uk/oracle-cloud-infrastructure-services) - [IaaS](https://www.dsp.co.uk/iaas) - [PaaS](https://www.dsp.co.uk/paas-security) - Oracle Database - [Oracle Database](https://www.dsp.co.uk/oracle-database-consulting) - [Autonomous Database](https://www.dsp.co.uk/oracle-autonomous-database-services) - [MySQL and Heatwave](https://www.dsp.co.uk/mysql-services) - [Oracle APEX](https://www.dsp.co.uk/oracle-apex-services) - Oracle Engineered Systems - [Oracle Exadata](https://www.dsp.co.uk/exadata-database-service-consultancy) - [Oracle Database Appliance](https://www.dsp.co.uk/oracle-database-appliance) - Oracle Applications - [Oracle Fusion and Cloud Applications](https://www.dsp.co.uk/oracle-fusion-services) - [Oracle E-Business Suite](https://www.dsp.co.uk/oracle-ebs-services) - [JD Edwards](https://www.dsp.co.uk/jd-edwards-services) - [Peoplesoft](https://www.dsp.co.uk/oracle-peoplesoft-services) - [Hyperion](https://www.dsp.co.uk/oracle-hyperion-services) - Microsoft - [SQL Server](https://www.dsp.co.uk/sql-server-consultancy) - [Microsoft Azure](https://www.dsp.co.uk/microsoft-azure-services) - [Power BI](https://www.dsp.co.uk/power-bi-services) - [Microsoft Fabric](https://www.dsp.co.uk/microsoft-fabric-services) - Google - [Google Cloud Platform (GCP)](https://www.dsp.co.uk/google-cloud-partner) - Multicloud and Database Hyperscaler - [Oracle Database@Azure](https://www.dsp.co.uk/oracle-databaseazure-partner) - [Oracle Database@GCP](https://www.dsp.co.uk/google-cloud-machine-learning) - [Oracle Database@AWS](https://www.dsp.co.uk/amazon-web-services-aws) - [Oracle Azure Interconnect](https://www.dsp.co.uk/oracle-microsoft-multicloud) - Open Source Database - [PostgreSQL](https://www.dsp.co.uk/postgresql-services) - [MySQL](https://www.dsp.co.uk/mysql-services) - [MariaDB](https://www.dsp.co.uk/migration-from-mariadb-to-mysql) #### Managed Services - [Oracle Database](https://www.dsp.co.uk/oracle-managed-services) - [SQL Server](https://www.dsp.co.uk/sql-server-managed-services) - [MySQL](https://www.dsp.co.uk/mysql-support) #### Application Services - [Oracle Applications](https://www.dsp.co.uk/oracle-application-cloud-migration) - [Oracle APEX](https://www.dsp.co.uk/oracle-apex-services) - [Microsoft](https://www.dsp.co.uk/microsoft-partnership) - [ISV Services](https://www.dsp.co.uk/isv-support-and-services) #### Cloud Services - [Oracle Cloud Infrastructure](https://www.dsp.co.uk/oracle-cloud-services) - [Microsoft Azure](https://www.dsp.co.uk/microsoft-azure-services) - [Google Cloud Platform](https://www.dsp.co.uk/gcp-database) - [Multi-cloud](https://www.dsp.co.uk/oracle-microsoft-multicloud) #### Data Science - [Machine Learning](https://www.dsp.co.uk/machine-learning-consulting-services) - [Business Intelligence](https://www.dsp.co.uk/business-intelligence-services) - [Artificial Intelligence](https://www.dsp.co.uk/artificial-intelligence-consulting-services) #### Consulting Services - [Oracle](https://www.dsp.co.uk/oracle-consultancy) - [Microsoft](https://www.dsp.co.uk/sql-server-consultancy) - [Google](https://www.dsp.co.uk/google-cloud-consultancy) - [Cloud Migration](https://www.dsp.co.uk/cloud-migrations) #### Technology Solutions - [Engineered Systems](https://www.dsp.co.uk/oracle-engineered-systems-partner) - [Data Security](https://www.dsp.co.uk/data-security) - [Database Architecture](https://www.dsp.co.uk/solution-design-and-architecture) - [Disaster Recovery](https://www.dsp.co.uk/database-disaster-recovery) - Industries - [Finance](https://www.dsp.co.uk/finance) - [Healthcare](https://www.dsp.co.uk/healthcare-it-services) - [Travel and Transport](https://www.dsp.co.uk/database-solutions-for-travel-and-transport) - [Higher Education](https://www.dsp.co.uk/higher-education) - [Public Sector](https://www.dsp.co.uk/public-sector) - [Manufacturing](https://www.dsp.co.uk/manufacturing) - [Retail](https://www.dsp.co.uk/retail) - [Software Vendors](https://www.dsp.co.uk/isv-support-and-services) ### Recent case study: #### Oracle EBS Cloud Deployment Consolidating and Migrating assets into Oracle Cloud Infrastructure. [**![Oracle EBS Cloud Deployment](https://content.dsp.co.uk/hs-fs/hubfs/stonewater-logo%20(1).png?width=250&name=stonewater-logo%20(1).png)**](https://www.dsp.co.uk/stonewater)   #### Most Visited Pages - [Oracle Licensing](https://www.dsp.co.uk/oracle-licensing) - [Oracle Cloud Calculator](https://www.oracle-cloud-calculator.com/) - [Oracle Cloud Migration](https://www.dsp.co.uk/oracle-cloud-migration) - [Oracle Exadata Services](https://www.dsp.co.uk/oracle-exadata-services) - [Artificial Intelligence Services](https://www.dsp.co.uk/artificial-intelligence-consulting-services) #### - - [SQL Server Support](https://www.dsp.co.uk/sql-server-support) - [Oracle Support](https://www.dsp.co.uk/oracle-support) - [Google Cloud Consultancy](https://www.dsp.co.uk/google-cloud-consultancy) - [New Application Development](https://www.dsp.co.uk/oracle-apex-services) - [Azure Virtual Desktop](https://www.dsp.co.uk/azure-virtual-desktop) - Resources - [Gartner ](https://www.dsp.co.uk/complimentary-gartner-download) - [ISG](https://www.dsp.co.uk/isg-oracle-report) - [Blogs](https://www.dsp.co.uk/blogs) - [Case Studies](https://www.dsp.co.uk/case-studies) - [Testimonials](https://www.dsp.co.uk/testimonials) - [Events](https://www.dsp.co.uk/events) - [Webinars](https://www.dsp.co.uk/webinars) - [Insights](https://www.dsp.co.uk/insights) - [Technical Resources](https://www.dsp.co.uk/technical-resources) ### DSP-Explorer acquires leading Oracle Applications Managed Services Provider, Claremont, to further extend its data management capabilities.   [![Read More](https://no-cache.hubspot.com/cta/default/3321273/c7e13c9c-65e1-46e7-8541-8da86a4fc305.png)](https://cta-redirect.hubspot.com/cta/redirect/3321273/c7e13c9c-65e1-46e7-8541-8da86a4fc305) #### Resources - [Blogs](https://www.dsp.co.uk/blogs) - [Webinars](https://www.dsp.co.uk/webinars) - [Case Studies](https://www.dsp.co.uk/case-studies) - [Testimonials](https://www.dsp.co.uk/testimonials) - About - [About DSP](https://www.dsp.co.uk/about) - [What We Do](https://www.dsp.co.uk/what-we-do) - [DSP Group Board ](https://www.dsp.co.uk/dsp-group-board) - [Careers](https://www.dsp.co.uk/careers) - [Project Harar](https://www.dsp.co.uk/project-harar) - [Customer Area](https://www.dsp.co.uk/contact-us) - [Customer Support Portal](https://dspgroup-ism.ivanticloud.com/) - [Customer Resources](https://www.dsp.co.uk/welcome) - [Contact us](https://www.dsp.co.uk/contact-us) # Integrating NetSuite with Oracle APEX [ Michael Pickering ](https://content.dsp.co.uk/author/michael-pickering) 07-Apr-2021 10:46:00 ### Contents [03-Nov-2020 12:12:00 APEX Authentication Using Microsoft Azure Face API ](https://content.dsp.co.uk/apex-authentication-using-microsoft-azure-face-api) [11-Jan-2018 10:34:01 ORDS, OAuth2 & Web Services in APEX – Part 3 ](https://content.dsp.co.uk/ordsoauth2-web-services-in-apex-part-3) [17-Jan-2018 10:33:39 Introducing APEX TabLock ](https://content.dsp.co.uk/introducing-apex-tablock) I recently worked on a project which involved generating a sales dashboard in [APEX](https://www.dsp.co.uk/oracle-apex-services), for which the source data resided in NetSuite. There is a fair amount of documentation out there that deals with NetSuite APIs as well as, of course, restful services in Oracle, but a definitive guide to combining the two was not immediately forthcoming. I thought it would be useful to share my findings on this subject. At a high level, I was required to access a number of reports that had been set up in NetSuite as ‘Saved Searches’ and pull the data into Oracle via APEX_WEB_SERVICE, by using a NetSuite Restlet script (in my case, JavaScript that calls a NetSuite saved search and returns the results as a JSON object). The results were either a single figure (e.g. a count of item sales in a given week) or a multi-column list (e.g. items and prices). A pl/sql package handled the conversion of the json results so I could store them in a collection and ultimately access them in APEX using sql. NetSuite allows the use of oAuth1 and oAuth2 authentication. I actually ended up configuring both during the project, so I have shared the method for both below. I won’t go into the details of oAuth1 vs oAuth2 here as there is plenty of info already available, other than to say that while oAuth2 is more secure, it requires a certain level of manual intervention to keep the access tokens alive, which was not entirely practical for the nature of my project. --- Find out how we can help you build solutions using Oracle APEX with our award winning [application development and consultancy services](https://www.dsp.co.uk/oracle-apex-services). [![Book a meeting with an Oracle APEX expert](https://no-cache.hubspot.com/cta/default/3321273/5db1aa70-8710-434d-9e0b-6b1c26541a0c.png)](https://cta-redirect.hubspot.com/cta/redirect/3321273/5db1aa70-8710-434d-9e0b-6b1c26541a0c) --- ### **NetSuite setup** - A User account needs to be set up that represents the data consumer. When logged into this account, the URL will be something like https://12345678.app.netsuite.com/ where 12345678 is the **NetSuite user-id** allocated to the account. This value is used when accessing data via a web service call. - An integration record must be set up for the NetSuite user, that allows oAuth1 token-based authentication and/or oAuth2 access (as required). A **consumer key** and a **consumer secret** will be generated at this point - make sure you take a record of these as they are not accessible after initial creation. - For oAuth1 access and token must be then created against the integration record, a **token key** and **token secret** are generated at this point - make sure you take a record of these as they are not accessible after initial creation. - Generate and deploy a Restlet .js script that will return the desire data. In our case, the code runs a NetSuite Saved Search and returns the results as a json object. It requires a parameter of ‘id’ that represents the id of the saved search (found when viewing saved searches in the front end) (see attached example). This will generate a **script id** and **deploy id**   ### **OAuth1 approach - Postman** In Postman set up a Get request as below: https://.restlets.api.netsuite.com/app/site/hosting/restlet.nl?script=&deploy=&id= [![APEX_NETSUITE_IMAGE1](https://content.dsp.co.uk/hs-fs/hubfs/APEX_NETSUITE_IMAGE1.png?width=949&name=APEX_NETSUITE_IMAGE1.png)](https://content.dsp.co.uk/hubfs/APEX_NETSUITE_IMAGE1.png) [![APEX_NETSUITE_IMAGE2](https://content.dsp.co.uk/hs-fs/hubfs/APEX_NETSUITE_IMAGE2.png?width=941&name=APEX_NETSUITE_IMAGE2.png)](https://content.dsp.co.uk/hubfs/APEX_NETSUITE_IMAGE2.png) [![APEX_NETSUITE_IMAGE3](https://content.dsp.co.uk/hs-fs/hubfs/APEX_NETSUITE_IMAGE3.png?width=725&name=APEX_NETSUITE_IMAGE3.png)](https://content.dsp.co.uk/hubfs/APEX_NETSUITE_IMAGE3.png) Note ‘Realm’ should be the NetSuite user id. This should then return the results of the report in json format, e.g. [![APEX_NETSUITE_IMAGE4](https://content.dsp.co.uk/hs-fs/hubfs/APEX_NETSUITE_IMAGE4.png?width=584&name=APEX_NETSUITE_IMAGE4.png)](https://content.dsp.co.uk/hubfs/APEX_NETSUITE_IMAGE4.png)   ### **OAuth 1 - Oracle **  In order to achieve the same thing via pl/sql, the code needs to replicate what postman does behind the scene with the token data. A unique signature is generated using a combination of the current time, a random ‘Nonce’ string and the token key and secret. The call will only work if the signature is generated properly. For testing purposes, you can use some static values (e.g. for current date/time) in postman and oracle and validate that the signature created is the same (in postman you can view the code of the web service call in jQuery, which shows the signature generated for a request). Correctly generating this signature was by far the most challenging aspect of this configuration, as the process that postman follows to generate it based on it is not transparent. After a lot of googling and trial and error, I was able to generate the same signature out of both systems. ``` function get_data(p_script     IN number,                   p_deploy     IN number,                   p_search_id  IN varchar2) return CLOB IS c_oauth_signature_method CONSTANT VARCHAR2(10) := 'HMAC-SHA1'; c_oauth_version CONSTANT VARCHAR2(5) := '1.0'; v_result                 CLOB; v_script                 NUMBER; v_deploy                 NUMBER; v_search_id              VARCHAR2(32000); v_oauth_consumer_key     VARCHAR2(500); v_oauth_token            VARCHAR2(500); v_oauth_secret           VARCHAR2(500); v_oauth_nonce            VARCHAR2(500); v_oauth_timestamp        VARCHAR2(100); v_oauth_consumer_secret  VARCHAR2(500); v_oauth_header           VARCHAR2(2000); v_random                 varchar2(11); v_oauth_signature        VARCHAR2(1000); v_base_string            VARCHAR2(2000); v_access_user  varchar2(20); begin   -- all parameters are stored in config and retrieved via a lookup function   v_oauth_consumer_key := get_config_char_value(p_subtype => 'CLIENT_ID',                    p_type => 'AUTH');   v_oauth_consumer_secret := get_config_char_value(p_subtype  => 'CLIENT_SECRET', p_type => 'AUTH');   v_oauth_token := get_config_char_value(p_subtype => 'TOKEN', p_type => 'AUTH'); v_oauth_secret := get_config_char_value(p_subtype => 'TOKEN_SECRET', p_type => 'AUTH'); v_access_user := get_config_char_value(p_subtype => 'ACCESS_USER', p_type => 'AUTH'); -- RANDOM oauth_nonce SELECT dbms_random.string('A', 11) INTO v_random FROM DUAL; SELECT UTL_ENCODE.base64_encode(UTL_I18N.string_to_raw(v_random, 'AL32UTF8')) INTO v_oauth_nonce FROM DUAL; SELECT (SYSDATE - TO_DATE('01-01-1970', 'DD-MM-YYYY')) * (86400) INTO v_oauth_timestamp FROM DUAL; v_oauth_timestamp := substr(v_oauth_timestamp, 1, 10); v_base_string := 'GET' || chr(38) || 'https%3A%2F%2F' || v_access_user || '.restlets.api.netsuite.com%2Fapp%2Fsite%2Fhosting%2Frestlet.nl' || chr(38) || 'deploy%3D' || p_deploy || '%26id%3D' || p_search_id || '%26oauth_consumer_key%3D' || v_oauth_consumer_key || '%26oauth_nonce%3D' || v_oauth_nonce || '%26oauth_signature_method%3D' || c_oauth_signature_method || '%26oauth_timestamp%3D' || v_oauth_timestamp || '%26oauth_token%3D' || v_oauth_token || '%26oauth_version%3D' || c_oauth_version || '%26script%3D' || p_script; --generate the signature select UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode( dbms_crypto.mac( UTL_I18N.STRING_TO_RAW(v_base_string,'AL32UTF8') ,DBMS_CRYPTO.HMAC_SH1 ,UTL_I18N.STRING_TO_RAW(v_oauth_consumer_secret ||chr(38) || v_oauth_secret, 'AL32UTF8')))) into v_oauth_signature from dual; v_oauth_signature := replace(v_oauth_signature, '=', '%3D'); v_oauth_signature := replace(v_oauth_signature, '/', '%2F'); v_oauth_signature := replace(v_oauth_signature, '+', '%2B'); v_oauth_header := 'OAuth ' || 'realm="' || v_access_user || '"' || ', ' || 'oauth_consumer_key="' || v_oauth_consumer_key || '", ' || 'oauth_token="' || v_oauth_token || '", ' || 'oauth_signature_method="' || c_oauth_signature_method || '", ' || 'oauth_timestamp="' || v_oauth_timestamp || '", ' || 'oauth_nonce="' || v_oauth_nonce || '", ' || 'oauth_version="' || c_oauth_version || '", ' || 'oauth_signature="' || v_oauth_signature || '"'; APEX_WEB_SERVICE.g_request_headers.delete(); 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 := v_oauth_header; v_result := APEX_WEB_SERVICE.make_rest_request(p_url=> 'https://' || v_access_user ||'.restlets.api.netsuite.com/app/site/hosting/restlet.nl?script=' || p_script ||chr(38) ||'deploy=' ||p_deploy ||chr(38) ||'id=' ||p_search_id, p_http_method => 'GET' ); RETURN v_result; end; ```   ### **OAuth 2 Postman** To get the initial token. 1. execute the following in browser https://.app.netsuite.com/app/login/oauth2/authorize.nl?scope=restlets&redirect_uri=https://localhost&response_type=code&client_id= 1. You should then be taken to a website that lets you click ok to authorise the application 2. The result may be a page not found error next, but the URL should now have a code in it like: 44957bb819f680f74522f7123500f6faa1625bbasd123123120e179f4d11420eb7ae 1. In postman, do a post request, auth type = basic (enter consumer id and secret), content type = application/x-www-form-urlencoded. Add the following params into body: code redirect_uri grant_type = authorization_code [![APEX_NETSUITE_IMAGE5](https://content.dsp.co.uk/hs-fs/hubfs/APEX_NETSUITE_IMAGE5.png?width=1086&name=APEX_NETSUITE_IMAGE5.png)](https://content.dsp.co.uk/hubfs/APEX_NETSUITE_IMAGE5.png) [![APEX_NETSUITE_IMAGE6](https://content.dsp.co.uk/hs-fs/hubfs/APEX_NETSUITE_IMAGE6.png?width=1006&name=APEX_NETSUITE_IMAGE6.png)](https://content.dsp.co.uk/hubfs/APEX_NETSUITE_IMAGE6.png) 1. Executing this will give you a bearer token and a refresh token e.g. access_token {     "access_token": "xxxxx",     "refresh_token": "xxxx",     "expires_in": "3600",     "token_type": "Bearer" } 1. You can then use the access token every time you call the service, to get you a valid refresh token to use via postman/apex_web_services: The refresh token can then be used to access the data in the same way as with oAuth1, except the authorisation type is ‘Bearer Token’ (to which you enter the refresh token from above). [![APEX_NETSUITE_IMAGE1](https://content.dsp.co.uk/hs-fs/hubfs/APEX_NETSUITE_IMAGE1.png?width=949&name=APEX_NETSUITE_IMAGE1.png)](https://content.dsp.co.uk/hubfs/APEX_NETSUITE_IMAGE7.png) [![APEX_NETSUITE_IMAGE8](https://content.dsp.co.uk/hs-fs/hubfs/APEX_NETSUITE_IMAGE8.png?width=1071&name=APEX_NETSUITE_IMAGE8.png)](https://content.dsp.co.uk/hubfs/APEX_NETSUITE_IMAGE8.png) ### **OAuth 2 Oracle** In oracle you must recreate this process to get the refresh token first – effectively this needs to happen every call as the refresh token expires after 60 mins. Note: The initial access token expires after 7 days and must be regenerated. ``` function get_refresh_token RETURN varchar2 IS v_result         CLOB; v_bearer         VARCHAR2(32000); v_username       VARCHAR2(32000); v_password       VARCHAR2(32000); v_refresh_token  VARCHAR2(32000); v_access_user    VARCHAR2(20); BEGIN v_username := get_config_char_value(p_subtype => 'CLIENT_ID', p_type => 'AUTH'); v_password := get_config_char_value(p_subtype => 'CLIENT_SECRET', p_type => 'AUTH'); v_refresh_token := get_config_char_value(p_subtype => 'REFRESH_TOKEN', p_type => 'AUTH'); v_access_user := get_config_char_value(p_subtype => 'ACCESS_USER', p_type => 'AUTH'); APEX_WEB_SERVICE.g_request_headers.delete(); APEX_WEB_SERVICE.g_request_headers(1).name := 'Content-Type'; APEX_WEB_SERVICE.g_request_headers(1).value := 'application/x-www-form-urlencoded'; v_result := APEX_WEB_SERVICE.make_rest_request(p_url => 'https://' || v_access_user || '.suitetalk.api.netsuite.com/services/rest/auth/oauth2/v1/token', p_http_method => 'POST', p_username => v_username, p_password => v_password, p_parm_name => APEX_util.string_to_table('grant_type:refresh_token'), p_parm_value => APEX_util.string_to_table('refresh_token:' || v_refresh_token)); apex_json.parse(v_result); v_bearer := apex_json.get_varchar2(p_path => 'access_token'); RETURN v_bearer; END; function ws_get_data(p_key        IN CLOB,                      p_script     IN number,                      p_deploy     IN number,                      p_search_id  IN varchar2) return CLOB IS v_result CLOB; v_access_user varchar2(20); begin v_access_user := get_config_char_value(p_subtype => 'ACCESS_USER', p_type => 'AUTH'); APEX_WEB_SERVICE.g_request_headers.delete(); 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 ' || p_key; -- Get the XML response from the web service. v_result := APEX_WEB_SERVICE.make_rest_request(p_url => 'https://' || v_access_user || '.restlets.api.netsuite.com/app/site/hosting/restlet.nl?script=' || p_script || chr(38) || 'deploy=' || p_deploy || chr(38) || 'id=' || p_search_id, p_http_method => 'GET' ); RETURN v_result; end; ``` ### **Summary** The NetSuite API is really useful for accessing report data. Once you get through the intricacies of the web services calls, you can start embedding the data into your [APEX application](https://www.dsp.co.uk/oracle-apex-application-development). --- **Need some help with your APEX applications? Speak to the experts today.**   **[![Book a meeting with an Oracle APEX expert](https://no-cache.hubspot.com/cta/default/3321273/5db1aa70-8710-434d-9e0b-6b1c26541a0c.png)](https://cta-redirect.hubspot.com/cta/redirect/3321273/5db1aa70-8710-434d-9e0b-6b1c26541a0c)** --- **Author**: [Michael Pickering](https://content.dsp.co.uk/apex/author/michael-pickering) **Job Title**: Oracle Development Consultant **Bio**: Michael is a Development Consultant at DSP. Having attained an MSc in Computer-Based Information Systems, he began his career developing healthcare reporting solutions, before moving to specialise in Oracle Forms and Reports in the construction industry. Here at DSP, he is part of a highly skilled development team providing APEX development solutions and training to both UK and international businesses. ---   [API](https://content.dsp.co.uk/topic/api) [Data Visualization](https://content.dsp.co.uk/topic/data-visualization) [APEX](https://content.dsp.co.uk/topic/apex) [OAuth](https://content.dsp.co.uk/topic/oauth) [NetSuite](https://content.dsp.co.uk/topic/netsuite) [03-Nov-2020 12:12:00 APEX Authentication Using Microsoft Azure Face API ](https://content.dsp.co.uk/apex-authentication-using-microsoft-azure-face-api) [11-Jan-2018 10:34:01 ORDS, OAuth2 & Web Services in APEX – Part 3 ](https://content.dsp.co.uk/ordsoauth2-web-services-in-apex-part-3) [17-Jan-2018 10:33:39 Introducing APEX TabLock ](https://content.dsp.co.uk/introducing-apex-tablock) [Previous Why you should consider moving from CentOS to Oracle Linux ](https://content.dsp.co.uk/why-you-should-consider-moving-from-centos-to-oracle-linux) [Next AI, Machine Learning, Cloud and the Legal Sector ](https://content.dsp.co.uk/ai-ml-cloud-and-the-legal-sector) #### Contact Sales Enquiries [+44 (0) 203 880 1686](tel:+4420%2038801686) Technical Support +44 (0) 330 058 8367 [enquiries@dsp.co.uk](mailto:enquiries@dsp.co.uk) #### Follow us #### Office Locations [**London (Head Office)**](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,198m/data=!3m2!1e3!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MTAwOC4wIKXMDSoASAFQAw%3D%3D) [Chancery House](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,19z/data=!3m1!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MDkyOS4wIKXMDSoASAFQAw%3D%3D) [53-64 Chancery Lane](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,19z/data=!3m1!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MDkyOS4wIKXMDSoASAFQAw%3D%3D) [London](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,19z/data=!3m1!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MDkyOS4wIKXMDSoASAFQAw%3D%3D) [WC2A 1QS](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,19z/data=!3m1!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MDkyOS4wIKXMDSoASAFQAw%3D%3D) [**Leeds** Richmond House, Lawnswood Business Park, Leeds, LS16 6QY](https://www.google.com/maps/dir/53.8405223,-1.6062773/DSP-Explorer/@53.8404788,-1.6061783,17z/data=!4m9!4m8!1m1!4e1!1m5!1m1!1s0x4879591e5d204ac9:0x4e41b926d72adca!2m2!1d-1.6064847!2d53.8404842) [**Derby**](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) [Cubo Pride Park,](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) [1 Pride Place](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) [Derby](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) [DE24 8QR](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) ** [Dublin](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu)** [Inniscarra,](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu) [Main Street,](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu) [Rathcoole,](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu) [Dublin](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu) #### About Us DSP is a Data Management and Cloud Platform MSP that delivers enterprise grade support & consulting services for Oracle, Microsoft and Multi-Cloud technologies. ![Oracle Partner](https://content.dsp.co.uk/hubfs/logo-2021/Partner-logos/Oracle-logos/Colour/o-prtnr-cmyk-250.png) ![Microsoft Partner](https://content.dsp.co.uk/hubfs/Microsoft%20partner%20logos%20-%20July%202023/mic-sol-part-colour@200x.png) ![Google Cloud Partner](https://content.dsp.co.uk/hubfs/logo-2021/Partner-logos/GCP-Logos/Google-Cloud-Partner%20(1).svg) Registered Office: 30 City Road, London, EC1Y 2AB. Company Registration Number: 03898451 ```json { "@context" : "https://schema.org", "@type" : "BlogPosting", "dateModified" : "2024-10-01", "datePublished" : "2024-10-01", "headline" : "Integrating NetSuite with Oracle APEX", "image" : [ "https://www.dsp.co.uk/hubfs/APEX_NETSUITE_IMAGE2.png" ], "mainEntityOfPage" : { "@id" : "https://content.dsp.co.uk/integrating-netsuite-with-oracle-apex", "@type" : "WebPage" } } ```