SYS-CON MEDIA Authors: Elizabeth White, Yeshim Deniz, Roger Strukhoff, Jason Bloomberg, Pat Romanski

Blog Feed Post

Using SQL*Plus to Seed your Dockerized Oracle Database

In my last blog, you learned  to create a containerized Oracle database for your development/testing purpose. You also know how to connect to your container and run command in the container.

Most applications require some reference data e.g. example,my  OrderApp application based on Apache Tom EE  requires catalog data to be pre-populated before I can test my application. 

One of the readers asked me how can we run a SQL script on his local or shared drive to seed the containerized database.

In this blog, I will show how you can execute scripts with SQL*Plus inside the container to seed your dockerized Oracle database.

Connecting to SQLPlus


In the last blog, we learned that ORACLE_HOME for the database running in the container is /u01/app/oracle/product/12.1.0/dbhome_1.

I can connect to the database by running the following command:

docker exec -it orcldb /u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus system/[email protected]

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 20 06:22:58 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Jan 14 2018 03:09:54 +00:00

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL>


You might remember ORCL was the instance name that provided for my database.

Also note that when I run the command, SQL*Plus is getting executed inside the container.

Running a SQL script Using SQLPlus


As the command is getting executed inside the container running the Oracle database, the SQL script has to be accessible from the container.

My Script

My application depends upon a user in the PDB. My script creates the user, creates tables in that user's schema and populates data in those tables. 

I have a script named user.sql that I want to execute and here are the contents of /Users/dpanda/orderapp2/orcl/sql/user.sql script.


create user orderapp identified by orderapp2
default tablespace users temporary tablespace temp
/

alter user orderapp quota unlimited on users
/
grant connect, resource to orderapp
/
connect orderapp/[email protected]
@/u04/app/sql/sample_oow_tomcat_create.sql
@/u04/app/sql/sample_oow_productline.sql
commit;
exit;

As I am invoking the SQL*Plus inside the container, I have to specify the drive inside the container.

Mapping Volume from the Container to Local or Shared Drive

You might remember from the last blog that when I started the Database container, I mapped the drive in /u04/app in the container to /Users/dpanda/orderapp2/orcl by using –v option as below:

docker run -d --env-file db.properties -p 1521:1521 -p 5500:5500 --name orcldb --net appnet  --shm-size="4g" -v /Users/dpanda/orderapp2/orcl:/u04/app:/u04/app container-registry.oracle.com/database/standard




The script directory has to be specified as /u04/app/sql as my script is located in /Users/dpanda/orderapp2/orcl/sql directory on my MAC .


Here is the docker command I can use to run my script:

docker exec -it orcldb
/u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus system/[email protected] @/u04/app/sql/user

As you can see, I can connecting to the pdb1 database by executing SQLPlus command and running the user.sql script.

Here is the output you will get

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 13 06:16:32 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sat Jan 13 2018 06:16:19 +00:00

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production


User created.


User altered.


Grant succeeded.

…..


1 row created.


1 row created.


1 row created.


Commit complete.

Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production


Hope this helps to automate your script to seed your containerized Oracle database.


In a future blog, I will demonstrate how can you Oracle Instant Client in a Docker container to automate your scripts.

Read the original blog entry...

More Stories By Debu Panda

Debu Panda is a Director of Product Management at Oracle Corporation. He is lead author of the EJB 3 in Action (Manning Publications) and Middleware Management (Packt). He has more than 20 years of experience in the IT industry and has published numerous articles on enterprise Java technologies and has presented at many conferences. Debu maintains an active blog on enterprise Java at http://debupanda.blogspot.com.

