Category: Automation

  • Tinkering: a SQL script for the ORDS_ADMIN.ENABLE_SCHEMA procedure

    Tinkering: a SQL script for the ORDS_ADMIN.ENABLE_SCHEMA procedure

    Post-ORDS installation

    Once you’ve installed ORDS, you need to REST-enable your schema before taking advantage of ORDS (I used to forget this step, but now it’s like second nature).

    RESOURCES: I've discussed ORDS installation here and here. I'd check both pages if you're unfamiliar with it or want a refresher. 

    ORDS.ENABLE_SCHEMA / ADMIN_ORDS.ENABLE_SCHEMA

    While logged into your database with SQLcl, you can issue the following commands to not only create a new user but grant them the required Roles (and the underlying Privileges) and REST-enable their schema (aka “ORDS-ifying” a schema):

    /* Remember to remove the brackets when you run this code */ 
    
    Create User [username] Identified By [password];
    Grant Connect to [username];
    Grant Resource to [username];
    
    Grant Unlimited Tablespace to [newuser];
    
    /* This PL/SQL procedure assumes you are logged in as the SYS.
    If you are logged in as that new user, AND HAVE BEEN GRANTED
    THE DBA ROLE, then you can execute the ORDS.ENABLE_SCHEMA 
    procedure */
    
    Begin
        ORDS_ADMIN.ENABLE_SCHEMA(p_schema = > '[username]');
    End;
    /
    Create User [username] Identified By [password];
    Grant Connect to [username];
    Grant Resource to [username];
    
    Grant Unlimited Tablespace to [newuser];
    
    Begin
        ORDS_ADMIN.ENABLE_SCHEMA(p_schema = > '[username]');
        Commit;
    End;
    /

    Automate because lazy

    But even that is too much work for me, so I took a stab at automating this via an SQL script. As you can see, the above commands are simple and repeatable (a perfect candidate for automation). And since I’m constantly adding and dropping users for various reasons, copying and pasting code from an old blog or writing everything by hand gets annoying. Additional reasons for automating:

    1. laziness
    2. a desire to improve SQL and PL/SQL skills
    3. an interest in scripting
    4. I get easily distracted

    The script

    After about a day and a half, I have a working prototype script to call upon when I’m on the SQLcl command line. Here is what I came up with:

    NOTE: If you just came here to remix the code, I have this in the scripts folder in my GitHub blog repo as well. Please feel free to sample it and/or roast it/me 🔥. But if you keep scrolling, I'll go into more detail section-by-section.
    18-OCT-2023 UPDATE: I've slightly changed this code to include the Commit; command in the PL/SQL portion of the script. You'll see that reflected in line 44. Thanks René 🙌🏻!
    INPUT
    PROMPT Choose a new database username:
    ACCEPT NEWUSER CHAR PROMPT 'Enter new user name hurrr:'
    PROMPT Choose a temporary password for &&NEWUSER: 
    ACCEPT NEWPASS CHAR PROMPT 'Make it super secret:'
    
    /*
    I wish I could figure out a way to ONLY ask for username > check 
    that against existing database users > AND THEN allow a user to 
    continue with the 'choose password' step. I was only able to figure 
    out how to ask for the username and password AND THEN checks 
    against the database. I stole the code from this thread: 
    https://stackoverflow.com/questions/30710990/creating-an-oracle-user-if-it-doesnt-already-exist 
    
    Currently, its just extra steps for the user; kind of annoying. If you're 
    reading this and can figure out a way to get this working, let me know! 
    I'll make the change and attribute you in the comments :) 
    */
    
    Set Verify On 
    
    /*
    You can refer to section 6.3.10.11 for more details on this 
    SET VERIFY OFF command 
    https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/using-substitution-variables-sqlplus.html#GUID-3ACD41F3-A5A2-48D5-8E81-C29F9C14C865
    */
    
    /*
    The difference between using single and double ampersands: 
    https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/using-substitution-variables-sqlplus.html#GUID-C6BE6E41-821F-413E-B4B1-56AAE4A46298
    */
    
    Declare
    check_if_user_exists Integer;
    plsql_block VARCHAR2(500);
    NEWUSER VARCHAR2(20) := '&&NEWUSER';
    Begin
      Select count(*) Into check_if_user_exists From dba_users Where username=NEWUSER;
      If (check_if_user_exists = 0) Then
      Execute Immediate 'Create User &&NEWUSER Identified By &&NEWPASS';
      Execute Immediate 'Grant Connect To &&NEWUSER';
      Execute Immediate 'Grant Resource To &&NEWUSER';
      Execute Immediate 'Grant Unlimited Tablespace To &&NEWUSER';
      plsql_block := 'Begin ORDS_ADMIN.ENABLE_SCHEMA(p_schema => :1); Commit; End;';
      Execute Immediate plsql_block using NEWUSER;
      End If;
    End;
    /
    
    /*
    The p_schema parameter is mandatory, that's why I'm including it. 
    If you omit the other parameters, the procedure will use the default 
    parameter values. 
    
    Learn more about this procedure here: 
    https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.2/orddg/oracle-rest-data-services-administration-pl-sql-package-reference.html#GUID-459B8B6F-16EC-4FEC-9969-E8231668AD85
    
    I was able to get this entire thing to work through trial-and-error, 
    while also using this for reference: 
    https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems017.htm
    */
    
    PROMPT
    PROMPT
    PROMPT Congrats 🎉 the user: &&NEWUSER, with the password: &&NEWPASS is now a bona fide database user 🙌🏻! 
    PROMPT Not only that, &&NEWUSER can log into Database Actions and REST-Enable their database objects too 😍!
    PROMPT
    PROMPT
    PROMPT Click RETURN to return to the SQLcl prompt. And NEVER forget:
    PAUSE "You're good enough, you're smart enough, and doggone it, people like you!"
    PROPS: I owe much credit to this StackOverflow post and Jon Heller's and Mark Bobak's comments.

    Breaking it down

    Starting up podman

    I’ll first start up podman and cd in the correct directory. “Correct” insomuch that this is where my ordsuserl.sql script lives.

    starting-up-podman-machine-chris-hoina-senior-product-manager-ords-oracle-rest-apis-database-tools
    ordsuser.sql, the script I’ll be working with.

    Once my container status displays healthy, I’ll execute the ords serve command. This will launch ORDS in standalone mode (using the embedded Jetty server). I’ll use ORDS in a few minutes, so I should prepare it now.

    Prompting the user

    I’ll then log into my podman container using the following string:

    sql sys/password1234@//localhost:41465/ORCLPDB1 as sysdba
    sys-connection-string--chris-hoina-senior-product-manager-ords-oracle-rest-apis-database-tools-podman
    But why not the podman exec command?
    NOTE: I'm logging in as SYS (I've since been informed you shouldn't really be doing this as SYS; in fact, it looks like you shouldn't be doing this as SYSTEM. Tom has some good comments in this post here. 

    Why am I not using the podman exec command here?

    If you’ve seen my latest post on podman ports and networking, this command contradicts that entire article. There are actually two ways (maybe more, if I’m unaware) you can connect to your Oracle database in a podman container. The first way is to simultaneously hop on over to the container and sign in from inside that container. Your connection string would look like this:

    podman exec -it 21entdb sql sys/password1234@//localhost:1521/ORCLPDB1 as sysdba

    The second option is to sign in remotely like I’m doing in this current example:

    sql system/password1234@//localhost:41465/ORCLPDB1

    This is analogous to when you SSH into a machine remotely. I should have mentioned it in this recent YUM/Oracle Linux post. Still, when you create a Compute Instance, you can later SSH into that Instance and perform actions like you usually would in the Terminal or the Command Prompt. But instead of being on your own machine, you’re on a remote machine. Ports are very confusing (for me, at least), so please read that podman ports post.

    When you use the exec command, consider yourself on another computer on the Linux operating system. Once there, you must log in using the 1521 port because that is where the database’s TNS Listener (deep dive on Oracle database connection) is. However, when you are outside that container (i.e., that machine’s Linux OS), you need to use your local port (in this case, 41465) because it essentially acts as a proxy or a pass-through to the container’s 1521 port. Savvy 🏴‍☠️?

    DISCLAIMER: This my best-effort attempt at explaining this confusing concept. It is subject to change. But I really want people to take advantage of our tools in the Oracle Container Registry, so I hope this helps! 

    Hath connected to the database

    Once I’m in, I can call upon my script to quickly create a new user and REST-enable their schema (recall, I “cd” into the correct directory in an earlier step). The syntax:

    @ordsuser.sql
    MORE SQLcl: You can read the different SQL, SQLcl, and PL/SQL commands here.

    After pressing Return/Enter, a prompt will appear; this is what it looks like on the “front end”:

    reviewing-top-of-output-in-terminal-chris-hoina-senior-product-manager-ords-oracle-rest-apis-database-tools-podman
    Notice the prompts in those first four lines.
    HINT: I would also spend some time here, learning about the PROMPT, ACCEPT, and INPUT commands. Yes, this comes from the SQL*Plus documentation, but this works as you'd expect in SQLcl.

    Meanwhile, here is the corresponding section in the script:

    initial-input-section-of-script-vs-code-chris-hoina-senior-product-manager-ords-oracle-rest-apis-database-tools-podman
    The first section of the SQL script.

    Once I enter the password and press Enter/Return on my keyboard, the rest of the script is automatically executed using the provided username and password as substitution variables for the rest of the script! There is an in-depth explanation in the docs here, but you should grab the code and tinker with it to see how everything interacts and works together.

    NOTE: The Set Verify On command displays the changes made from the original PL/SQL procedure and the updated PL/SQL procedure (with the updated username and password). It isn't necessary, but I wanted to provide some feedback to a user. 

    PL/SQL procedure

    Assuming the user (i.e., the one you selected) doesn’t exist, the PL/SQL procedure should execute without issues. In the following image, you can see what is happening in real time:

    1. A new user is created with the assigned username and password
    2.  That user is granted the Connect and Resource roles
    3.  The schema is then REST-enabled using the ORDS_ADMIN.ENABLE_SCHEMA PL/SQL procedure
    reviewing-the-verify-on-plsql-procedure-chris-hoina-senior-product-manager-ords-oracle-rest-apis-database-tools-podman

    Why NEWUSER in the Declare block?

    Yeah, good question. At a minimum, you need to include the p_schema in the ORDS_ADMIN.ENABLE_SCHEMA procedure. Read up on that hereBut, I wanted the username to remain in lowercase since that will later be used for my schema’s URIs (e.g., http://localhost:8080/ords/ordstest/emp/).

    So I quickly taught myself/attempted a crash course on define variables and bind arguments; most of what I learned came from this EXECUTE IMMEDIATE documentation. And that’s why you see this in lines 25 and 34 of the code:

    NEWUSER VARCHAR2(20) := '&&NEWUSER'; 
    
    /* as well as */
    
    ORDS_ADMIN.ENABLE_SCHEMA(p.schema => :1);
    plsql-section-of-script-vs-code-chris-hoina-senior-product-manager-ords-oracle-rest-apis-database-tools-podman
    You can see the interaction between lines 25 and 34.

    And on the front end, courtesy of the Set Verify On command, you’ll see that updated block of code:

    The username and password fields are updated.

    Feedback

    I’ll then provide myself (or the user) with helpful feedback (along with words of encouragement). Once satisfied, I can click the Return/Enter key to exit the script and sign in to Database Actions as that new user.

    The corresponding section in the script looks like this:

    I’m cheating by adding PROMPT to give line breaks.

    Sign-in to Database Actions

    Now, I can navigate to the Database Actions sign-in page at localhost:8080/ords/sql-developer. If I wanted to, I could also navigate to the newly introduced landing page at http://localhost:8080/ords/_/landing (obviously, depending on your deployment, this address will differ).

    SQL Worksheet then the proof

    I’ll head to a SQL Worksheet, select All Objects in the Navigator tab, and a clean schema ready to take on the world!

    Summary

    And this marks the end of today’s lesson. So what did we learn?

    1. You can execute SQL scripts directly from the SQLcl command line.
    2. My script is cool, but I wish I could verify if a user exists sooner (I end up forcing the user to go through that password step).
    3. The script has no exception handling (I’m giving myself a pass on this since I’m just now getting into PL/SQL).
    4. Spend some time with the documentation on one screen and your script on another. After some time, you can actually understand how everything interacts.

    One final thought. My process required a lot of trial and error, but seeing how everything flows and works is entertaining.

    Please sample/remix my code; it’s located in my GitHub blog repo. Make it better, and let me know what you come up with! And be sure to check out ORDS and SQLcl 😍!

    Follow

    And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!

  • ORDS, SQLcl, APEX via YUM and DNF

    TL;DR

    BREAKING NEWS!! APEX packages with ORDS and SQLcl now available in Oracle Linux YUM servers and through ULN

    The deets

    Here is what you need to know. We are releasing three new APEX (a.k.a. “The Artist formerly known as Oracle Application Express”) packages for the following repositories:

    • ol7_oci_included
    • ol8_oci_included
    • ol9_oci_included

    NoARCH RPMs

    One more update! These RPMs will be packaged as NoArch. This means you will be free to install these on any CPU architecture (Intel x86_64, AMD, and ARM). As a reminder, OCI Always Free tenancy Compute Instances are available with:

    • Micro instances (I’m using a VM.Standard.E2.1.Micro; an AMD processor)
    • Ampere A1 Compute instances (Arm processor)
    DOCS: You can review details on our standard Compute Instance shapes as well as those available with an Always Free account.
    TIP: These will all be available through both the publicly available Oracle YUM servers and the Unbreakable Linux Network (ULN) channels. 

    Fortunately, I was provided advanced copies of the brand-new RPM files that will be included in their respective oci_included repositories. You’ll soon have access to no less than three flavors of APEX (and their dependencies). YUM!

    You’ll soon see the following packages:

    Oracle Application Express 23.1

    NOTE: In this write-up, and the images below, you'll see me reference this as apex-core.

    The Oracle Application Express package contains everything you need to install APEX in your database. We also package it with the SQLcl package to simplify installation.

    Oracle Application Express Images 23.1

    NOTE: In this write-up, and the images below, you'll see me reference this as apex-images.

    This Oracle Application Express Images package includes your APEX images (i.e., you may be familiar with this as these are symlinked to your /i/ virtual directory for Oracle APEX images). This package also contains the ORDS package, making it easier to run the APEX image of your choice.

    This package will also automatically add a symbolic link (aka symlink) from your APEX images files to your default ORDS doc_root path.

    Oracle Application Express Patch Set Bundle 23.1.3

    NOTE: In this write-up, and the images below, you'll see me reference this as apex-patch.

    The Oracle Application Express Patch Set Bundle provides a means for you to take the latest APEX patch sets, compare them against your existing base APEX images, overwrite those base files with the latest changes, and then place them into their own /opt/oracle/apex/[latest patch]/images directory. Oh, and you’ll also retain the APEX base images in their own /opt/oracle/apex/[apex base]/images directory too!

    And when you’re ready, you can update APEX in your database with the latest patches. The package even symlinks the newest patch set images to your APEX installation’s virtual image directory (i.e., the /i/ directory). And finally, we automatically symlink (as mentioned in the Oracle Application Express Images 23.1 section) this folder to your default ORDS doc_root path.

    REMEMBER: If you create a Compute Instance in OCI, these repositories will automatically be accessible to you (if you aren't though, check this resource out to get set up)!

    A YUM, DNF, and Oracle Linux reminder

    A week ago, I had limited (i.e., zero) experience with the Linux package managers Yellowdog Updater Modified (YUM) and Dandified YUM (DNF). But since then, I’ve tinkered around with Oracle Linux (OL) quite a bit. I even successfully installed Oracle Linux 8 (OL8) on an old Raspberry PI 3 Model B V1.2 (which, if you can believe it, was first released in 2016)!

    PAUSE: If you want to see a tutorial of I how I did this, let me know in the comments, or @ me on Twitter/X or Threads.

    Exploring OL8 and OCI Compute Instances

    If you have an OCI Always Free account, you too can get acquainted with Oracle Linux 8 (OL8), as that is provided when creating a new Compute Instance in your OCI tenancy.

    reviewing-instance-image-and-shape-yum-ol8-dnf-rpm-chris-hoina-senior-product-manager-oracle-database-ords-sqlcl-actions

    After creating that Instance, you can SSH into it and check out what Oracle Linux ships with. Once in, use one of these commands (depending on the version):

    # Command for Oracle Linux 7:
    yum list *release-el7
    
    # Command for Oracle Linux 8:
    dnf list *release-el8
    
    # Command for Oracle Linux 9:
    dnf list *release-el9

    And here I am checking the packages and repositories (with the dnf list *release-ol8 command) in my Compute Instance:

    dnf-list-release-command-for-oracle-linux-info-chris-hoina-senior-product-manager-oracle-database-ords-sqlcl-actions

    Since I first created this Compute Instance, I’ve been tinkering with DNF and the repositories, so your output might look different. But at a minimum, you can expect to see the ol8_oci_included and ol8_baseos_latest repositories.

    Oracle Database Tools via YUM or DNF

    NOTE: This next section WILL NOT apply if you are working with the Oracle Linux that is provided in your OCI Compute Instance!!!

    You won’t find a direct link to our Database Tools repository if you do this independently (i.e., not creating an OCI Compute Instance as I did). That is an easy fix; you can manually configure the repositories.

    NOTE: If you are doing this through OCI (as in with a newly-created Compute Instance), you won't need to configure this, it will be included in the ol8_oci_included repository (this applies to OL7 and OL9 too)!

    But for manual configuration, here are the direct links for the available Oracle Linux versions (you’ll want to use whichever one corresponds to your Linux version):

    • Oracle Linux 7 http://yum.oracle.com/repo/OracleLinux/OL7/oracle/software/x86_64
    • Oracle Linux 8 http://yum.oracle.com/repo/OracleLinux/OL8/oracle/software/x86_64

    You can manually configure the repositories like this:

    adding-database-tools-repository-via-dnf-on-oracle-linux-8-chris-hoina-senior-product-manager-oracle-database-ords-sqlcl-actions

    You can then inspect the packages in this repository with the following command:

    dnf repository-packages [the repo id] info --all
    inspecting-the-newly-created-repository-and-packages-oracle-linux-8-chris-hoina-senior-product-manager-oracle-database-ords-sqlcl-actions

    I can now download our ORDS and SQLcl packages, our two must-haves:

    In the above images, you can see these packages are being installed from the ol8_oci_included repository. This is because Compute Instances in OCI automatically have access to the Unbreakable Linux Network (ULN); you can read all about it in the OCI documentation found here.

    NOTE: Once again, taking the extra step to configure this additional repository is unnecessary; I'm only demonstrating how to do it if you are working with something other than an OCI Compute Instance.

    The big reveal/super secret surprise

    If you’ve been working with Linux (and specifically Oracle Linux), then none of this will be much of a surprise to you. And you probably (definitely) know more about this than I do. But what you DON’T know is that we are releasing three new APEX packages for the ol7_oci_included, ol8_oci_included, and ol9_oci_included repositories.

    Since I know a guy (thanks, Adrian 🤫), I was provided advanced copies of the brand-new RPM files that will be included in the respective oci_included repositories. You’ll soon have access to three flavors of APEX (and their dependencies):

    Oracle Application Express 23.1

    The Oracle Application Express package contains everything you need to install APEX in your database. We also package it with the SQLcl package to simplify installation.

    Oracle Application Express Images 23.1

    The Oracle Application Express Images package includes your APEX images (i.e., you may be familiar with this as these are symlinked to your /i/ virtual directory). This package also contains the ORDS package, making it easier to run the APEX image of your choice.

    It will also automatically add a symbolic link (aka symlink) from your APEX image files to your default ORDS doc_root path.

    Oracle Application Express Patch Set Bundle 23.1.3

    The Oracle Application Express Patch Set Bundle provides a means for you to take the latest APEX patch sets, compare them against your existing base APEX images, overwrite those base files with the latest changes, and then place them into their own /opt/oracle/apex/[latest patch]/images directory. Oh, and you’ll also retain the APEX base images in their own /opt/oracle/apex/[apex base]/images directory too!

    And when you’re ready, you can update APEX in your database with the latest patches. The package even symlinks the newest patch set images to your APEX installation’s virtual image directory (i.e., the /i/ directory). And finally, we automatically symlink (as mentioned in the Oracle Application Express Images 23.1 section) this folder to your default ORDS doc_root path.

    REMEMBER: If you create a Compute Instance in OCI, these repositories will automatically be accessible to you (if you aren't though, check this resource out to get set up)!

    That was a lot of words. But in practice, the following images illustrate what to expect when you issue the dnf install command (for the APEX-CORE or APEX-IMAGES packages).

    APEX-CORE

    REMINDER: The official package name will be Oracle Application Express 23.1.
    rpm-installing-apex-core-package-oracle-linux-8-chris-hoina-senior-product-manager-oracle-database-ords-sqlcl-actions
    APEX-CORE install via a local RPM
    completing-the-package-installation-for-apex-core-oracle-linux-8-chris-hoina-senior-product-manager-oracle-database-ords-sqlcl-actions
    Completing the APEX-CORE package installation, revealing helpful installation information

    APEX-IMAGES

    REMINDER: The official package name will be Oracle Application Express Images 23.1.

    Alternatively, you may also decide to install the APEX-IMAGES packages (if you intend on using many versions of APEX along with its ORDS companion):

    completing-the-package-installation-for-apex-images-oracle-linux-8-chris-hoina-senior-product-manager-oracle-database-ords-sqlcl-actions
    APEX-IMAGES install via a local RPM
    completing-the-package-installation-for-apex-images-oracle-linux-8-chris-hoina-senior-product-manager-oracle-database-ords-sqlcl-actions
    Completing the APEX-IMAGES package installation, revealing helpful installation information

    Once these packages are available in the ol7_oci_included, ol8_oci_included, and ol9_oci_included repositories, install commands become much more straightforward. You won’t have to bother with the individual RPM files; you can issue either the yum install or dnf install commands.

    NOTE: We switched to DNF for OL8 and later but the commands are symlink'd so I believe either will work. You can read about that here.

    The only thing you have to do now is decide which packages you’ll want to install!

    Check for updates

    This last section is just a reminder. If you haven’t done so yet, check for updates to all your repositories (namely the ol7_oci_included, ol8_oci_included, and ol9_oci_included repositories).

    TIP: You can issue the yum --help or dnf --help command to refresh your memory on which "update" command to use. 
    issuing-dnf-help-command-oracle-linux-8-chris-hoina-senior-product-manager-oracle-database-ords-sqlcl-actions

    But once again, I suspect you already know how to do this if you have more than a week’s experience with Oracle Linux, YUM, or DNF. Be on the lookout for updates to the Oracle Linux release notes too!

    Other stuff

    The end.

    Follow

    And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!

  • Oracle Database REST APIs and Apple Automator Folder Actions

    Oracle Database REST APIs and Apple Automator Folder Actions

    The plan was to create an ORACLE REST endpoint and then POST a CSV file to that auto-REST enabled table (you can see how I did that here, in section two of my most recent article). But, instead of doing this manually, I wanted to automate this POST request using Apple’s Automator application…

    Me…two paragraphs from now

    Follow along with the video


    The plan

    I did it. I went so far down the rabbit hole, I almost didn’t make it back alive. I don’t know when this ridiculous idea popped into my head, but it’s been well over a year. Until now, I either hadn’t had the time or the confidence to really tackle it.

    The plan was to create an ORACLE REST endpoint and then POST a CSV file to that auto-REST enabled table (you can see how I did that here, in section two of my most recent article). But, instead of doing this manually, I wanted to automate this POST request using Apple’s Automator application.

    The use case I made up was one where a person would need to periodically feed data into a table. The data doesn’t change, nor does the target table. Here is an example of the table I’m using:

    bank transfer table structure chris hoina, senior product manager, ords, oracle rest, database tools, sqlcl, sql developer, oracle rest
    The basic structure of the Bank Transfers table

    And the DDL, should there be any interest:

    CREATE TABLE ADMIN.BANK_TRANSFERS
    (TXN_ID NUMBER ,
    SRC_ACCT_ID NUMBER ,
    DST_ACCT_ID NUMBER ,
    DESCRIPTION VARCHAR2 (4000) ,
    AMOUNT NUMBER
    )
    TABLESPACE DATA
    LOGGING
    ;

    Once this table was created, I auto-REST enabled the table and retrieved the complete cURL Command for performing a Batch Load request. Remember, we have three examples for cURL Commands now, I chose Bash since I’m on a Mac:

    batchload curl command for bank transfers table, chris hoina, senior product manager, ords, oracle rest, database tools, sqlcl, sql developer, oracle rest
    Retrieving the the Batch Load cURL Command

    Once I grabbed the cURL Command, I would temporarily save it to a clipboard (e.g. VS Code, TextEdit, etc.). I’d then create a new folder on my desktop.

    ords curl post new folder for batch load automation, chris hoina, senior product manager, ords, oracle rest, database tools, sqlcl, sql developer, oracle rest
    The newly created ords_curl_post folder

    How I actually did it

    I’d then search via Spotlight for the Automator application. Once there, I’d choose Folder Action.

    choose automator folder action, chris hoina, senior product manager, ords, oracle rest, database tools, sqlcl, sql developer, oracle rest
    Choosing Folder Action for this automation
    HEY!! README: I'm going to breeze through this. And it may seem like I am well-aquainted with this application. I am not. I spent hours debugging, reading through old StackExchange forums, and Apple documentation so I could share this with you. There is a ton more work to do. But bottom line, this thing works, and its something that is FREE and accessible for a lot of people. You could have a TON of fun with this stuff, so keep reading!

    There’s no easy way to get around this, but to get really good at this, you’ll just need to tinker. Luckily, most of these automation modules are very intuitive. And there is a ton of information online on how to piece them all together.

    Automator 🤖

    All of these modules are drag-and-drop, so it makes it easy to create an execution path for your Folder Action application. Eventually, I ended up with this (don’t worry, I’ll break it down some, a video is in the works for a more detailed overview):

     complete folder action automation, chris hoina, senior product manager, ords, oracle rest, database tools, sqlcl, sql developer, oracle rest
    Complete Folder Action automation for the ORDS Batch Load request

    The modules

    The modules I’m using are:

    • Get Specified Finder Items
    • Get Folder Contents
    • Run Shell Script (for a zsh shell, the default for this MacBook)
    • Set Value of Variable
    • Get Value of Variable
    • Display Notification

    You can see at the very top, that I have to choose a target folder since this is a folder action. I chose the folder I created; ords_curl_post.

    Get Specified Finder Items and Get Folder Contents

    The first two modules are pretty straightforward. You get the specified finder items (from that specific folder). And then get the contents from that folder (whatever CSV file I drop in there). That will act as a trigger for running the shell script (where the filename/s serve as the input for the cURL Command).

    PAUSE: I must confess, I had essentially ZERO experience in shell scripting prior to this, and I got it to work. Its probably not the prettiest, but damn if I'm not stoked that this thing actually does what it is supposed to do.

    The only main considerations on this shell script are that you’ll want to stay with zsh and you’ll want to choose “as arguments” in the “Pass input” dropdown menu. Choosing “as arguments” allows you to take that file name and apply it to the For Loop in the shell script. I removed the echo "$f" because all it was doing was printing out the file name (which makes sense since it was the variable in this script).

    choose as arguments for the shell script, chris hoina, senior product manager, ords, oracle rest, database tools, sqlcl, sql developer, oracle rest
    Choosing “as arguments

    The Shell Script

    That cURL Command I copied from earlier looks like this:

    curl --location --request POST \
    --header "Content-Type: <CONTENT_TYPE>" \
    --data-binary @<FILE_NAME> \
    'https://abcdefghi1234567-darmok.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/bank_transfers/batchload' 

    I made some modifications though. I made sure Content-Type was text/csv. And then I added some fancy options for additional information (more details on this here, go nuts) when I get a response from the database.

    REMINDER: I didn't know how to do this until about 30 mins before I got it to work. I'm emphasizing this because I want to drive home the point that with time and some trial-and-error, you too can get something like this to work!

    With my changes, the new cURL Command looks like this:

    curl -w 'Response Code: %{response_code}\nTotal Time: %{time_total} seconds\nUpload Speed: %{speed_upload} bytes/sec\nUpload Size: %{size_upload} bytes' --location --request POST \
    --header "Content-Type: text/csv" \
    --data-binary @"$f" \
    'https://abcdefghi1234567-darmok.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/bank_transfers/batchload'

    What a mess…That -w option stands for write-out. When I receive the response from the Batch Load request, I’ll want the following information:

    • Response Code (e.g. like a 200 or 400)
    • Total Upload Time
    • Upload Speed
    • Upload Size

    All of that is completely optional. I just thought it would be neat to show it. Although, as you’ll see in a little bit, Apple notifications has some weird behavior at times so you don’t really get to see all of the output.

    I then applied the cURL command to the shell script, (with some slight modifications to the For Loop), and it ended up looking like this:

    new for loop with updated curl command, chris hoina, senior product manager, ords, oracle rest, database tools, sqlcl, sql developer, oracle rest
    New shells script with updated cURL command

    Here is what the output looked like when I did a test run (with a sample CSV):

    output of curl command, chris hoina, senior product manager, ords, oracle rest, database tools, sqlcl, sql developer, oracle rest
    Success on the cURL command

    Set Value of Variable

    All of that output, referred to as “Results”, will then be set as a variable. That variable will be henceforth known as the responseOutput (Fun fact: that is called Camel casing…I learned that like 3-4 months ago). You’ll first need to create the variable, and once you run the folder action, it’ll apply the results to that variable. Like this:

    Get Value of Variable and Display Notification

    Those next two modules simply “GET” that value of the variable/results and then sends that value to the Display Notification module. This section is unremarkable, moving on.

    And at this point, I was done. All I needed to do was save the script and then move on to the next step.

    Folder Actions Setup

    None of this will really work as intended until you perform one final step. I’ll right-click the target folder and select “Folder Actions Setup.” From there a dialog will appear; you’ll want to make sure both the folder and the script are checked.

    Trying it out

    Next, I emptied the folder. Then I dropped in a 5000-row CSV file and let Folder Actions do its thing. This entire process is quick! I’m loving the notification, but the “Show” button simply does not work (I think that is a macOS quirk though). However, when I go back to my Autonomous Database, I can 100% confirm that this ORDS Batch Load worked.

    Final thoughts

    This was relatively easy to do. In total, it took me about 3-4 days of research and trial and error to get this working. There is a lot I do not know about shell scripting. But even with a rudimentary understanding, you too can get this to work.

    Next, I’d like to create a dialog window for the notification (the output from the cURL Command). I believe you can do that in AppleScript; I just don’t know how yet.

    If you are reading this and can think of anything, please leave a message! If you want to try it out for yourself, I’ve shared the entire workbook on my GitHub repo; which can be found here.

    I’ll also be doing an extended video review of this, where I’ll recreate the entire automation from start to finish. Be on the lookout for that too!