How DO I Create a REST API for ORACLE DB Access

[BillSut]

I've been tasked with creating a REST API to be used to connect to an Oracle Cloud DB and retrieve data from a single table and store it in a data set.  I have a lot of programming experience but I'm new to SAS.  I also do not have access to VIYA so it will have to be old school code.  I'm using SAS 9.4.  Do you know of any place I could find some examples for this.  I would greatly appreciate any help.  Thanks in advance.

[ChrisHemedinger]

Do you mean create an API for others to use, or call an API from Oracle Cloud DB, that fetches data for you to store?

If the latter, then you want to look at PROC HTTP as a REST client and probably the JSON libname engine to format the results into a table. 

[BillSut]

Yes, you are correct.  I posted my question before meeting with the Oracle guy.  I will be using their REST API in Oracle Cloud Services.  I will check out PROC HTTP and the JSON libname engine..  Any code examples where someone has done this would be appreciated.  Thanks for your reply!

[BillSut]

Chris,

I just finished reading your article "How to test PROC HTTP and the JSON library engine" from Jan 2018.  I tried using the sample code and received the below errors.  I'm using SAS 9.4m5 so I'm assuming that the errors are because of the government firewall I'm using.  Do you concur?

ERROR: The TCP/IP tcpSockEstablishSSL() support routine failed with error 10054 (The connection was reset by a peer.).
ERROR: The tcpSockRead call failed. The system error is 'The connection was reset by a peer.'.
ERROR: Call to tcpSockContinueSSL failed.
NOTE: PROCEDURE HTTP used (Total process time):
real time 0.18 seconds
cpu time 0.03 seconds

NOTE: The SAS System stopped processing this step because of errors.

ERROR: Physical file does not exist, D:\1067590410C\_TD9872_TYMXW-F7H8026GL_\now.json.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME.
ERROR: Physical file does not exist, D:\1067590410C\_TD9872_TYMXW-F7H8026GL_\now.json.
ERROR: Error in the LIBNAME statement.
NOTE: Writing HTML Body file: sashtml2.htm
ERROR: Libref TIME is not assigned.

NOTE: Statements not processed because of errors noted above.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.93 seconds
cpu time 0.43 seconds

Thanks.

[AllanBowe]

You might find this product interesting:  https://sas9api.io/

It lets you access your complete SAS 9 estate using REST APIs.  You could create an STP and use it as a REST endpoint.

Other options are SAS Web Services, or calling the Stored Process Web Application directly.  Some papers on that are below.

https://support.sas.com/resources/papers/proceedings17/1091-2017.pdf
https://www.rawsas.com/building-web-apps-with-sas/
https://www.linkedin.com/pulse/5-tips-sas-app-developers-allan-bowe/
http://support.sas.com/resources/papers/proceedings17/1372-2017.pdf (apps for enterprise)

Build an app in 5 minutes: https://vimeo.com/349223867

[BillSut]

Thank you for the information.  I will check out the links you provided.