Installing Oracle REST Data Services (ORDS) and SQL Developer Web.

[@JeffSmiththat]

Note that our install/upgrade process changed in 2022, and I haven't had time to post an updated video. Here's a blog post that shows the latest, and includes slides www.thatjeffsmith.com/archive/2022/04/oracle-rest-data-services-version-22-1-read-the-readme/

[@sakshikhose2792]

Well Explained!Thanks for sharing

[@JeffSmiththat]

Great, so happy to hear you enjoyed it!

[@Praveenkumar-of7zf]

Can we Install Oracle in Mac M1 2020 directly without using any virtual machine?? if yes please suggest...!!!

[@JeffSmiththat]

There is currently no OS X port of the database. We do have LINUX ARM on 19c, which means running it on your M1 via a linux container or VM should be easy.

[@berndeckenfels]

I understood „unintended install“ - haha, that would be a great new feature ,)

[@JeffSmiththat]

Haha, lol! Unattended :)

[@IgorAllenRitzmann]

You're welcome. I speak portuguese and a little english. Instala = install

[@andreseduardoidrovomunoz1498]

hi, i have a problem when I install ords and I put the sys key, I get the error "Error. The version ORDS_VERSION does not exist." I do not know what I'm doing wrong.

[@JeffSmiththat]

'the sys key' - you mean when you're prompted for an administrator account's credentials?

[@geraldinelazo94]

What happens if you don't enable the sql developer web option when installing ords for the first time. Is there an option to configure it later? or I have to reinstall the ords?

[@JeffSmiththat]

You can simply update the configuration, by either editing the conf file directly or using the 'ords config' command. No need to re-install!

[@cj5214]

we are going to try this, let's see how it works.

[@JeffSmiththat]

It's been a week, mind if I check in? Feel free to email me if you'd like ([e-mail])

[@cj5214]

@@JeffSmiththat did all the labs for REST on the example. I down load the VM. so far we like it, no coding :). we are into security & firewall.

[@JeffSmiththat]

@@cj5214 great! Keep me posted!

[@Revanth2344]

Hi jeff, In my server ords 19 is already installed and I'm unable to find the params folder (ords.parameters). As I'm trying to install new ords in same server it doesn't prompt for admin credentials.. results in ords_grant_priv.sql script error... ORDS_METADATA created but it is unable to get grant permission.. But ords got installed, app url is up for sometime and goes down again, when refreshes page it is coming up and again going down after sometime... Please guide on this issue 🙏

[@JeffSmiththat]

You need the SYS user to do a proper install/upgrade. "But ords got installed, app url is up for sometime and goes down again, when refreshes page it is coming up and again going down after sometime..." When it goes 'down' - what do you see in the ORDS server logs?

[@Revanth2344]

I'm using aws rds , we can't alter or use SYS USER. ONE MORE THING Ords 19.2.0 installed and hosted in Tomcat server but there are no metadata and public users And there is not ords admin or runtime role in database.. My question is without those users and role in database how the application is working

[@JeffSmiththat]

RDS has very specific instructions.

[@sri6137]

Ords is generating output in json format. How to get output in xml format

[@JeffSmiththat]

Write SQL that generates XML and use the Media Resource option for your Handler blocks. I have an example here www.thatjeffsmith.com/archive/2021/04/oracle-rest-apis-and-your-xml/

[@fahdbahoo1]

Beautiful explanation Jeff. Do you have any video for installing ORDS for APEX ? share please. Thanks

[@JeffSmiththat]

Yes, for version 21.x - I have it in the video comments. For 22.x I dont' have video, just a blog post www.thatjeffsmith.com/archive/2022/04/oracle-rest-data-services-version-22-1-read-the-readme/

[@coolfreaks68]

At 18:17 the "Rest Services" option is never enabled by itself. You need to follow the below steps to get it working. 1. Start the ORDS as suggested in the video. 2. Then login to the Pluggable DB with user=system as sysdba role 3. Excute the following commands : create USER api_admin_user IDENTIFIED BY password1 ; grant connect to api_admin_user; grant resource to api_admin_user; alter user api_admin_user grant connect through ords_public_user; 4. Now, login to the Pluggable DB with user=api_admin_user 5. Execute the following command : DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN ORDS.ENABLE_SCHEMA(p_enabled => TRUE, p_schema => 'API_ADMIN_USER', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'api', p_auto_rest_auth => FALSE); commit; END;

[@JeffSmiththat]

We would never rest enabled a user by default, that's why you have to use the ORDS api ENABLE(). ENABLE does the alter user grant connect through ords_public_user...along with a few other things. If you have a good install of ORDS, you can do what i showed in the video, login as your user in SQLDev, and right-click on your connection and do the Enable REST.

[@idir1badsi65]

hello, i got this error java when i choosed the location to store configuration : Caused by: java.lang.OutOfMemoryError: Java heap space, can you please help me?