Latest Stories
Darktrace is the world's leading AI company for cyber security. Created by mathematicians from the University of Cambridge, Darktrace's Enterprise Immune System is the first non-consumer application of machine learning to work at scale, across all network types, from physical, virtualized, and cloud, through to IoT and industrial control systems. Installed as a self-configuring cyber defense platform, Darktrace continuously learns what is ‘normal' for all devices and users, updating its understa...
As you know, enterprise IT conversation over the past year have often centered upon the open-source Kubernetes container orchestration system. In fact, Kubernetes has emerged as the key technology -- and even primary platform -- of cloud migrations for a wide variety of organizations. Kubernetes is critical to forward-looking enterprises that continue to push their IT infrastructures toward maximum functionality, scalability, and flexibility. As they do so, IT professionals are also embr...
Industry after industry is under siege as companies embrace digital transformation (DX) to disrupt existing business models and disintermediate their competitor’s customer relationships. But what do we mean by “Digital Transformation”? The coupling of granular, real-time data (e.g., smartphones, connected devices, smart appliances, wearables, mobile commerce, video surveillance) with modern technologies (e.g., cloud native apps, big data architectures, hyper-converged technologies, artificial in...
At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
CloudEXPO has been the M&A capital for Cloud companies for more than a decade with memorable acquisition news stories which came out of CloudEXPO expo floor. DevOpsSUMMIT New York faculty member Greg Bledsoe shared his views on IBM's Red Hat acquisition live from NASDAQ floor. Acquisition news was announced during CloudEXPO New York which took place November 12-13, 2019 in New York City.
OpsRamp is an enterprise IT operation platform provided by US-based OpsRamp, Inc. It provides SaaS services through support for increasingly complex cloud and hybrid computing environments from system operation to service management. The OpsRamp platform is a SaaS-based, multi-tenant solution that enables enterprise IT organizations and cloud service providers like JBS the flexibility and control they need to manage and monitor today's hybrid, multi-cloud infrastructure, applications, and wor...
The Master of Science in Artificial Intelligence (MSAI) provides a comprehensive framework of theory and practice in the emerging field of AI. The program delivers the foundational knowledge needed to explore both key contextual areas and complex technical applications of AI systems. Curriculum incorporates elements of data science, robotics, and machine learning-enabling you to pursue a holistic and interdisciplinary course of study while preparing for a position in AI research, operations, ...
Codete accelerates their clients growth through technological expertise and experience. Codite team works with organizations to meet the challenges that digitalization presents. Their clients include digital start-ups as well as established enterprises in the IT industry. To stay competitive in a highly innovative IT industry, strong R&D departments and bold spin-off initiatives is a must. Codete Data Science and Software Architects teams help corporate clients to stay up to date with the mod...
Tapping into blockchain revolution early enough translates into a substantial business competitiveness advantage. Codete comprehensively develops custom, blockchain-based business solutions, founded on the most advanced cryptographic innovations, and striking a balance point between complexity of the technologies used in quickly-changing stack building, business impact, and cost-effectiveness. Codete researches and provides business consultancy in the field of single most thrilling innovative te...
Atmosera delivers modern cloud services that maximize the advantages of cloud-based infrastructures. Offering private, hybrid, and public cloud solutions, Atmosera works closely with customers to engineer, deploy, and operate cloud architectures with advanced services that deliver strategic business outcomes. Atmosera's expertise simplifies the process of cloud transformation and our 20+ years of experience managing complex IT environments provides our customers with the confidence and trust tha...
CloudEXPO has been the M&A capital for Cloud companies for more than a decade with memorable acquisition news stories which came out of CloudEXPO expo floor. DevOpsSUMMIT New York faculty member Greg Bledsoe shared his views on IBM's Red Hat acquisition live from NASDAQ floor. Acquisition news was announced during CloudEXPO New York which took place November 12-13, 2019 in New York City. Our Silicon Valley 2019 schedule will showcase 200 keynotes, sessions, general sessions, power panels, and...
With the introduction of IoT and Smart Living in every aspect of our lives, one question has become relevant: What are the security implications? To answer this, first we have to look and explore the security models of the technologies that IoT is founded upon. In his session at @ThingsExpo, Nevi Kaja, a Research Engineer at Ford Motor Company, discussed some of the security challenges of the IoT infrastructure and related how these aspects impact Smart Living. The material was delivered interac...
ShieldX's CEO and Founder, Ratinder Ahuja, believes that traditional security solutions are not designed to be effective in the cloud. The role of Data Loss Prevention must evolve in order to combat the challenges of changing infrastructure associated with modernized cloud environments. Ratinder will call out the notion that security processes and controls must be equally dynamic and able to adapt for the cloud. Utilizing four key factors of automation, enterprises can remediate issues and impro...
Intel is an American multinational corporation and technology company headquartered in Santa Clara, California, in the Silicon Valley. It is the world's second largest and second highest valued semiconductor chip maker based on revenue after being overtaken by Samsung, and is the inventor of the x86 series of microprocessors, the processors found in most personal computers (PCs). Intel supplies processors for computer system manufacturers such as Apple, Lenovo, HP, and Dell. Intel also manufactu...
The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dynamic, 24/7/365 environment. There is no time to wait for long development cycles that produce software that is obsolete at launch. DevOps may be disruptive, but it is essential. DevOpsSUMMIT at CloudEXPO expands the DevOps community, enable a wide sharing of knowledge, and educate delegates and technology providers alike.