Managing Oracle Database with REST APIs and ADB Built-in Tools

In this episode, Lois Houston and Nikita Abraham are joined by Cloud Engineer Nick Commisso to talk about managing Oracle Database with REST APIs.   They also look at Autonomous Database built-in tools, which are pre-assembled, pre-configured, and pre-deployed, delivering a consistent user experience.   Oracle MyLearn: https://mylearn.oracle.com/   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   Twitter: https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, Deepak Modi, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00;00;00;00 - 00;00;39;06 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let's get started! Hello and welcome to the Oracle University Podcast. I'm Lois Houston, Director of Product Innovation and Go to Market Programs with Oracle University. And with me is Nikita Abraham, Principal Technical Editor.   00;00;39;12 - 00;01;04;12 Hello again! Last week, we discussed Oracle Cloud Infrastructure’s Maximum Availability Architecture. And in today's episode, we'll talk about managing Oracle Database with REST APIs and also look at Autonomous Database built-in tools with our Cloud Engineer Nick Commisso. Hi Nick, thanks for being back on the podcast. What is Oracle REST Data Services? What do you use it for?   00;01;04;14 - 00;01;31;07 Oracle is not just a relational database anymore. And the REST APIs can be deployed with Oracle REST Data Services or ORDS to handle all of these data format models. And you can use ORDS for application development and accessing the data and can be used as a powerful tool for automating management, lifecycle, provisioning, and data-dictionary-type use.   00;01;31;09 - 00;02;02;02 Oracle Cloud offers full REST APIs for DBAs and developers who would prefer to interact with Oracle Autonomous Database Cloud services programmatically over REST rather than log in to the cloud console and click through screens. This provides a mechanism for developing customized deployment and management scripts that can be saved and reused for deployments, setting gold standards, and storing entire application infrastructure stacks as version-controlled code.   00;02;02;08 - 00;02;35;15 I think before we move on, it's important to clarify. For anyone who doesn't already know, what is REST? How do Oracle Cloud Infrastructure APIs use REST and HTTPS? REST is combined with HTTPS, but is not a protocol. REST is an acronym for Representational Stateless Transfer. The Oracle Cloud Infrastructure APIs are typical REST APIs that use HTTPS requests and responses and support HTTPS and SSL protocol TLS 1.2, the most secure industry standards.   00;02;35;15 - 00;03;18;17 Calls to the Oracle Cloud Infrastructure using REST APIs can be written in popular scripting languages such as Node.js, Python, Ruby, Perl, Java, C#, Bash, or Curl. The way you interact with your data are the API calls via HTTP - GET to access your data and stored procedures. PUT to update your data. POST to insert your data and execute PL/SQL. And DELETE to remove your data. When making an HTTP request with Oracle REST Data Services, how does the process flow from the request to accessing data in the database?   00;03;18;17 - 00;03;46;21 A person, process, or computer gets ready to make an HTTP request. You need to tell the request where the thing or data is, and the request will get into the web tier where ORDS is running. ORDS then translates the REST request to a SQL statement and accesses the table to get the information requested.   00;03;46;24 - 00;04;13;11 The result normally comes back as a JSON, but can also return an HTML, binary, and CSV. With all of these requests, a collection of connections to the database or connection pool is used and all of the data might not return, depending on the device asking. The results set up links to get more data, but each each time, this links to get another request through the connection pool.   00;04;13;13 - 00;04;41;06 The default size of the connection pool are 10 and it depends how fast is the database code that's tied to the APIs. But 10 probably isn't enough. Because of the results and connection pooling, it shouldn't be long-running code when using APIs. What is the architecture of Oracle REST Data Services? Can you tell us about the integration with components like Java servlets, Tomcat, WebLogic, and Apache?   00;04;41;08 - 00;05;13;24 Also, how does ORDS enable authentication and access to data in the Oracle database through REST calls? ORDS runs in a Java servlet. Or it can be run within Tomcat or WebLogic for E-Business or Fusion. The request comes into the web server and ORDS handles the request. ORDS is included in your Oracle database license. This is a simplified view of your architecture, but there's normally a load balancer in front of the Apache server to handle the requests coming in.   00;05;13;27 - 00;05;50;09 The REST service is already hooked up into the database. Authentication with the web server and the hooks are there to be accessing the data. The code and the data is already in the database in the APEX apps. And the REST calls allow for you to access the data. It harnesses the Oracle database. In order to manage your database with automation, along with minimal human interaction, you need to use ORDS and the REST APIs that are enabled for database management to provision, control, and monitor the Oracle database.   00;05;50;11 - 00;06;20;22 You need an Oracle database for ORDS to work. ORDS can run anywhere that Oracle can run and is easily plugged into the Oracle Database Management pipeline. What are some key features and functionalities offered by the Oracle Database REST APIs? There are over 600 REST endpoints provided to manage and monitor your Oracle database. These are supported starting from 11gR2 up to the current version of the database.   00;06;20;25 - 00;06;51;14 The REST APIs have general information, data dictionary, monitoring, performance, and lifecycle management. Can you give us some examples of specific details that are accessible through the REST APIs? For performance, there's Top SQL, ASH, and AWR reports. For monitoring, you can look at sessions, locks, waits, and alert logs. Lifecycle will allow you to manage multitenant for provisioning PDBs.   00;06;51;16 - 00;07;16;08 And let's not forget about the data dictionary tables where you can report on objects and database operations. And how do you get started with ORDS? To get started using ORDS, you need to install ORDS. You run the installer and there are configuration files that are also created that can be adjusted later. You need the information about connection to the database where you want ORDS installed.   00;07;16;10 - 00;07;47;08 What goes into the database is the schema, ORDS_METADATA, and a user, ORDS_PUBLIC_USER. Are you attending Oracle CloudWorld 2023? Learn from experts, network with peers, and find out about the latest innovations when Oracle CloudWorld returns to Las Vegas from September 18 through 21. CloudWorld is the best place to learn about Oracle solutions from the people who build and use them.   00;07;47;08 - 00;08;15;02 In addition to your attendance at CloudWorld, your ticket gives you access to Oracle MyLearn and all of the cloud learning subscription content as well as three free certification exam credits. This is valid from the week you register through 60 days after the conference. So, what are you waiting for? Register today. Learn more about Oracle CloudWorld at www.oracle.com/cloudworld.   00;08;15;04 - 00;08;48;14 Welcome back. Let's move on to Oracle's data toolset. Nick, what are the key tools offered by Oracle for data analysis and integration? Oracle Data Integrator or ODI is an enterprise class data integration tool with extract, load, and transform, or ELT architecture. Enterprise Data Quality or EDQ is a sophisticated, powerful tool for profiling, cleaning, and preparing your data.   00;08;48;17 - 00;09;24;14 Analytic views built into Oracle database provides a common framework for defining universally accessible semantic models. Oracle Analytics Cloud, or OAC, is the perfect complement, providing beautiful and insightful analysis of this data. So, how do these tools come together?  For our traditional market, this is a comprehensive and compelling suite of tools. Enterprise class tools for an enterprise class market. With autonomous database, we deliver an integrated platform.   00;09;24;17 - 00;09;47;24 It's not a single tool with the customer left to buy the other tools that we need, nor is it a solution delivered in kit form with the customer left to cobble it all together. It's pre-assembled, preconfigured, and pre-deployed. There is a consistent user experience with built-in best practices. It's like having an expert in a box there to guide you.   00;09;47;26 - 00;10;12;15 Components are defined in the common database layer so that they can be shared by all users in all tools. And the metadata? And all of this metadata is brought together in the catalog. So, it's not just the tools that are integrated, it's the data too, a business model spanning data sources that can be federated when appropriate and defined in a common data catalog, which eliminates silos.   00;10;12;17 - 00;10;49;24 The result is renewed confidence in data lineage and impact analysis. In other words, we have collaboration by design. This built-in collaboration between specialists eliminates silos. For example, hierarchies recognized automatically in the data preparation phase are defined in the database itself, are immediately accessible to the data analysts for aggregation purposes. Additional semantic modeling by the analysts, perhaps defining sophisticated calculations, such as percentage change since last year, and again, defined in the database itself, can be accessed by the data scientist.   00;10;49;27 - 00;11;15;19 This provides a great headstart in developing predictive models that, in turn, can be used by the CRM developer who might want to augment a customer view with the most suitable campaign to discuss during the next meeting. So, autonomous database comes with a sophisticated suite of tools pre-installed.   00;11;15;20 - 00;11;40;10 So, it's basically an open platform with open standards. If you want to speak SQL, speak SQL, so do we. We speak Python too, if that's your preference. Whether your data is in a CSV file or a JSON format, it's going to be comfortably at home in autonomous database. Using the language of your choice, analyze your data using whatever tool you're most comfortable with.   00;11;40;17 - 00;12;00;18 The whole idea is that there should be nothing new to learn. Thanks, Nick, for joining us today. To learn more about ADB built-in tools, head over to mylearn.oracle.com and get started on our free Oracle Cloud Data Management Foundations Workshop. Next week will be our last episode of the season where we’ll look at Oracle Data Lakehouse.   00;12;00;21 - 00;14;47;18 Until then, this is Nikita Abraham and Lois Houston signing off. That's all for this episode of the Oracle University Podcast. If you enjoyed listening, please click Subscribe to get all the latest episodes. We'd also love it if you would take a moment to rate and review us on your podcast app. See you again on the next episode of the Oracle University Podcast.