HOME BLOG

How to create table literals in Oracle

Posted on: October 9th, 2020 by Olu No Comments

Hi folks,

In this post I talk about how to create literal tables in Oracle.

You may have a list of values and want to run an Oracle query against those values.

A convenient way to do this is to create a literal table in an Oracle SQL using those values.

 

Single-column

If you want to create a single-column literal table,

you can use either dbms_debug_vc2coll or sys.ODCIVarchar2List

e.g.

select * from table (sys.ODCIVarchar2List('AAA', 'BBB', 'CCC'));

This will return an inline table with a single column named COLUMN_VALUE and rows with values AAA, BBB and CCC.

i.e.

COLUMN_VALUE
==
AAA
BBB
CCC

Second example

select column_value from table(sys.dbms_debug_vc2coll(‘AAA’, ‘BBB’, ‘CCC’))

 

Multiple colums

If you want to generate a literal table with two columns, you can use a query like:

select objectschema m, objectname n
from
table(sys.ODCIObjectList(
sys.odciobject('APPLE', 'FRUIT'),
sys.odciobject('CARROT', 'VEGGIE')
));

For more tahan 2 columns, you can use a query like:

create type t as object (a varchar2(10), b varchar2(10), c number);
create type tt as table of t;

select * from table( tt (
    t('APPLE', 'FRUIT', 1),
    t('APPLE', 'FRUIT', 1122), 
    t('CARROT', 'VEGGIE', 3),
    t('PEACH', 'FRUIT', 104),
    t('CUCUMBER', 'VEGGIE', 5),
    t('ORANGE', 'FRUIT', 6) ) )

That’s all for now. Happy database querying.

 

Sources

Oracle SQL Tip: Using dbms_debug_vc2coll and sys.ODCIVarchar2List. https://training.fusionapplied.com/2017/08/23/oracle-sql-tip-using-dbms_debug_vc2coll-and-sys-odcivarchar2list/

Is there a non-ugly way to use a multi-column, multi-row table literal in an Oracle 11g query? https://stackoverflow.com/questions/29903397/is-there-a-non-ugly-way-to-use-a-multi-column-multi-row-table-literal-in-an-ora/29904128

How to request sensitive details in Python without displaying the input on the screen

Posted on: September 26th, 2020 by Olu No Comments

Hi folks,

Here’s a short code snippet that demonstrates how to request sensitive information from a Python application without displaying the details on screen as the user enters it. The tool to use is a library called getpass as well as the subprocess library. Here it is.

 

import subprocess

info_process = subprocess.run([
'python', '-c', 
"print(import('getpass').getpass('Enter the details here'))"
], stdout=subprocess.PIPE)

details = info_process.stdout.decode('utf-8').strip()

That’s all for now. Happy coding

How to fix issue of “’” showing on page instead of “ ‘ ” in gVim in Windows

Posted on: September 26th, 2020 by Olu No Comments

Hi folks,

Recently I experienced an issue where when I opened a file using my favourite editor gVim in Windows and found to my utter bewilderment that the “’” character was showing instead of “ ‘ ”. I’ll quickly talk about how to fix such problem.

First, what’s the cause? The cause is that the editor isn’t using UTF-8 encoding. It might be using ISO-8859-1/Windows-1252. So, to fix it, update gVim to use UTF-8. Here’s how to do that.

Open your _vimrc file and add the following lines

 

set encoding=utf-8
set fileencoding=utf-8

Save and close the file. That’s it. Your apostrophe characters should now display correctly. Happy editing.

Sources:

“’” showing on page instead of “ ‘ ”. stackoverflow. https://stackoverflow.com/questions/2477452/%C3%A2%E2%82%AC-showing-on-page-instead-of

Set encoding and fileencoding to utf-8 in Vim. stackoverflow. https://stackoverflow.com/questions/16507777/set-encoding-and-fileencoding-to-utf-8-in-vim

SSH Login without password from Windows to Linux

Posted on: September 20th, 2020 by Olu No Comments

Your aim

You want to use Linux and OpenSSH to automate your tasks. Therefore you need an automatic login from Windows host A / user a to Linux Host B / user b. You don’t want to enter any passwords, because you want to call ssh from a within a shell script.

How to do it

First log in on A as user a and generate a pair of authentication keys. Do this using Git Bash. Do not enter a passphrase:

a@A:~> ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/a/.ssh/id_rsa): 
Created directory '/home/a/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /home/a/.ssh/id_rsa.
Your public key has been saved in /home/a/.ssh/id_rsa.pub.
The key fingerprint is:
3e:4f:05:79:3a:9f:96:7c:3b:ad:e9:58:37:bc:37:e4 a@A

Now use ssh to create a directory ~/.ssh as user b on B. (The directory may already exist, which is fine):

a@A:~> ssh b@B mkdir -p .ssh
b@B's password: 

Finally append a’s new public key to b@B:.ssh/authorized_keys and enter b’s password one last time:

a@A:~> cat .ssh/id_rsa.pub | ssh b@B 'cat >> .ssh/authorized_keys'
b@B's password: 

