Saturday, January 20, 2018

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/welcome1@ORCL

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/orderapp@pdb1
@/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/welcome1@PDB1 @/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.

38 comments:

akash thakur said...

hello sir,
thanks for giving that type of information.Digital PVC Door suppliers in Karnataka

PassBraindumps Platform-App-Builder said...

The blog is really appreciable and i like to keep on visiting this site MB6-895 exam test questions and answers once again that it would help me in further thanks for sharing the info.

ebook conversion said...

Ebookconversion.com offers high quality conversion of your PDF documents to Kindle format. So, find easy ways to transfer your PDF file into Kindle Mobi.

Anonymous said...

An amazing blog to go through and renew thoughts about utilizing SQL* Plus for categorizing mobile apps and websites with respect to local and shared drives. Great to find the traditional methods of writing program commands and providing the reference data for applications to run on dockerized Oracle Database. Thanks for the blog post.

Farah from Way2Smile - (An Expert Cloud Solution Provider in Dubai.)

Anonymous said...
This comment has been removed by the author.
Unknown said...

Friend, this web site might be fabolous, i just like it.

Best Software Development Agency Dubai UAE

Unknown said...

If you set out to make me think today; mission accomplished!I really like your writing style and how you express your ideas.Thank you.


Digital marketing Agency

Unknown said...

Friend, this web site might be fabolous, i just like it.


Web Development Agency Dubai UAE

David Stangley said...

Quran Learn Academy is offering online Quran tuition at very affordable rates. Learn Quran to keep your dunya and akhirah peaceful.

David Stangley said...

Shurooq, one of leading Pakistani ladies clothing brands is offering womens clothing online at cost friendly rates. Order now and buy your favorite one.

join to nagaqq said...


Nagaqq Yang Merupakan Agen Bandarq terbaik , Domino 99, Dan Bandar Poker Online Terpercaya di asia hadir untuk anda semua dengan permainan permainan menarik dan bonus menarik untuk anda semua

Bonus yang diberikan NagaQQ :
* Bonus rollingan 0.5%,setiap senin di bagikannya
* Bonus Refferal 10% + 10%,seumur hidup
* Bonus Jackpot, yang dapat anda dapatkan dengan mudah
* Minimal Depo 15.000
* Minimal WD 20.000
* Deposit via Pulsa TELKOMSEL
* 6 JENIS BANK ( BCA , BNI, BRI , MANDIRI , CIMB , DANAMON )

Memegang Gelar atau title sebagai AGEN POKER ONLINE Terbaik di masanya

Games Yang di Hadirkan NagaQQ :
* Poker Online
* BandarQ
* Domino99
* Bandar Poker
* Bandar66
* Sakong
* Capsa Susun
* AduQ
* Perang Bacarrat
* Perang Dadu (New Game)


Info Lebih lanjut Kunjungi :
Website : NAGAQQ
Facebook : NagaQQ official
WHATSAPP : +855977509035
Line : Cs_nagaQQ
TELEGRAM :+855967014811

BACA JUGA BLOGSPORT KAMI YANG LAIN:
Winner NagaQQ
Daftar NagaQQ
nagaqq

David Stangley said...

I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post.

womens clothing online

Google AdSense said...

Great Thanks! Are you a blogger and want to know Ways To Make Money From Blogging

David Stangley said...

Excellent and very exciting site. Love to watch. Keep Rocking.

billing software for small businesses

David Stangley said...

howdy, your websites are really good. I appreciate your work.

best dental surgeon in Lahore

David Stangley said...

Thanks so much for this information.I have to let you know I concur on several of the points you make here and others may require some further review, but I can see your viewpoint.

cloud based inventory management software

David Stangley said...

This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.

best invoicing software for small business

Caring said...

I haven’t any word to appreciate this post.....Really i am impressed from this post....the person who create this post it was a great human..thanks for shared this with us.

Needle Bearings Cam Follower Series

Unknown said...

I haven’t any word to appreciate this post.... Really, I am impressed from this post.... the person who creates this post it was a great human. Thanks for shared this with us. Women Hoodies & Sweatshirts
Wholesale Women Hoodies

David Carter said...

I was surfing the Internet for information and came across your blog. I am impressed by the information you have on this blog. It shows how well you understand this subject. Best SMM Panel
SMM Reseller Panel