[@idir1badsi65]

hi i finally changed to : java -jar -Xmx800M ords.war install advanced thanks

[@berndeckenfels]

Jeff, I wonder if you know. On OCI with an ATP Database, when I define the database on a private subnet I get the message that SQL Developer Web is only available from that IP. However still the endpoint of the database is availablke from the outside and it displays the ORDS 404 page. So I wonder is that set up in a way that there are two listeners, so you can have public ords services and the SQL Web is still isolated or is that some form of proxy? Also, can you actually publish the Developer App on the public part (for test databases).

[@JeffSmiththat]

The database isn't reachable, but the public ords that serves all the databases in that data center IS. I could go to abcdefg12345.adb.us-phoenix-1.oraclecloudapps.com and get a 404, but that doesn't mean that represents a valid DB somewhere.

[@berndeckenfels]

@@JeffSmiththat ah so it means it’s a Shared(?) ords, but it can’t reach the particular database on the internal net, not only for sql developer Web, but also not for registered schema.

[@okmmanvideos]

Hi Jeff , thanks for sharing .... In a OCI VM DB System based on linux , which user I should use to install ORDS .... oracle ? root?

[@JeffSmiththat]

ORDS has no 'install' to speak of, it's just a Zip. Unless you're using the RPM, then whichever user you want the process to run as, 'root' is overkill.

[@MamadouDEMBAMalal]

Hello, How to test with command line a datasource generally please ? If the test connection is Ok with database configuration with ORDS's configuration ?

[@JeffSmiththat]

Run the validate command for that pool

[@notoriginalbutworks]

incredible. thank you.

[@JeffSmiththat]

Glad you liked it!

[@muhammadalamgir5724]

during installation, i have a problem in my PC. it shows an error that, "java is not recognize as an internal or external commad. please help me. how can i solve this...?

[@JeffSmiththat]

Go install Oracle Java 8 or 11

[@MartinTriana1407]

Hi, when I execute the command: java -jar ords.war install gives the error: Warning: Support for running java -jar ords.war is deprecated. Add ords to your PATH value and use the ords command instead. 2022-05-11T21:49:37.219Z INFO Your configuration folder C:\temp\ords is located in ORDS product folder. Oracle recommends to use a different configuration folder. Can you help me please. Thanks

[@JeffSmiththat]

Yes. I'll have a new video for 22.1 up shortly, but in the meantime see this blog post: www.thatjeffsmith.com/archive/2022/04/oracle-rest-data-services-version-22-1-read-the-readme/

[@MartinTriana1407]

@@JeffSmiththat Thanks and I'm installing on Windows 11.

[@JeffSmiththat]

@@MartinTriana1407 10 or 11, it shouldn't matter

[@adarshnanu5229]

When a reference cursor is to be converted to REST, its type has to be resultset, correct ? However as per oracle specs, the valid types are as below. p_param_type The native type of the parameter. Valid values: STRING, INT, DOUBLE, BOOLEAN, LONG, TIMESTAMP Finally I checked some other online reference, and finally made it work. It would be great if you could get the documentation updated for ORDS. Thanks in advance.

[@JeffSmiththat]

You're absolutely right, that needs fixed PRONTO. Logging the bug now, thanks for the heads-up!

[@adarshnanu5229]

@@JeffSmiththat thanks , I hope a full review will happen and all the other missing data if any also get updated.

[@JeffSmiththat]

@@adarshnanu5229 just send your issues as you find them

[@poramatewongbiasujj7048]

Thanks for explanation If I want to run with apache tomcat, Do you have any tutorial?

[@JeffSmiththat]

Nope, but get the configdir set and just drop it into Tomcat like any other servlet

[@johncrowell2743]

Has anyone been able to successfully install and configure Customer Managed ORDS on Autonomous Database? I have been trying to follow the documentation but can't seem to start ORDS. My customer managed environment for ORDS runs in Oracle Cloud Infrastructure (Oracle Linux 8.5). I have followed it up to the step "/opt/oracle/ords start" but then get the error "-bash: /opt/oracle/ords: Is a directory". I also tried to follow this tutorial but when I ran the command "sudo systemctl start ords" I get an error: "Job for ords.service failed because the control process exited with error code. See "systemctl status ords.service" and "journalctl -xe" for details.". Has anyone been able to successfully set this up and if so, can you provide the link or explain the steps you used to do so?

[@JeffSmiththat]

Sorry John I'm just now seeing this. I suggest you open a case with My Oracle Support. The Autonomous support team can subCase a ticket over to the ORDS team to help you out. My video here doesn't take into account the Customer Managed ORDS for Autonomous Scenario.

[@AyubKhan-hd7yf]

Jeff what is pdb and how would i get id/wd connection for sql plus details to proceed for apex install? also i have ATP db how i would connect it

[@JeffSmiththat]

