Using ORDS to Make Your ADB Data Available in VBS

Visual Builder Studio requires its data sources to connect to the webpage it produces using REST calls. Therefore, the data source has to provide a REST interface. A simple, easy, secure, and free way to do that is with Oracle REST Data Services (ORDS).   In this episode, hosts Lois Houston and Nikita Abraham chat with Senior Principal OCI Instructor Joe Greenwald about what ORDS can do, how to easily set it up, how to work with it, and how to use it within Visual Builder Studio.   Develop Fusion Applications Using Visual Builder Studio: https://mylearn.oracle.com/ou/course/develop-fusion-applications-using-visual-builder-studio/122614/   Build Visual Applications Using Visual Builder Studio: https://mylearn.oracle.com/ou/course/build-visual-applications-using-oracle-visual-builder-studio/110035/   Oracle University Learning Community: https://education.oracle.com/ou-community   LinkedIn: https://www.linkedin.com/showcase/oracle-university/   X (formerly Twitter): https://twitter.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00 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. 00:26   Nikita: Hello and welcome to the Oracle University Podcast! I’m Nikita Abraham, Principal Technical Editor with Oracle University, and with me is Lois Houston, Director of Innovation Programs.   Lois: Hi there! In our last episode, we took a look at model-based development tools, their start as CASE tools, what they morphed into, and how they’re currently used in Oracle software development. We’re wrapping up the season with this episode today, which will be about how to access Oracle database data through a REST interface created and managed by Oracle REST Data Services, or ORDS, and how to access this data in Visual Builder Studio.   01:03 Nikita: Being able to access Oracle database data through a REST interface over the web is highly useful, but sometimes it can be complicated to create that interface in a programming language. Joe Greenwald, our Senior OCI Learning Solutions Architect and Principal Instructor is back with us one last time this season to tell us more about ORDS, and how it makes it much simpler and easier for us to REST-enable our database for use in tools like Visual Builder Studio. Hi Joe! Tell us a little about what Visual Builder Studio is and why we must REST-enable our data for VBS to be able to use it.   01:40 Joe: Hi Niki, hi Lois! Ok, so, Visual Builder Studio is Oracle’s low-code software development and project asset management product for creating graphical webpage front-ends for web applications. It’s the tool of choice for designing, building, and implementing all of Oracle Fusion Cloud Applications and is being used by literally tens of thousands of engineers at Oracle now to bring the next generation of Fusion Applications to our customers and the market. It’s based on standards like HTML5, CSS3, and JavaScript. It’s highly performant and combined with the Redwood graphical design system and components that we talked about previously, delivers a world-class experience for users. One thing about Visual Builder Studio though: it only works with data sources that have a REST interface. This is unusual. I like to think I’ve worked with every software development tool that Oracle’s created since I joined Oracle in 1992, including some unreleased ones, and all of them allowed you to talk to the database directly. This is the first time that we’ve released a tool that I know of where we don’t do that. Now at first, I was a little put off and wondered how’s it going to do this and how much work I would have to do to create a REST interface for some simple tables in the Oracle database. Like, here’s one more thing I must do just to create a page that displays data from the database. As it turns out, it’s a wise design decision on the part of the designers. It simplifies the data access parts of Visual Builder Studio and makes the data access model common across the different data sources. And, thanks to ORDS, REST-enabling data in Oracle database couldn’t be easier! 03:13 Lois: That’s cool. We don’t want to focus too much on Visual Builder Studio today. We have free courses that teach you how to create service connections to REST services to access the data and all of that. What we actually want to talk with you about is working with Oracle REST Data Service. How easy is it to work with Oracle REST Data Service to add REST support, what we call REST-enable your Oracle Database, and why it is important?  Nikita: Yeah, I could use a bit of a refresher on REST myself. Could you describe what REST is, how it works for both the client and server, and what ORDS is doing for us? 03:50 Joe: Sure. So, REST is a way to make a request to a server for a resource using the HTTP web protocol from a client, like your browser, to a web server, which hands off the request to code that handles the request and sends the response back to your client/browser, which then uses it, displays or whatever. So, you can see we have two parts. We have the client, which makes the request, and the server, which handles the request and figures out what the response should be (static, dynamic, or a combination of both) and sends that back to the client. For example, a visual application built with Visual Builder Studio acts as a client making the request, just as your browser makes a request. It’s really just a web app built with HTML5, CSS3, and JavaScript, and the JavaScript makes a request to the server on your behalf. Let’s say you wish to access your student record within Oracle University. And now, this is a contrived example and it won’t actually work, but it’s good for illustrative purposes.  Oracle University, let’s say, publishes the URL for your student data as something like https://oracle.com/oracleuniversity/student/{studentnumber} you put in some kind of number, like the number 23, and if you enter that into your browser address bar and press Enter, then your browser, on your behalf, sends a GET request—what we call an HTTP GET operation—to the web server. When the web server receives the request, it will somehow read the record for student 23, format a response, and send the response back to the client. 05:16 Joe: That’s a GET or a READ request. Now, what if you are creating a new student? Well, you fill out a form on the webpage and you click the Submit button. And it sends a POST request, which tells the server to create a new record in its storage mechanism, most likely a database of some form. If you do an update, you change certain fields on the webpage and click the Submit button and this time, an update request is made. If you wanted to delete the record, you’d find the record you want to delete and press the Submit button, and this time a delete request is made. This is the general idea, though there are different ways to do creates and updates that are really irrelevant here. Those requests to the server I mentioned are called HTTP operations and there are several of them. But the four most popular are GET to retrieve data, POST to create a new record on the server, PUT to update a record, and DELETE to remove a record.  On the client side, we just need to specify where the record is that we want to retrieve—that’s the oracle.com/oracleuniversity/student part of the URL and an identifying value, which makes it unique. So, when I do a GET request on customer or student 23, I’m going to get back a representation of the student data that exists in the student database for a student with ID 23. There should not be more than one of these or that would indicate an error. The response typically comes back in a format of a key:value pair called JavaScript Object Notation (JSON), but it could also be in a Text format, HTML, Excel, PDF, or whatever the server implements and is requested.  06:42 Nikita: OK great! That’s on the client side making the request. But what’s happening on the server side? Do I need to worry about that if I’m the client? Joe: No, that’s the great part. As a client, I don’t know, and frankly I’d rather not know what the server’s doing or how it does it. I don’t want to be dependent on the server implementation at all. I simply want to make a request and the server handles the request and sends a response. Now, just a word about what’s on the server. Some data on the server is static like a PDF file or an image or an audio file, for example, and sometimes you’ll see that in the URL the file type as an extension, like .pdf, and you get back a PDF file that your browser displays or that you can download to your machine. But with dynamic data, like student data coming out of a database based on the student number, a query is made against a database. The database responds with the data, and that’s formatted into some type of data format—typically JSON—and sent back to the client, which then does something with it, like displaying it on a webpage. So, as we can see, the client is fairly simple in the sense that it makes a request, receives the data response, and displays it or does something with it. And that’s one of the reasons why the choice to use REST and only REST in Visual Builder Studio is such a wise one. 07:54 Joe: Regardless of the different data sources or the different server implementations or how the data is stored on the server, or any of that, Visual Builder Studio doesn’t know and doesn’t care. What it sees is the REST request it sends and the response it gets back and then it deals with the response data regardless of how it’s implemented on the server. I mentioned the server sends back a representation of the resource, in this case, for example, the student record. That’s really where the abbreviation REST comes from: REpresentational State Transformation, which is a long way of saying, bring me back a representation of the resource—the thing—that I’m requesting. Now, of course, the server is a little more complex. On the server side, we would need software that is going to take the request from the web server using some programming language like Java, C#, C++, Python, or maybe even JavaScript in a Node.js application. You have a program that receives a request from the web server, executes the request (typically by connecting to the database if it’s a database call), makes the request, receives a data response from the database, formats that into some form, and passes it back to the web server, which then sends it back to the client that requested it. 09:01 Lois: Ok… I think I see. I’m guessing that ORDS gets involved somehow between the client and the server. Joe: Yes, exactly. We can see that the implementation on the server side is where the complexity is. For example, if I implement a student management service in Java, I have to write a bunch of Java code, a lot of which is boilerplate, housekeeping, boring code. For simple database access, it’s tedious to have to do this over and over, and if the database changes, it can be even more tedious to maintain that code to handle simple to moderately complex requests. Writing and maintaining software code to just read and write data from the database to pass to a client for a web request is cool the very first time you do it and then gets boring very quickly and it’s prone to errors because it’s so manual. So, it would be nice if we had a piece of software that could handle the tedious, boring, manual bits of this service. It would receive the request that our client, the browser or Visual Builder Studio for example, is sending, take that request, execute the request against the database for us, receive the response from the database, and then format it for us and send it back to us, without a developer having to write custom code on the server side. And that is what Oracle REST Data Services (ORDS) does. 10:13 Joe: ORDS contains a lightweight web server based on the Jetty web server that receives the request from the client, like a browser or Visual Builder Studio or whatever, in the form of a URL, parses the request, generates a query or an update, or an insert or delete, depending on the nature of the HTTP operation sent or requested, and sends it to the database on our behalf. The database executes the request from ORDS, sends back a response to ORDS, and ORDS formats the response for us in the JSON and sends it back to our client. In nutshell, that’s it. 10:45 Lois: So ORDS does all that? And it’s free? How does it work? Uhm, remember I’m not as technical as you are. Joe: Of course. ORDS is free. It’s a lightweight, highly performant Java app that can run in many different modes, from stand-alone on a server to embedded in an application server like WebLogic, to running in the Oracle Cloud with the Oracle Autonomous Database (ADB). When you REST-enable your tables, your web requests are intercepted by ORDS running in ADB. It’s optimized for the purpose of handling web requests, connecting to the Oracle database, and sending back formatted responses as JSON. It can also handle more complex requests as well in the form of queries with special parameters.  So, you can see what ORDS does for us. It handles the request coming from the client, which could be a browser or Visual Builder Studio or APEX or whatever client—pretty much any client today can make an HTTP call—it handles the call, parses the request, makes the request to the server on our behalf, and of course security is built-in and all of that, and so we don’t get to data we’re not supposed to see. It receives a response from the database, formats it into the JSON key:value pair format, and sends it back to our client. 12:00 Are you planning to become an Oracle Certified Professional this year? Whether you're a seasoned IT pro or just starting your career, getting certified can give you a significant boost. And don't worry, we've got your back. Join us at one of our cert prep live events in the Oracle University Learning Community. You'll get insider tips from seasoned experts and learn from other professionals' experiences. Plus, once you've earned your certification, you'll become part of our exclusive forum for Oracle-certified users. So, what are you waiting for? Head over to mylearn.oracle.com and create an account to jump-start your journey towards certification today! 12:43 Nikita: Welcome back. So, Joe, then the next question is, what do we do to REST-enable our database? Does that only work for ADB? Joe: This can be done in a couple of different ways. It can be done implicitly, called AutoREST, or explicitly. AutoREST is very convenient. In the case of an ADB database, you log in as the user who owns the structures, select your tables, views, packages, procedures, or functions that you want to REST-enable. Choose REST and then Enable from the menu for the table, view, stored package, procedure, or function and a URL is generated using your POST, GET, PUT, and DELETE for the standard database create, retrieve, update, delete operations. And it’s not just for ADB. You can do this in SQL Developer Desktop as well. Then, when you invoke the URL for the service, if you include just the name of the resource, like students, you get the entire collection back. If you add an ID at the end of the URL, like student/23, you get back the data for that specific student back, or whatever the structure is. You can add more complex filter parameters as well. And that’s it! Very easy. And, of course, you can apply appropriate security and ORDS enforces it.  But you also can create custom code to handle more complex requests.  13:53 Lois: Joe, what if there’s custom logic or processing that you want to do when the REST call comes in and you need to write custom code to handle it? Joe: Remember, I said on the server side, we use custom code to retrieve data as well as apply business rules, validations, edits, whatever needs to be done to appropriately handle the REST call. So it’s a great question, Lois. When using ORDS, you can write a REST service handler in PL/SQL and SQL, just like if you were writing a stored procedure or a function or a package in the database, which is exactly what you’re doing. ORDS exposes your PL/SQL code wrapped in a REST interface with, of course, the necessary security. And since it’s PL/SQL, it runs in the database, so it’s highly performant, fast, and uses code you’re likely already familiar with or maybe already have. Your REST service handler can call existing PL/SQL packages, procedures, and functions. For example, if you created packages with stored procedures and functions that wrap access to your database tables and views, you can REST-enable those stored procedures, functions, and packages, and call them over the web. And maintain the package access you already created. I do want to point out that the recommended way to access your tables and views is through packages, stored procedures, and functions. While you can expose your tables and views directly to REST, should you really do that? In practice, it’s generally not a recommended way to do it. Do you want to expose your data in tables and views directly through a REST interface? Ideally, no, access should be through a PL/SQL wrapper, same as it’s—hopefully—done today for your client-server applications. 15:26 Nikita: I understand it’s easy to generate a simple REST interface for tables and so on to do basic create, retrieve, update, and delete operations. But what’s required to create custom code to handle more complex business operations?  Joe: The process to create your own custom handlers is a little bit more involved as you would expect. It uses your skills as a PL/SQL programmer, while hiding the details of the REST implementation to let you focus on the logic and processing. Mechanically, you’d begin by creating a module that has a URL associated with it. So, for example, you would create a URL like https://oracle.com/oracleuniversity/studentregistry. Then, within that module, you create a template that names the specific resource—or thing—that you want to work with. For example, student, or course, or registration. 16:15 Joe: Then you create the handler for it. You have a handler to do the read, another handler for the insert, another handler for an update, another handler for a delete, and even possibly multiple handlers for more complex APIs based on your needs and the parameters being passed in.  You can create complex URLs with multiple parameters for passing needed information into the PL/SQL procedure, which is going to do the actual programming work for you. There are predefined implicit variables about the message itself that you can use, as well as all the parameters from the URL itself. Now, this is all done in a nice developer interface on the web if you’re using SQL Developer Web with ADB or in SQL Developer for the desktop. Either one can do this because under the covers, ORDS is generating and executing the PL/SQL calls necessary to create and expose your web services. It’s very easy to work with and test immediately. 17:06 Lois: Joe, how much REST knowledge do I need to use ORDS properly to create REST services? Joe: Well, you should have some basic knowledge of REST, HTTP operations, request and response messages, and JSON, since this is the data format ORDS produces. The developer interface is really not designed for somebody who knows nothing about REST at all; it’s not designed to take them step-by-step through everything that needs to be done. It’s not wizard-based. Rather, it’s an efficient, minimal interface that can be used quickly and easily by someone who has at least some experience building REST services. But, if you have a little knowledge and you understand how REST works and how a REST interface is used and you understand PL/SQL and SQL, you could do quite a lot with only minimal knowledge. It’s easy to get started and it’s fun to see your data start appearing in webpages formatted for you, with very little or even no code at all as in the case of AutoREST enabling. And ORDS is free and comes as part of the database in ADB as SQL Developer Web and SQL Developer Desktop, both of which are free as well. And SQL Developer Web and SQL Developer Desktop both have a data modeler built into them so you can model your database tables, columns, and keys, and generate and execute the code necessary to create the structures immediately, and they can create graphical models of your database to aid in understanding and communication. Now, while this is not required, modeling your database structures before you build them is most definitely a best practice. 18:29 Nikita: Ok, so now that I have my REST-enabled database tables and all, how do I use them in VBS Designer? Joe: In Visual Builder Studio Designer, you define a service connection by its endpoint and paste the URL for the REST-enabled resource into the wizard, and it generates everything for you by introspecting the REST service. You can test it, see the data shape of the response, and see data returned. You access your REST-enabled data from your database from Visual Builder Studio Designer and use it to populate lists, tables, and forms using the quick start wizards built in. I’ll also mention that ORDS provides other capabilities in addition to handling REST calls for the database tables and views. It also exposes over 500 different endpoints for managing your Oracle Database, things like Pluggable Database Management (PDBs), Data Pump, Data Dictionary, Performance, and Monitoring. It’s very easy to use and get started with. A great place to start is to create a free, autonomous database in Oracle Cloud, start it up, and then access the database actions. You can start creating tables, columns, and keys, and loading data, or you can load your own scripts, if you’ve got them, to produce the tables and columns and load them. You can upload the script and run it and it will create your tables and other needed structures. You can then REST-enable them by selecting simple menu options. It’s a lot of fun and easy to get started with. 19:47 Lois: So much good stuff today. Thank you, Joe, for being with us today and in the past few weeks and sharing your knowledge with us. Nikita: Yeah, it’s been so nice to have you around. Joe: Thank you both! It’s been great being here with you. 19:59 Lois: And remember, our Visual Builder courses, Develop Visual Applications with VBS and Develop Fusion Apps with VBS, both show you how to work with a third-party REST service. And our data modeling and design course teaches the fundamentals of data modeling. You can access all these of courses, for free, on mylearn.oracle.com. Join us next week for another episode of the Oracle University Podcast. Until then, I’m Lois Houston… Nikita: And Nikita Abraham signing off! 20:30 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.