Flutter for Single-Page Scrollable Websites with Navigator 2.0

Creating the HR user

Now, before you start asking, well, why don’t you just Data Pump this up to your Cloud Service, I want to say – great idea! And you totally SHOULD do that if you can. However, not everyone will have a ‘living’ Oracle instance on-prem available for this, and sometimes you just want to get things done, quick-and-dirty style. That’s the idea behind this post.

I’m going to run this code as the ADMIN user in SQL Developer Web

CREATE USER hr IDENTIFIED BY PassWordGoodNotThis;
 
GRANT CONNECT, resource TO hr;
 
ALTER USER HR QUOTA UNLIMITED ON DATA; -- this part is important
 
 
BEGIN -- this part is so I can login as HR via SQL Developer Web
    ords_admin.enable_schema (
        p_enabled               => TRUE,
        p_schema                => 'HR',
        p_url_mapping_type      => 'BASE_PATH',
        p_url_mapping_pattern   => 'peeps', -- this flag says, use 'peeps' in the URIs for HR
        p_auto_rest_auth        => TRUE   -- this flag says, don't expose my REST APIs
    );
    COMMIT;
END;
/

With that executed, we can now login as HR.

The link to SQL Developer Web needs the new URL Mapping Pattern for the REST Enabled Schema inserted.

The link your Database Console gives you for SQL Developer Web is setup for you to login as ADMIN. But we’re wanting to login as HR now.

Even if I get the user/pass right, if my URL for the HR user isn’t provided, SQL Developer Web won’t let you authenticate as HR.

I’m in, now let’s create stuff!

This part is pretty straightforward. I already have some scripts I generated for HR. Now I can just run them.

Paste in your code and hit F5 or just hit the 2nd execute button, just like the desktop.

What’s it look like?

Quite standard, really

I wanted to add ALL of the tables to my diagram, how to do that? Well, there’s a few ways, but let’s look at this one, the ‘Add Objects to Diagram’ button –

For your more complex schemas, this will come in handy.

One last thing…let’s create a MV

In the SQL Worksheet, switch your object navigator from TABLES to Materialized Views. Then hit the ‘New’ button, and supply your query to ‘feed’ the MV.

Fill in the properties, and when you’re happy, click the ‘Create’ button. Note, you can click on the DDL page to see the proposed CREATE MATERIALIZED VIEWS command, or just copy it out, and tweak as you want back in the Worksheet.

After it’s created, you can drag and drop the MV over to the worksheet, and we’ll create a nice SELECT for you. I’ve wrapped mine with an EXPLAIN command, and added a call to DBMS_XPLAN. With both statements selected, I hit the Execute as Script button, and I can see just how my query is satisfied.

Now, why did I need to do the two SQL commands as ‘one’ operation? In a SQL Developer Web world, each operation you do is done as a separate transaction, in a separate session. So if I combine the two and execute as a script, the DISPLAY() function call can work.

One final note: I needed to use my ADMIN account to GRANT CREATE VIEW and CREATE MATERIALIZED VIEW to my HR user before I could do the above.

Taking Advantage of AUTO TABLE and ORDS

If you already have a TABLE in your schema, and you want to create a REST API for accessing said table, we make that easy.

It’s a right-click in SQL Developer (Desktop)

This UI is coming to SQL Developer Web, soon.

Or you could of course just run this very simple PL/SQL block –

BEGIN
    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'JEFF',
                       p_object => 'HOCKEY_STATS',
                       p_object_type => 'TABLE',
                       p_object_alias => 'hockey_stats',
                       p_auto_rest_auth => TRUE);
    COMMIT;
END;

Another quick aside, if you need to catch up on these topics, I’ve talked about creating your application SCHEMA and REST Enabling it for SQL Developer Web access.

And, I’ve talked about using the CSV Load feature available with the ORDS AUTO Table mechanism.

My TABLE

I have a HOCKEY_STATS table, that I want to load from some CSV data I have on my PC. It’s an 8MB file (35000 rows, 70 columns).

Now, I could use the Import from CSV feature in SQL Developer (Desktop) to populate the TABLE…

Took approximately 16 seconds to batch load 35,000 records to my Autonomous Database service running in our Ashburn Data Center from Cary, NC – using the MEDIUM Service.

That’s not super quick, but it was super easy.

But what if I need a process that can be automated? And my API du jour is HTTP and REST?

Let’s POST up my CSV to the TABLE API

Let’s find the URI first. Go into your Development Console page for your service – you’ll see we show you what all of your ORDS API calls will start with:

You don’t have to guess or reverse engineer your ORDS calls from the SQL Developer Web or APEX links anymore.

After the ‘/ords/’ I’m going to include my REST Enabled SCHEMA alias, which I have specified as ‘tjs’ in place of ‘JEFF’, and then my TABLE alias, which I’ve just left as ‘hockey_stats’.

So if I want to do a CSV load, I need to HTTPS POST to

<pre lang='text'>
https://ABCDEFGHIJK0l-somethingash.adb.us-ashburn-1.oraclecloudapps.com/ords/tjs/hockey_stats/batchload?batchRows=1000
</pre>

The ‘/batchload?batchRows=1000’ at the end tells ORDS what we’re doing with the TABLE, and how to do it. This is documented here – and you’ll see there’s quite a few options you can tweak.

Before I can exercise the API, I need to assign the ORDS Privilege for the TABLE API to the ‘SQL Developer’ ORDS Role. That will let me authenticate and authorize via my ‘JEFF’ Oracle database user account.

There’s a PL/SQL API for this as well as an interface in APEX.

If that sounds ‘icky’ then you can also take advantage or our built-in OAUTH2 Client (example).

Now, let’s make our call. I’m going to use a REST Client (Insomnia) but I could easily just use cURL.

Almost 10 seconds…not blazing fast, but again, very easy (no code!) and it’s sending 8MB over HTTP….

I could tweak the batchRows parameter, and see if I could get faster loads, I’m sure I could. But the whims of public internet latency and the nature of the data I’m sending up in 16 KB chunks will make this a fun ‘it depends’ tech scenario.

https://blogs.oracle.com/developers/microservices-the-easy-way-with-ords-and-micronaut-part-1

• Intro
• Getting Started With Kubernetes And Docker
• Getting Started With Autonomous DB
• Building A Helidon Microservice Part 1
• Building A Helidon Microservice Part 2
• Building A Helidon Microservice Part 3