Archive for September, 2022

Docker Part 1 – Install Docker CE and Docker Compose in Oracle Linux Server 8.6 – OCI-IAAS

Posted by Sriram Sanka on September 29, 2022

In this Post, lets see how to Install Docker CE . I am using Oracle Linux Server 8.6 (Free Instance Offered by Oracle Cloud) .

Docker lets you build, test, and deploy applications quickly, Docker is a software platform that allows you to build, test, and deploy applications quickly. Docker packages software into standardized units called containers that have everything the software needs to run including libraries, system tools, code, and runtime. Using Docker, you can quickly deploy and scale applications into any environment and know your code will run.

Docker Compose is a tool that was developed to help define and share multi-container applications. With Compose, we can create a YAML file to define the services and with a single command, can spin everything up or tear it all down.

The big advantage of using Compose is you can define your application stack in a file, keep it at the root of your project repo (it’s now version controlled), and easily enable someone else to contribute to your project. Someone would only need to clone your repo and start the compose app

Note : If there is no repo available in your OS, try adding EPEL repo and Try again.

[root@certbot conf.d]# dnf config-manager --add-repo=https://download.docker.com/linux/centos/docker-ce.repo
Adding repo from: https://download.docker.com/linux/centos/docker-ce.repo

Once the Repo is Added, we are good to go …..

run this command “dnf install docker-ce docker-ce-cli containerd.io”

[root@certbot conf.d]# dnf install docker-ce docker-ce-cli containerd.io
Last metadata expiration check: 0:00:57 ago on Wed 21 Sep 2022 08:05:23 PM GMT.
Dependencies resolved.
=============================================================================================================================================================================================================================================
 Package                                                     Architecture                             Version                                                                      Repository                                           Size
=============================================================================================================================================================================================================================================
Installing:
 containerd.io                                               x86_64                                   1.6.8-3.1.el8                                                                docker-ce-stable                                     33 M
 docker-ce                                                   x86_64                                   3:20.10.18-3.el8                                                             docker-ce-stable                                     21 M
 docker-ce-cli                                               x86_64                                   1:20.10.18-3.el8                                                             docker-ce-stable                                     30 M
Installing dependencies:
 container-selinux                                           noarch                                   2:2.188.0-1.module+el8.6.0+20721+d8d917a9                                    ol8_appstream                                        59 k
 docker-ce-rootless-extras                                   x86_64                                   20.10.18-3.el8                                                               docker-ce-stable                                    4.6 M
 fuse-common                                                 x86_64                                   3.3.0-15.0.2.el8                                                             ol8_baseos_latest                                    22 k
 fuse-overlayfs                                              x86_64                                   1.9-1.module+el8.6.0+20721+d8d917a9                                          ol8_appstream                                        73 k
 fuse3                                                       x86_64                                   3.3.0-15.0.2.el8                                                             ol8_baseos_latest                                    55 k
 fuse3-libs                                                  x86_64                                   3.3.0-15.0.2.el8                                                             ol8_baseos_latest                                    95 k
 libcgroup                                                   x86_64                                   0.41-19.el8                                                                  ol8_baseos_latest                                    70 k
 libslirp                                                    x86_64                                   4.4.0-1.module+el8.6.0+20721+d8d917a9                                        ol8_appstream                                        70 k
 slirp4netns                                                 x86_64                                   1.2.0-2.module+el8.6.0+20721+d8d917a9                                        ol8_appstream                                        54 k
Installing weak dependencies:
 docker-scan-plugin                                          x86_64                                   0.17.0-3.el8                                                                 docker-ce-stable                                    3.8 M
Enabling module streams:
 container-tools                                                                                      ol8

Transaction Summary
=============================================================================================================================================================================================================================================
Install  13 Packages