Agent Host said...

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info. ppc services new york
content marketing services usa

Numbers Pro said...

This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free. Tax Agent in Craigieburn
Accounting Bookkeeping Service
Tax Agent in Melbourne

Michael Oliver said...

Good blog,

Digital Marketing Companies in Chennai, Website Design Companies in Chennai, SEO Companies in Chennai, Digital Marketing Company Chennai, Web Design Company Chennai

https://wisewebtek.com


Unknown said...

https://telegra.ph/Melhores-maneiras-de-ganhar-seguidores-no-Instagram-07-14


https://digitalmarketinglahore.mystrikingly.com/blog/melhor-lugar-para-comprar-seguidores-no-instagram-reais-e-ativos


https://k12.instructure.com/eportfolios/71209/Home/Por_que_e_onde_comprar_curtidas_no_Instagram_com_avaliao_gratuita



http://articles.mpeblog.com/25740464/compre-seguidores-instant-neos-no-instagram-para-aumentar-seu-envolvimento-com-o-instagram


https://www.ranker.com/list/top-instagram-tips-to-get-free-followers-on-instagram-without-verification-or-search/naveed


https://peatix.com/event/1997957/view

https://blog1.dreamwidth.org/755.html

https://penzu.com/public/5686fd97

https://www.pearltrees.com/naveediq/item376852403



https://hoaxbuster.com/forum/viewtopic.php?f=3&t=15112



Anton Sanjaya said...

Aslijudi Situs Judi Online Terbaik Dan Terpercaya Dengan Tingkat Keamanan Dan Kenyamanan Yang Sangat Terjamin.
Tingkat Winrate yang Sangat Tinggi Dengan Kemudahan jackpot Terbesar.

Prediksi Parlay
Livescore

Best Software Development Company said...

Hey There
Thanks for sharing this informative post.
If your looking for Best software development services
visit Cubestech - Best Software Development company

SREEJITHA said...

THANKS FOR SHARING THIS POSt
READ-https://lilacinfotech.com/blog/112/Taxi-Dispatch-Apps-Tips-To-Improve-Your-Taxi-Business

Gowri said...

This blog is a great source of information which is very useful for me. Thank you very much for sharing this!

Android development companies in Chennai
Mobile app development company in Chennai
app development in chennai
App development companies in Chennai
Mobile app development company Chennai
android development companies in chennai

oceansoftwaressms123 said...

Very good article post. Thanks Again. Great. You are doing a very good job.

Mobile app development company in chennai
Mobile Application Development companies in chennai
Android development companies in chennai
Mobile app development company chennai

Rising Quran Online School said...

If you’re looking for Online Quran Classes, then you’re in the right place. Rising Quran is an online school, mainly for students from all over the world. We welcome people from all over the world to learn about the Quran and the commandments of Islam regardless of race, color or cast.
Contact us
Learn Online Quran Courses
Learn Online Tuition Courses

Gowripriya said...

This blog is a great source of information that is very useful for me. Thank you very much for sharing this!

Financial Consultant in chennai
Certified Financial Planner (CFP)
investment consultants in chennai
financial consultant in madhavaram
tamil nadu financial advisors
MUTUAL FUND ADVISOR IN PERAMBUR
Financial consultant in kolathur

Amrita said...

Good

focus billing software
payroll
hcm software
HCM payroll software
hcm saas companies
top 10 HCM software
best hcm solutions
best hcm companies
warehouse management system
best warehouse management system

Social media services said...
This comment has been removed by the author.
SMM Panel said...

Looking for the most cheapest SMM panel India? We provide the most cost-effective social media marketing solutions to increase your online visibility and engagement.

SMM Panel said...

Looking for a low-cost way to increase your social media presence? Check out our dependable and cheap SMM panel for all of your marketing demands.

Gowripriya said...

used compact excavator for sale
used dump truck for sale
used wheel loaders for sale
used heavy duty rigging equipment for sale
used paver machine for sale in india

Dazzle Accessories said...

Anklets have been worn by women for centuries in various real anklets design, and they are often associated with femininity, beauty, and elegance.

Artificial Jewelry Pakistan said...

Wedding necklace design gucci pendant variety product