Create a Playground - The Challenges of Scripting

Now that we have the database running and can connect to it, we need nice sample data to create good demos for more learning to come. Seasoned Oracle developers will remember the SCOTT or HR schemas. Let's use something different. My colleague Friedhold  created an Oracle version of the Northwind database.

First we need to create a new Oracle schema as a home for our sample data:

CREATE USER "NORTHWIND" IDENTIFIED BY "password";

GRANT CREATE SESSION TO northwind;

GRANT CREATE table TO northwind;

GRANT CREATE SEQUENCE TO northwind;

GRANT CREATE PROCEDURE TO northwind;

GRANT CREATE TRIGGER TO northwind;

ALTER USER northwind QUOTA 200M ON DATA;

BEGIN
   ORDS_ADMIN.ENABLE_SCHEMA(
     p_enabled => TRUE,
     p_schema => 'northwind',
     p_url_mapping_type => 'BASE_PATH',
     p_url_mapping_pattern => 'northwind',
     p_auto_rest_auth => TRUE
   );
   COMMIT;
END;
/


This script can be executed in a SQL Editor of DBeaver or in the SQL Worksheet of the SQL Developer Web of our Cloud Database. Connect with the admin user. The last portion, the PL/SQL anonymous block enables access to the new schema from SQL Developer web and ORDS.

Now that we have the schema created, let's open the Northwind table creation script that we downloaded from Github in DBeaver. It looks like this:


Execute this as a script would be the next logical step but DBeaver does not really know about SQL*Plus specific commands like PROMPT. There are some ways out of that:

  • Manually edit the script and remove Oracle specific script commands (suitable for small scripts)
  • Use the Worksheet of the SQL Developer Web
  • Download and install SQL Developer
  • Download and install the SQL*Plus Add-On for the Oracle Instant Client

The easiest choice seems to be SQL Developer Web as we already have it. But it turned out that the web Worksheet cannot process longer scripts. It may refuse working, at least in my experience. So if we think we have to deal with more Oracle SQL*Plus scripts, a native tool is the only choice. To have a light weight standard command line tool for the database at hand seems to be a good idea, so let's go with SQL*Plus.

Download the SQL*Plus add-on from the Instant Client page and unzip the content to your instant client directory.

To make it work, there are three more things:
  1. Because this is a SQL*Net connection, we need to provide the location of the wallet in sqlnet.ora:
    Edit the file in the [instantclient_home]/network/admin directory and set the wallet directory like:

    WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/Users/kaimoeller/Applications/Oracle/instantclient_19_3/network/admin")))

    Remember, we placed our ATP database wallet files there, now we need to tell SQL*Net where they are.
  2. Set the TNS_ADMIN environment variable to the same directory according to your computer's operating system
  3. Add your instant client directory to the PATH variable of your system to access SQL*Plus from everywhere

Let's test it!


Yeah, we're connected! We have control over our cloud database from the SQL*Plus prompt. 

Now we can run the northwind scripts. First the cr_Northwind.sql to create the tables, then the ins_Northwind.sql for the content.

Before running the ins_Northwind.sql script in an Oracle tool like SQL*Plus the line

SET DEFINE OFF 

should be added on top. It prevents misinterpretation of ampersands as placeholders.

If this is all to much right now and SQL*Plus scripting is not needed in near future, just do the following:
  1. cr_Northwind.sql: Execute statement by statement in a DBeaver SQL Editor connected to the Northwind schema. For this, create a new connection like shown in the recent post. The same driver can be used for that.
  2. ins_Northwind.sql: This script runs in DBeaver just as is without modification.
Check the results in DBeaver, for example the content of the CUSTOMERS table. Recognize the correct display of the European special characters that come with the example data of this version. In my environment on a Mac, it worked right away. The database's character set AL32UTF8.


I think we have achieved a lot. We have access to a free Oracle ATP Database instance with its own provided Web UI, by a powerful desktop tool (DBeaver) and with the Oracle's standard scripting tool (SQL*Plus). It is populated with a good set of data to explore more.

Comments

Post a Comment

Popular posts from this blog

Prototype for a Serverless Business Application

Image
 There are a lot of great tutorials out there on how to build applications based on AWS and in particular on Amplify. But I wanted to try out by myself how much effort it is, to build an application that is a bit closer to a real business desktop app meeting the following requirements: Serverless architecture Based on an RDBMS (relational database) Performing CRUD operations on entities Authentication and authorization Sound exception handling The result is an application serving currently one entity that can be queried and updated. Here's the architectural diagram: The question may be raised, if serverless and using Amplify , why not going NoSQL with DynamoDB? Here are some points that I find important: The majority of software projects is not greenfield. Complex relational data models exist with three digit numbers of tables, numerous (SQL based) use cases and interfaces that can hardly be migrated to a NoSQL data store For business applications in fields like ERP or financials, ...
Read more

Build a Secure Database Driven Web Application with Amplify and React: Part 1 - Setup

Image
In my recent post I have published a demo CRUD application utilizing React.js, AWS Amplify and PostgreSQL on Aurora. For everyone who did not want to sign up and take a look for any reason, here's a screenshot: In the next posts, I want to document how easy it is to build a secure serverless web application on top of Amplify using React as a frontend. AWS account AWS command line tools including amplify are installed and connected to the account Installed Node.js (version >=17) and React.js (version >=18.2) An IDE of choice like VS code Initialize the Project To set up the project, have a code editor ready and an terminal window with command prompt. First, we initialize the React application and give it a name like ardemo for Amplify-React Demo.      > npx create-react-app ardemo A directory with the project name will be created, we change to that and can run the intial React application right away:   > cd ardemo > npm start  By default, the node ...
Read more
Image
Time Flies - AWS Aurora serverless has grown up Maybe you remember my demo application leveraging AWS Aurora Serverless v1 on Postgres, using the built in data API and a React frontend. AWS has announced v1 deprecated a while ago and after a postponement, the end of v1 is likely coming by end of next month (March 2025). The AWS community was not so amazed about having to migrate to Aurora v2 as it lacked two important features:  Scaling down to zero which is important for low budget organizations or projects. The data API, providing a highly convenient way to communicate with an RDBMS without any hassle in drivers and connection pooling Now here's the good news: AWS has added both features to Aurora v2 in the meantime, and there is even more: The data API request limit of 1000 requests per second does was removed for v2 . Due to the improved architecture, up- and downscaling performs faster. In particular, scaling up from 0 (the wake up scenario) is faster and results in improved...
Read more