Total download size: 92 M
Installed size: 365 M
Is this ok [y/N]: y
Downloading Packages:
(1/13): docker-ce-20.10.18-3.el8.x86_64.rpm                                                                                                                                                                   12 MB/s |  21 MB     00:01
(2/13): docker-ce-rootless-extras-20.10.18-3.el8.x86_64.rpm                                                                                                                                                  8.1 MB/s | 4.6 MB     00:00
(3/13): docker-scan-plugin-0.17.0-3.el8.x86_64.rpm                                                                                                                                                           9.5 MB/s | 3.8 MB     00:00
(4/13): docker-ce-cli-20.10.18-3.el8.x86_64.rpm                                                                                                                                                               10 MB/s |  30 MB     00:02
(5/13): fuse-common-3.3.0-15.0.2.el8.x86_64.rpm                                                                                                                                                               78 kB/s |  22 kB     00:00
(6/13): fuse3-3.3.0-15.0.2.el8.x86_64.rpm                                                                                                                                                                    650 kB/s |  55 kB     00:00
(7/13): fuse3-libs-3.3.0-15.0.2.el8.x86_64.rpm                                                                                                                                                               7.8 MB/s |  95 kB     00:00
(8/13): container-selinux-2.188.0-1.module+el8.6.0+20721+d8d917a9.noarch.rpm                                                                                                                                  11 MB/s |  59 kB     00:00
(9/13): fuse-overlayfs-1.9-1.module+el8.6.0+20721+d8d917a9.x86_64.rpm                                                                                                                                         10 MB/s |  73 kB     00:00
(10/13): containerd.io-1.6.8-3.1.el8.x86_64.rpm                                                                                                                                                              9.9 MB/s |  33 MB     00:03
(11/13): libcgroup-0.41-19.el8.x86_64.rpm                                                                                                                                                                    206 kB/s |  70 kB     00:00
(12/13): libslirp-4.4.0-1.module+el8.6.0+20721+d8d917a9.x86_64.rpm                                                                                                                                           219 kB/s |  70 kB     00:00
(13/13): slirp4netns-1.2.0-2.module+el8.6.0+20721+d8d917a9.x86_64.rpm                                                                                                                                        2.2 MB/s |  54 kB     00:00
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                                                         27 MB/s |  92 MB     00:03
Docker CE Stable - x86_64                                                                                                                                                                                     85 kB/s | 1.6 kB     00:00
Importing GPG key 0x621E9F35:
 Userid     : "Docker Release (CE rpm) <docker@docker.com>"
 Fingerprint: 060A 61C5 1B55 8A7F 742B 77AA C52F EB6B 621E 9F35
 From       : https://download.docker.com/linux/centos/gpg
