Dermot's Tech Tips

ORDS 23.3.0 brings a new way of accessing protected resources using JSON Web Tokens (JWT). Making it much easier to integrate ORDS with OAuth2-compliant Identity Providers such as  Oracle Identity Cloud Service (IDCS) Oracle Identity Access Management (IAM) Microsoft Azure Active Directory  Okta OAuth0  JWT access tokens issued by Identity Providers on behalf of a user/resource owner, allow a client to access the users protected resources in ORDS. ORDS provides a new feature called JWT Profile, which defines how JWT bearer tokens can be validated for a particular REST-Enabled schema. Example JWT Profile JWTs presented as Bearer Tokens for protected resources for a particular REST-Enabled schema have to be Signed with a signature which can be validated using the schemas JWT Profile p_jwk_url Provide an audience "aud" claim which matches the schemas  JWT ...

SQLcl allows you to define "named" scripts or statements using the ALIAS command.  Jeff has a neat example on his blog . I created the following alias to make it really easy to destroy a database!  drop objects. RM Alias Dropping tables, views, procedures is a daily occurrence for database developers. The following SQLcl ALIAS may come in handy when you want to drop lots of objects at the same time. Just specify the object_type : TABLE, VIEW, PROCEDURE, .... object_name: Using a  LIKE expression.  Example: MY%  created: Number of days. Example: 1 A script is written with all the objects listed. The list of objects can be reviewed and an option to execute the script or cancel without dropping anything is provided. Needless to say you can do a lot of damage dropping database objects and this makes it very easy to do so. So use at your own risk! Best to grab the alias code below and see if you can tweak it to suit yourself. Test Env create t...

I put together a small Oracle PL/SQL package which demonstrates how to access REST Enabled SQL service and standard REST end points through PL/SQL and SQL. It can also dynamically create an Oracle Database View to access information from a REST Enabled SQL request. This view can be referenced in SQL just like any other view but the data is provided from the REST Enabled SQL service which could be running against another database. Maybe an alternative to Oracle database links? Examples using RESTSQL package restsql.pks restsql.pkb Setup HTTP access in Oracle 12c: You may need to setup HTTP access in your database. Heres how I went about providing access to my Oracle user TESTREPO Example of a view automatically created by restsql.rest_view: EXEC restsql.rest_view('REMOTE_ALL_TABLES','http://10.175.200.185:8087/ords/demo/_/sql','DEMO','demo','select * from all_tables'); Limitations : The view is limited to the ...

POST a query or DML or DDL or even a short script over HTTPS to ORDS and have ORDS run that on your Oracle Database and have the results returned in JSON. That's the plan. What You Need To Know REST Enabled SQL uses Schema Authentication as well First Party Authentication.   This means you can run SQL against an Oracle Database if the following is true. REST Enabled SQL is enabled in ORDS A database user is REST Enabled  The database user name is known The database user password is known So before turning on REST Enabled SQL in ORDS it is vital that current REST Enabled schemas have strong, secure passwords. This should be resolved before turning on REST Enabled SQL in ORDS, as once it is turned on, all REST Enabled schemas will be accessible using only their database username and password through ORDS. The same applies for any new REST Enabled schemas. Setup Requirements Download ORDS 17.3. Download cURL  . Not necessary, but the exampl...

The Oracle REST Data Service provides REST access to your Oracle Database. Tables, predefined queries and PL/SQL blocks can be exposed as RESTful services. This is great when you can foresee what table, query or action you wish to REST enable. Starting in ORDS 17.3 you can now POST the query, pl/sql or sql*plus statement to ORDS at run time. This new feature is call REST Enabled SQL and it is built into ORDS as standard. It allows you to define the query or statement you want to run without having to create a predefined RESTFul service. Download http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/ords-beta-173-3873522.html REST Enabled SQL provides a HTTPS POST API that your HTTP client (web browser, java application, ...) can access just like other ORDS services. Just POST the SQL statement you wish to run to the REST Enabled SQL endpoint and the results will be returned in JSON. Example Web Form A HTML form POSTs to the REST Enabled SQL endp...

Oracle Database 12c allows you to define a column to be an IDENTITY column. It can be either  GENERATED ALWAYS or BY DEFAULT. A GENERATED ALWAYS AS IDENTITY column does not allow you to INSERT a value for it. When it comes to moving data from one table to another and this type of column is present, there are a few steps to "disable" the GENERATED ALWAYS AS IDENTITY column and after the data move "enable" it. set echo on drop table test_identity; create table test_identity(id number generated always as identity); --This insert will fail as the column is generated always --and it does not accept values insert into test_identity(id) values(1); --Change the identity column to generated by default. --This will allow us to insert our values alter table test_identity modify id generated by default as identity; --This insert works. as will Copy to Oracle insert into test_identity(id) values(1); select * from test_identity; --Change the identity column back to...

SQLcl is the bee's knees. Problem Today I had a common problem. I wanted to reposition a column within a table. "Oh you should use views!"  "Never reference a table directly!"  "Column position should be meaningless!" I hear you shout. Yes but, look at this table definition. PERSON table FIRSTNAME ,ADDRESS,PHONE, LASTNAME Doesn't that drive you mad.  I want the order FIRSTNAME, LASTNAME , ADDRESS, PHONE Or worse. You decide to add a new column  (middlename) to your table definition script. create table person (firstname varchar2(100),                             middlename varchar2(100),                                  lastname varchar2(100),                              ...