Pluggable database...in 12c and higher you can have one container database with multiple pluggable databases (pdbs) running inside of it.

[@JeffSmiththat]

You need sys to install apex, so talk to your DBA

[@AyubKhan-hd7yf]

@@JeffSmiththat so do I need to install Oracle DB 12c ? i thought without any installation i can access sql developer directly on web

[@AyubKhan-hd7yf]

@@JeffSmiththat I am practicing self so do i need to install oracle Client ? or 12c database in machine?

[@JeffSmiththat]

@@AyubKhan-hd7yf you need an Oracle Database, somewhere. It can even be 11gR2,.but you need sysdba account to do the install of ORDS/SQLDev Web

[@purnendudas3178]

Hi Jeff, I am following your video since long and it helped me to perform installation of ORDS. I am stuck at a point that I can't make changes HTTP to HTTPS and the huddle is I can't uninstall and re-install again. I am having few Clients who are using my Apex Application and don't want to loose it. In case if you can help me on this it will save my position.

[@JeffSmiththat]

What version of ORDS are you using? Configuration changes are different pre version 22.1...

[@santhoshparamesh2664]

@@JeffSmiththat I am also in the same situation need to make changed HTTP to HTTPS and i cant uninstall and reinstall. My Ords version is 19.1 please help to make changes from http to https

[@JeffSmiththat]

@@santhoshparamesh2664 you don't need to uninstall/reinstall you only need to update the configuration settings to use https vs http

[@IgorAllenRitzmann]

hi, great video. So, How i enable in sqldeveloper rest services ? jump point 18:21. on my sqldeveloper dopn't appear it to configuring. THX

[@JeffSmiththat]

What version of ords did you install? What version of sqldev are you running? Who are you logging in as? Do you see the ords public user and metadata schemas in your database?

[@IgorAllenRitzmann]

@@JeffSmiththat i fix it instala correctly and loggged on pdb. I Was in rootcdb.

[@JeffSmiththat]

@@IgorAllenRitzmann great, glad to hear you got it going!

[@malshamsi8055]

@@JeffSmiththat I have the same issue could you help me please I'm new at this. When I right click it, REST Services menu not show up in SQL Developer ords version: ords-21.1.3.153.1102 sqldev version: sqldeveloper-20.4.1.407 logged in as sys, Thank you for the video!

[@JeffSmiththat]

@@malshamsi8055 you installed ords to that database?

[@vikrammadamshetti6427]

Jeff, thanks for making this video. Very useful. For various security reasons, sys user credentials are not provided to us. Also, sysdba privilege is not given to any other users. Recent ORDS documentation shows that sys user credentials are not required. But not clearly stated how the schemas are created on the backend. Appreciate any of your thoughts on this. Thank you.

[@JeffSmiththat]

There's a script to run to create a user to do the install.. Alternative is to ask your DBA to do the install for you.

[@vikrammadamshetti6427]

@@JeffSmiththat Thank you so much for your prompt response. Would you mind locating the script for me? Unfortunately, DBA can not run the installation for us because we have to put this into CI/CD pipeline and all this needs to happen with out any user intervention.

[@JeffSmiththat]

@@vikrammadamshetti6427 it's in your ords installer directory, 'ords_installer_privileges.sql' - see the docs as well

[@vikrammadamshetti6427]

@@JeffSmiththat Thank you again for your response. Will this create ords_metadata and ords_public_user schemas. I already visited this sql but don't see any syntax to create these schemas.

[@JeffSmiththat]

@@vikrammadamshetti6427 no, you use that script to create the user that will be used in the silent installer run of ords

[@enockoloo3814]

unfortunately for developers you cant do much with this everything is controlled by dba - you have to send them requests and they come back with all unnecessary questions.

[@JeffSmiththat]

Right, you need to engage your DBA, request them to investigate.

[@GuidoGabriel]

NIiiiiiiiiiiiiiiiiceeeeeeeeeeeee

[@JeffSmiththat]

Glad you enjoyed that, Guido!

[@armashish]

hello

[@JeffSmiththat]

Hey!

[@Superdooperhero]

You should have gone with 2 for HTTPS, otherwise this is useless to everyone else.

[@JeffSmiththat]

Installing for hello world vs for production, two different stories. Also if you're using tomcat or WLS, a moot point. Also, some folks will have ords running http on one side of a firewall with a load balancer https in front. I take your point, but there's no one size fits all answer here.

[@Superdooperhero]

Same story with authentication

[@JeffSmiththat]

If you so go with 2, we'll generate a self-signed certificate. You'll need to get a real one from somewhere like Let's Encrypt else browsers will block/discourage. Maybe that's a good part 2 video follow-up?

[@JeffSmiththat]

@@Superdooperhero I wanted a 10 minute video not a 30 minutes video. I can always make more.

[@Superdooperhero]

@@JeffSmiththat Looking forward to the 30 minutes one