Is this ok [y/N]: Y
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                                                                                     1/1
  Installing       : docker-scan-plugin-0.17.0-3.el8.x86_64                                                                                                                                                                             1/13
  Running scriptlet: docker-scan-plugin-0.17.0-3.el8.x86_64                                                                                                                                                                             1/13
  Installing       : docker-ce-cli-1:20.10.18-3.el8.x86_64                                                                                                                                                                              2/13
  Running scriptlet: docker-ce-cli-1:20.10.18-3.el8.x86_64                                                                                                                                                                              2/13
  Running scriptlet: container-selinux-2:2.188.0-1.module+el8.6.0+20721+d8d917a9.noarch                                                                                                                                                 3/13
  Installing       : container-selinux-2:2.188.0-1.module+el8.6.0+20721+d8d917a9.noarch                                                                                                                                                 3/13
  Running scriptlet: container-selinux-2:2.188.0-1.module+el8.6.0+20721+d8d917a9.noarch                                                                                                                                                 3/13
  Installing       : fuse3-libs-3.3.0-15.0.2.el8.x86_64                                                                                                                                                                                 4/13
  Running scriptlet: fuse3-libs-3.3.0-15.0.2.el8.x86_64                                                                                                                                                                                 4/13
  Installing       : containerd.io-1.6.8-3.1.el8.x86_64                                                                                                                                                                                 5/13
  Running scriptlet: containerd.io-1.6.8-3.1.el8.x86_64                                                                                                                                                                                 5/13
  Installing       : libslirp-4.4.0-1.module+el8.6.0+20721+d8d917a9.x86_64                                                                                                                                                              6/13
  Installing       : slirp4netns-1.2.0-2.module+el8.6.0+20721+d8d917a9.x86_64                                                                                                                                                           7/13
  Running scriptlet: libcgroup-0.41-19.el8.x86_64                                                                                                                                                                                       8/13
  Installing       : libcgroup-0.41-19.el8.x86_64                                                                                                                                                                                       8/13
  Running scriptlet: libcgroup-0.41-19.el8.x86_64                                                                                                                                                                                       8/13
  Installing       : fuse-common-3.3.0-15.0.2.el8.x86_64                                                                                                                                                                                9/13
  Installing       : fuse3-3.3.0-15.0.2.el8.x86_64                                                                                                                                                                                     10/13
  Installing       : fuse-overlayfs-1.9-1.module+el8.6.0+20721+d8d917a9.x86_64                                                                                                                                                         11/13
  Running scriptlet: fuse-overlayfs-1.9-1.module+el8.6.0+20721+d8d917a9.x86_64                                                                                                                                                         11/13
  Installing       : docker-ce-rootless-extras-20.10.18-3.el8.x86_64                                                                                                                                                                   12/13
  Running scriptlet: docker-ce-rootless-extras-20.10.18-3.el8.x86_64                                                                                                                                                                   12/13
  Installing       : docker-ce-3:20.10.18-3.el8.x86_64                                                                                                                                                                                 13/13
  Running scriptlet: docker-ce-3:20.10.18-3.el8.x86_64                                                                                                                                                                                 13/13
  Running scriptlet: container-selinux-2:2.188.0-1.module+el8.6.0+20721+d8d917a9.noarch                                                                                                                                                13/13
  Running scriptlet: docker-ce-3:20.10.18-3.el8.x86_64                                                                                                                                                                                 13/13
  Verifying        : containerd.io-1.6.8-3.1.el8.x86_64                                                                                                                                                                                 1/13
  Verifying        : docker-ce-3:20.10.18-3.el8.x86_64                                                                                                                                                                                  2/13
  Verifying        : docker-ce-cli-1:20.10.18-3.el8.x86_64                                                                                                                                                                              3/13
  Verifying        : docker-ce-rootless-extras-20.10.18-3.el8.x86_64                                                                                                                                                                    4/13
  Verifying        : docker-scan-plugin-0.17.0-3.el8.x86_64                                                                                                                                                                             5/13
  Verifying        : fuse-common-3.3.0-15.0.2.el8.x86_64                                                                                                                                                                                6/13
  Verifying        : fuse3-3.3.0-15.0.2.el8.x86_64                                                                                                                                                                                      7/13
  Verifying        : fuse3-libs-3.3.0-15.0.2.el8.x86_64                                                                                                                                                                                 8/13
  Verifying        : libcgroup-0.41-19.el8.x86_64                                                                                                                                                                                       9/13
  Verifying        : container-selinux-2:2.188.0-1.module+el8.6.0+20721+d8d917a9.noarch                                                                                                                                                10/13
  Verifying        : fuse-overlayfs-1.9-1.module+el8.6.0+20721+d8d917a9.x86_64                                                                                                                                                         11/13
  Verifying        : libslirp-4.4.0-1.module+el8.6.0+20721+d8d917a9.x86_64                                                                                                                                                             12/13
  Verifying        : slirp4netns-1.2.0-2.module+el8.6.0+20721+d8d917a9.x86_64                                                                                                                                                          13/13