From now on you can log into B as b from A as a without password:

a@A:~> ssh b@B

A note from one of our readers: Depending on your version of SSH you might also have to do the following changes:

  • Put the public key in .ssh/authorized_keys2
  • Change the permissions of .ssh to 700
  • Change the permissions of .ssh/authorized_keys2 to 640

 

Source:

 

SSH login without password. http://linuxproblem.org/art_9.html

How to add rsync to Git Bash in Windows 10

Posted on: September 20th, 2020 by Olu No Comments

Download the package: http://www2.futureware.at/~nickoe/msys2-mirror/msys/x86_64/rsync-3.1.2-2-x86_64.pkg.tar.xz

 

Launch Git Bash and change to the download location.

 

Extract the archive using coommand:

 

tar -xf rsync-3.1.2-2-x86_64.pkg.tar.xz

 

You will get a folder usr.

 

Move usr/bin/rsync.exe to C:\Program Files\Git\usr\bin

 

Source:

How to Add rsync to Git Bash for Windows 10. https://gist.github.com/hisplan/ee54e48f17b92c6609ac16f83073dde6

 

How to do mocking in setUp in Python

Posted on: August 29th, 2020 by Olu No Comments

Hi folks,

In this post I will talk about how to mock functions in Python across all methods of your test class.

To do this, all you have to do is perform your mocking in the setUp method of your test class.

A way to do this is to create a patch object using code like:

mock_item_patch = mock.patch('some.module.path.item')

 

Next, call start() on the patch object to create your mock using code like

mock_item = mock_item_patch.start()

 

Once you do this, you can set return_value or side_effect as needed. E.g.

mock_item.foo.return_value = 'bar'

 

Don’t forget to add a call to stop the patch on clean-up using code

self.addCleanup(mock_item_patch.stop)

 

Below is code showing all these in action.

 

from unittest import mock, TestCase


class YourTestClass(TestCase):
    def setUp(self):
        mock_item_patch = mock.patch('some.module.path.item')
        mock_item = mock_item_patch.start()
        mock_item.foo.return_value = 'bar'
        self.addCleanup(mock_item_patch.stop)


    def test_something(self):
        ...

 

That’s all for now. Happy software development.

How to dump stored MySQL routines

Posted on: June 12th, 2020 by Olu No Comments

Hi folks,

In this post I will quickly share how to make a MySQL dump of stored routines (procedures and functions). Recently, I was looking to back up databases for a MySQL server which I maintain. One of the databases contained a stored function. So, I thought, heh, let’s just use the good old mysqldump the way I do it with tables. Well, it didn’t work. After a bit of Googling and searching MySQL documentation, I eventually found the answer. All you have to do is add the routines flag when invoking mysqldump.

E.g.

 

mysqldump --routines -u youruser -p yourdb > yourdb.sql

 

That’s all for now.

How to perform acions when a Python program finishes whether successfully or not

Posted on: April 25th, 2020 by Olu No Comments

Hi folks,

In this post I talk about a good way to perform actions when your Python program completes. For example you may want to clean up temporary files created by program or close database connections or other resources resources when your program terminates. You may want to do this regardless of whether the program finishes successfully or crashes due to an error. An excellent way to perform such clean-up tasks automatically is to use the atexit module. Here is an example below of how to use it in a class below.

 

import atexit


class Foo:

    def __init__(self):
        atexit.register(self.goodbye)
        x = 5

    def goodbye(self):
        print('You are leaving the program')



f = Foo()

 

If you run the following code, you will see the message ‘You are leaving the program’ just before the program exits.

Here is another example where we intentionally cause the program to crash.

import atexit


class Bar:

    def __init__(self):
        atexit.register(self.goodbye)
        x = 5 / 0 # error!

    def goodbye(self):
        print('You are leaving the program')



f = Bar()

 

If you run this program, you will see the error, but nevertheless still see the message ‘You are leaving the program’ printed before the program exits.

You can read more about atexit in the Python documentation.

That’s it for now. Happy software development.

How to run system commands in Python and get the output

Posted on: April 20th, 2020 by Olu No Comments

Hi folks,

Here is an easy way to run system commands in Python and get the output. It is by using subprocess.getoutput command. E.g.

import subprocess
output = subprocess.getoutput("ls -l ~")
print(output)

 

That’s all for now. Till next time, happy software development.

How to fix ‘Connection Refused’ error when connecting to Apache virtual host from other client ip addresses

Posted on: April 17th, 2020 by Olu No Comments

Hi folks,

In this post I talk about how to fix ‘Connection Refused’ error you may experience when trying to connect to your web application which has been set up as a virtual host on Apache server. The issue I discuss here is where you are able to access the virtual host from your local machine where the app is hosted, but not from any other machine.

It turns out that the solution to this problem lies in the server name used for the virtual host.

If you use a server name that ends in ‘.localhost’, other clients will not be able to connect to your application. So, if you have such a server name, rename it to something that does not end in ‘.localhost’.

That’s all for now. Till next time, happy software development.