Installed:
  container-selinux-2:2.188.0-1.module+el8.6.0+20721+d8d917a9.noarch          containerd.io-1.6.8-3.1.el8.x86_64              docker-ce-3:20.10.18-3.el8.x86_64            docker-ce-cli-1:20.10.18-3.el8.x86_64
  docker-ce-rootless-extras-20.10.18-3.el8.x86_64                             docker-scan-plugin-0.17.0-3.el8.x86_64          fuse-common-3.3.0-15.0.2.el8.x86_64          fuse-overlayfs-1.9-1.module+el8.6.0+20721+d8d917a9.x86_64
  fuse3-3.3.0-15.0.2.el8.x86_64                                               fuse3-libs-3.3.0-15.0.2.el8.x86_64              libcgroup-0.41-19.el8.x86_64                 libslirp-4.4.0-1.module+el8.6.0+20721+d8d917a9.x86_64
  slirp4netns-1.2.0-2.module+el8.6.0+20721+d8d917a9.x86_64

Complete!

Enable and Start the docker Service

[root@certbot conf.d]# systemctl enable docker
Created symlink /etc/systemd/system/multi-user.target.wants/docker.service → /usr/lib/systemd/system/docker.service.
[root@certbot conf.d]# systemctl start docker

Command “Docker PS” shows the running dockers.

[root@certbot conf.d]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

Lets Install Docker Compose as follows

[root@certbot conf.d]# curl -L "https://github.com/docker/compose/releases/download/1.23.2/docker-compose-$(uname -s)-$(uname -m)" -o docker-compose
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
100 11.2M  100 11.2M    0     0  44.9M      0 --:--:-- --:--:-- --:--:-- 44.9M
[root@certbot conf.d]# mv docker-compose /usr/local/bin && sudo chmod +x /usr/local/bin/docker-compose
[root@certbot conf.d]# docker-compose version
docker-compose version 1.23.2, build 1110ad01
docker-py version: 3.6.0
CPython version: 3.6.7
OpenSSL version: OpenSSL 1.1.0f  25 May 2017
[root@certbot conf.d]#

Add user to docker group (if not already added)
sudo usermod -aG docker $USER
2. create a symbolic link to /usr/bin using the following command
$ sudo ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose
3. Restart docker service
$ sudo service docker restart

Lets see the Available Commands with Docker help, We can discuss more on these commands in the Next Part

See 'docker --help'.

Usage:  docker [OPTIONS] COMMAND

A self-sufficient runtime for containers

Options:
      --config string      Location of client config files (default "/root/.docker")
  -c, --context string     Name of the context to use to connect to the daemon (overrides DOCKER_HOST env var and default context set with "docker context use")
  -D, --debug              Enable debug mode
  -H, --host list          Daemon socket(s) to connect to
  -l, --log-level string   Set the logging level ("debug"|"info"|"warn"|"error"|"fatal") (default "info")
      --tls                Use TLS; implied by --tlsverify
      --tlscacert string   Trust certs signed only by this CA (default "/root/.docker/ca.pem")
      --tlscert string     Path to TLS certificate file (default "/root/.docker/cert.pem")
      --tlskey string      Path to TLS key file (default "/root/.docker/key.pem")
      --tlsverify          Use TLS and verify the remote
  -v, --version            Print version information and quit

Management Commands:
  app*        Docker App (Docker Inc., v0.9.1-beta3)
  builder     Manage builds
  buildx*     Docker Buildx (Docker Inc., v0.7.1-docker)
  config      Manage Docker configs
  container   Manage containers
  context     Manage contexts
  image       Manage images
  manifest    Manage Docker image manifests and manifest lists
  network     Manage networks
  node        Manage Swarm nodes
  plugin      Manage plugins
  scan*       Docker Scan (Docker Inc., v0.12.0)
  secret      Manage Docker secrets
  service     Manage services
  stack       Manage Docker stacks
  swarm       Manage Swarm
  system      Manage Docker
  trust       Manage trust on Docker images
  volume      Manage volumes

Commands:
  attach      Attach local standard input, output, and error streams to a running container
  build       Build an image from a Dockerfile
  commit      Create a new image from a container's changes
  cp          Copy files/folders between a container and the local filesystem
  create      Create a new container
  diff        Inspect changes to files or directories on a container's filesystem
  events      Get real time events from the server
  exec        Run a command in a running container
  export      Export a container's filesystem as a tar archive
  history     Show the history of an image
  images      List images
  import      Import the contents from a tarball to create a filesystem image
  info        Display system-wide information
  inspect     Return low-level information on Docker objects
  kill        Kill one or more running containers
  load        Load an image from a tar archive or STDIN
  login       Log in to a Docker registry
  logout      Log out from a Docker registry
  logs        Fetch the logs of a container
  pause       Pause all processes within one or more containers
  port        List port mappings or a specific mapping for the container
  ps          List containers
  pull        Pull an image or a repository from a registry
  push        Push an image or a repository to a registry
  rename      Rename a container
  restart     Restart one or more containers
  rm          Remove one or more containers
  rmi         Remove one or more images
  run         Run a command in a new container
  save        Save one or more images to a tar archive (streamed to STDOUT by default)
  search      Search the Docker Hub for images
  start       Start one or more stopped containers
  stats       Display a live stream of container(s) resource usage statistics
  stop        Stop one or more running containers
  tag         Create a tag TARGET_IMAGE that refers to SOURCE_IMAGE
  top         Display the running processes of a container
  unpause     Unpause all processes within one or more containers
  update      Update configuration of one or more containers
  version     Show the Docker version information
  wait        Block until one or more containers stop, then print their exit codes

Run 'docker COMMAND --help' for more information on a command.

To get more help with docker, check out our guides at https://docs.docker.com/go/guides/

Posted in DevOps, Docker, Docker CE, Docker Compose, Linux | Tagged: DevOps, Docker, Docker-CE, Docker-Compose | Leave a Comment »

Install & Configure SSL for Apache-Nginx using Lets Encrypt-CertBot.

Posted by Sriram Sanka on September 28, 2022

One can Install Apache and Nginx using YUM Or DNF in the Selected Unix flavor whereas by default it is a non-secure sub-domain when you access.

Lets encrypt offers free SSL which can be configured to get the SSL for your domain irrespective of Private Or Public Domains. In case of Private Domains , you just need to add a text Entry to Pass the Validations.

For this I am using Oracle Cloud Instance(Always Free).

Change the Host Name using hostnamectl as below

[root@certbot ~]# hostnamectl set-hostname certbot.ramoradba.com
[root@certbot ~]# hostname

As its the Initial Login after Instance provision , run the yum update and Install Apache and/or Nginx as per your choice.

Run Yum Update and make sure everything updated without any issues.

Install Apache-httpd using yum repo.

Try Access the IP/Hostname to see the Installed Apache Default Page.

Install Nginx using Yum

Try to Access Nginx from the Browser

Enable EPEL Repo to Configure Snap and Certbot

Enable the Socket and run the below to Install certbot ,

systemctl enable --now snapd.socket

ln -s /var/lib/snapd/snap /snap

snap install certbot --classic

Restart your session to get the Certbot ,You can Configure SSL for Nginx Or Apache as below, Add an Entry in you domain controller for your IP matching with the Host Name Configured.

You can Either Configure SSL and Install Or Choose the certonly Option to Get the Certificates only, you can configure your SSL.conf as per your webserver configuration
Also In case , your System is not internet facing, You can choose the Preferred Challenges as either http or DNS You can review the Supported Challenged here https://letsencrypt.org/docs/challenge-types/

certbot --nginx -d <subdomain>.ramoradba.com
certbot --apache -d <subdomain>.ramoradba.com

Make Sure you have a Virtual_Host entry available with the domain you chose., Otherwise it will fail.

<VirtualHost *:80>
    ServerName certbot.ramoradba.com
    ServerAdmin webmaster@localhost
    DocumentRoot /var/www/html

    ErrorLog ${APACHE_LOG_DIR}/error.log
    CustomLog ${APACHE_LOG_DIR}/access.log combined
</VirtualHost>

certbot --apache -d certbot.ramoradba.com

Reload your browser session to see the Installed Certificate .

Thanks for your visit, Hope you like it.

Posted in Apache, Installation, Linux, Nginx, Security, ssl | Tagged: Apache, certbot, letsencrypt, Linux, Nginx, OCI, ssl | Leave a Comment »

Python Basics – Part 1

Posted by Sriram Sanka on September 17, 2022

Language Introduction

Python is a dynamic, interpreted (bytecode-compiled) language. There are no type declarations of variables, parameters, functions, or methods in source code. This makes the code short and flexible, and you lose the compile-time type checking of the source code. Python tracks the types of all values at runtime and flags code that does not make sense as it runs.

https://www.edureka.co/blog/introduction-to-python/

In the Below Sections I have attached couple of reference Documents and Practice Notes for your reference. To obtain the contents, Rename the file Extension from txt to “ipynb” , which can be accessed using Jupyter Or Anaconda etc.

installingpython.1 Download

String Split

Description

Split the string input_str = ‘Kumar_Ravi_003’ to the person’s second name, first name and unique customer code. In this example, second_name= ‘Kumar’, first_name= ‘Ravi’, customer_code = ‘003’.

input_str = input('data')
first_name = input_str[6:10]
second_name = input_str[0:5]
customer_code = input_str[-3:]
print(first_name)
print(second_name)
print(customer_code)

string -lstrip()

input_str = input('Enter Input : ')
final_str = input_str.lstrip()
print(final_str)

introduction-datatypes Download

List is a collection which is ordered and changeable. Allows duplicate members.

Tuple is a collection which is ordered and unchangeable. Allows duplicate members.

Set is a collection which is unordered, unchangeable*, and unindexed. No duplicate members.

Dictionary is a collection which is ordered** and changeable. No duplicate members.

List to String

Description

Convert a list [‘Pythons syntax is easy to learn’, ‘Pythons syntax is very clear’] to a string using ‘&’. The sample output of this string will be:

Pythons syntax is easy to learn & Pythons syntax is very clear

Note that there is a space on both sides of ‘&’ (as usual in English sentences).

l =[]
l.append('Pythons syntax is easy to learn')
l.append(' Pythons syntax is very clear')
print('This is the List ',l)
input_str = l
string_1 = " & ".join(input_str)
print('This is Combined String ',string_1)

listcomprehensionsinpython Download

lists Download

tuples Download

dictionaries Download

sets Download

reference-workbook-2-data-structures Download

conditional-ifelse Download

looping-for-while Download

comprehensions Download

functions Download

map-reduce-filter Download

References

https://python-course.eu/advanced-python/lambda-filter-reduce-map.php

https://book.pythontips.com/en/latest/map_filter.html

https://python.swaroopch.com/functions.html

https://anh.cs.luc.edu/python/hands-on/3.1/handsonHtml/functions.html

https://treyhunner.com/2015/12/python-list-comprehensions-now-in-color/

https://python-3-patterns-idioms-test.readthedocs.io/en/latest/Comprehensions.html

https://docs.python.org/3/tutorial/controlflow.html

https://docs.python.org/3/reference/compound_stmts.html

https://docs.python.org/3/tutorial/datastructures.html

https://jupyter-notebook-beginner-guide.readthedocs.io/en/latest/

https://jupyter-notebook-beginner-guide.readthedocs.io/en/latest/what_is_jupyter.html

https://python.swaroopch.com/

https://docs.python-guide.org/intro/learning/

https://www.simplilearn.com/tutorials/python-tutorial

https://developers.google.com/edu/python/lists

https://developers.google.com/edu/python/introduction

Posted in Anaconda, Python | Tagged: Anaconda, Basics, Python | Leave a Comment »

SQL Formatter – Using Java/PL/SQL by Sayan Malakshinov

Posted by Sriram Sanka on September 17, 2022

It is very easy to Format SQL using Third Party tools, Recently I found one source to get the SQL Formatted from the Sql prompt without connecting to any Third Party GUI Tools.

I prefer to use SQLPLUS more than GUI tools., You can see the code formatted beautifully and very readable.

Step 1: Load oracle.dbtools-common.jar into the Database.

Microsoft Windows [Version 10.0.19044.2006]
(c) Microsoft Corporation. All rights reserved.

C:\Users\Dell>loadjava -u sriram/zaqmlp123 C:\app\Dell\product\21c\dbhomeXE\jlib\oracle.dbtools-common.jar

Step 2: Grant required java Privileges to the User and Create Java and Write a wrapper Package using the java

C:\Users\Dell>sqlplus /nolog

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Sep 17 16:52:53 2022
Version 21.3.0.0.0

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

SQL> conn sriram@xepdb1
Enter password:
Connected.
SQL>
SQL> exec dbms_java.grant_permission( 'SRIRAM', 'SYS:java.lang.RuntimePermission', 'oracle.DbmsJavaScriptUser', '' );

PL/SQL procedure successfully completed.

SQL> exec dbms_java.grant_permission( 'SRIRAM', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.jdk.nashorn.internal.runtime', '' );

PL/SQL procedure successfully completed.

SQL> exec dbms_java.grant_permission( 'SRIRAM', 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );

PL/SQL procedure successfully completed.

SQL> select
  2
SQL> ed
Wrote file afiedt.buf

  1  CREATE or replace AND COMPILE JAVA SOURCE NAMED SQLFormatter AS
  2  import oracle.dbtools.app.Format;
  3  import java.sql.Connection;
  4  import java.sql.DriverManager;
  5  import java.sql.SQLException;
  6  import oracle.sql.BLOB;
  7  import oracle.sql.CLOB;
  8  import java.io.StringWriter;
  9  import java.io.PrintWriter;
 10  public class SQLFormatter {
 11      private static String getStackTrace(Exception e) {
 12         StringWriter writer = new StringWriter();
 13         PrintWriter printWriter = new PrintWriter( writer );
 14         e.printStackTrace( printWriter );
 15         printWriter.flush();
 16         return writer.toString();
 17      }
 18      public static Format getFormat() {
 19          oracle.dbtools.app.Format format = new oracle.dbtools.app.Format();
 20          format.options.put("singleLineComments", Format.InlineComments.CommentsUnchanged);
 21          format.options.put("kwCase", Format.Case.UPPER);
 22          format.options.put("idCase", Format.Case.NoCaseChange);
 23          format.options.put("adjustCaseOnly", false);
 24          format.options.put("formatThreshold", 1);
 25          format.options.put("alignTabColAliases", false);
 26          format.options.put("alignTypeDecl", true);
 27          format.options.put("alignNamedArgs", true);
 28          format.options.put("alignEquality", false);
 29          format.options.put("alignAssignments", true);
 30          format.options.put("alignRight", false);
 31          format.options.put("identSpaces", 3);
 32          format.options.put("useTab", false);
 33          format.options.put("breaksComma", Format.Breaks.Before);
 34          format.options.put("breaksProcArgs", false);
 35          format.options.put("breaksConcat", Format.Breaks.Before);
 36          format.options.put("breaksAroundLogicalConjunctions", Format.Breaks.Before);
 37          format.options.put("breaksAfterSelect", true);
 38          format.options.put("commasPerLine", 1);
 39          format.options.put("breakOnSubqueries", true);
 40          format.options.put("breakAnsiiJoin", true);
 41          format.options.put("breakParenCondition", true);
 42          format.options.put("maxCharLineSize", 120);
 43          format.options.put("forceLinebreaksBeforeComment", false);
 44          format.options.put("extraLinesAfterSignificantStatements", Format.BreaksX2.Keep);
 45          //format.options.put("flowControl", Format.FlowControl.IndentedActions);
 46          format.options.put("spaceAroundOperators", true);
 47          format.options.put("spaceAfterCommas", true);
 48          //format.options.put("spaceAroundBrackets", Format.Space.Default);
 49          return format;
 50      }
 51    public static String format(String str)
 52    {
 53      String res;
 54      try {
 55         Format f = SQLFormatter.getFormat();
 56         res = f.format(str);
 57         }
 58      catch (Exception e){
 59         res = "Error: " + e.getMessage() + " [ " + SQLFormatter.getStackTrace(e) + " ]";
 60      }
 61      return res;
 62    }
 63    public static CLOB formatClob(oracle.sql.CLOB clob)
 64    throws SQLException
 65    {
 66      String str = clob.getSubString(1, (int) clob.length());
 67      String res = SQLFormatter.format(str);
 68      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 69      CLOB resClob = CLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
 70      resClob.setString(1L, res);
 71      return resClob;
 72    }
 73* }
 74  /

Java created.

SQL> select
  2
SQL> ed
Wrote file afiedt.buf

  1  create or replace package SQLFormatter as
  2    FUNCTION Format(str in varchar2) RETURN VARCHAR2
  3    AS LANGUAGE JAVA NAME 'SQLFormatter.format(java.lang.String) return java.lang.String';
  4    FUNCTION FormatClob(str in clob) RETURN CLOB
  5    AS LANGUAGE JAVA NAME 'SQLFormatter.formatClob(oracle.sql.CLOB) return oracle.sql.CLOB';
  6* end;
  7  /

Package created.

SQL> desc SQLFormatter
FUNCTION FORMAT RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STR                            VARCHAR2                IN
FUNCTION FORMATCLOB RETURNS CLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STR                            CLOB                    IN

SQL> select  sql_id||' - '||sql_text as "UnClear" from v$sqlarea where sql_id='gngtvs38t0060'
  2  union
  3  select sql_id||' - '||SQLFormatter.format(sql_text) "Readable" from v$sqlarea where sql_id='gngtvs38t0060'
  4  /

UnClear
------------------------------------------------------------------------------------------------------------------------
gngtvs38t0060 - SELECT /*+ CONNECT_BY_FILTERING */ s.privilege# FROM sys.sysauth$ s        CONNECT BY s.grantee# = PRIOR
 s.privilege#                                 AND (s.privilege# > 0 OR s.privilege# = -352)
 START WITH (s.privilege# > 0 OR s.privilege# = -352) AND s.grantee# IN     (SELECT c1.privilege# FROM sys.codeauth$ c1
WHERE c1.obj# = :1)            UNION                                                                      SELECT c2.priv
ilege# FROM sys.codeauth$ c2 WHERE c2.obj# = :2              ORDER BY 1 ASC









gngtvs38t0060 - SELECT /*+ CONNECT_BY_FILTERING */
   s.privilege#
FROM
   sys.sysauth$ s
CONNECT BY
   s.grantee# = PRIOR s.privilege#
AND (
         s.privilege# > 0
      OR s.privilege# =-352
   )
START WITH
   (
         s.privilege# > 0
      OR s.privilege# =-352
   )
AND s.grantee# IN (
      SELECT
         c1.privilege#
      FROM
         sys.codeauth$ c1
      WHERE
         c1.obj# =:1
   )
UNION
SELECT
   c2.privilege#
FROM
   sys.codeauth$ c2
WHERE
   c2.obj# =:2
ORDER BY 1 ASC


2 rows selected.

Refer http://orasql.org/2020/12/23/format-sql-or-pl-sql-directly-in-oracle-database/ for more info.

https://github.com/xtender/xt_scripts/tree/master/extra/SQLFormatter

Hope this is useful for you as well 🙂

Posted in formatter, sql | Tagged: formatter, java, oracle, plsql, sql | Leave a Comment »