TechNotes Central: 2020

Thursday, December 17, 2020

N: Skipping acquire of configured file 'main/binary-arm64/Packages' - Ubuntu 20.10

I share solution next error.

Edit file from path:

sudo nano /etc/apt/sources.list.d/vscode.list

Comment or change next line on file.

Original line comment or update:

deb [arch=amd64,arm64,armhf] http://packages.microsoft.com/repos/vscode stable main

To:

deb [arch=amd64] http://packages.microsoft.com/repos/vscode stable main

That it is.

References: https://stackoverflow.com/questions/65306968/vs-code-n-skipping-acquire-of-configured-file-main-binary-arm64-packages/65310278#65310278

Tuesday, November 10, 2020

Query Working days monday to friday and saturday 1/2 + Oracle

I share query to get working days monday to friday and saturday 1/2, for example if you hire an employee start to work on (mm/dd/yyyy) 10/15/2020 then you to pay until 10/31/2020, the working day to pay is 13.5, because to omit sunday and sum 1/2 for saturday.

Query:

SELECT

(SELECT (( TRUNC( to_date('10/31/2020', 'mm/dd/yyyy'), 'IW' ) - TRUNC( to_date('10/15/2020', 'mm/dd/yyyy'), 'IW' ) ) * 5 / 7

+ LEAST( to_date('10/31/2020', 'mm/dd/yyyy') - TRUNC( to_date('10/31/2020', 'mm/dd/yyyy'), 'IW' ) + 1, 5 )

- LEAST( to_date('10/15/2020', 'mm/dd/yyyy') - TRUNC( to_date('10/15/2020', 'mm/dd/yyyy'), 'IW' ) + 1, 5 )) + 1

AS WeekDaysDifference

FROM dual)

WITH t

AS (SELECT to_date('10/15/2020', 'mm/dd/yyyy') start_date,

to_date('10/31/2020', 'mm/dd/yyyy') end_date

FROM dual)

SELECT (Count(*)/2) numSaturday

FROM (SELECT To_char(start_date + ( LEVEL - 1 ), 'fmday') dt

FROM t

CONNECT BY LEVEL <= end_date - start_date + 1)

WHERE dt IN ( 'saturday' )) workingDays FROM DUAL;

Test:

References:

https://stackoverflow.com/questions/25400025/count-the-no-of-saturdays-and-sundays-in-date-range-oracle

https://stackoverflow.com/questions/12932965/sql-to-return-the-number-of-working-days-between-2-passed-in-dates

https://stackoverflow.com/questions/43632677/function-to-get-number-of-weekdays-between-two-dates-excluding-holidays/43633234#43633234

https://stackoverflow.com/questions/44203389/oracle-days-between-two-date-and-exclude-weekdays-how-to-handle-negative-number

Saturday, October 31, 2020

RESTful Web Services from Oracle, HTTP Methods GET and POST

I share example how to consuming RESTful Web Services from Oracle, methods GET and POST with PL/SQL Anonymous Block, for this example i use http://jsonplaceholder.typicode.com/ json.

1. GET

DECLARE
    req     utl_http.req;
    res     utl_http.resp;
    urlrest VARCHAR2(100) := 'http://jsonplaceholder.typicode.com/posts/1';
    name    VARCHAR2(4000);
    buffer  VARCHAR2(4000);
BEGIN
    req := utl_http.Begin_request(urlrest, 'GET', ' HTTP/1.1');
   utl_http.Set_header(req, 'user-agent', 'mozilla/4.0');
   utl_http.Set_header(req, 'content-type', 'application/json');
   res := utl_http.Get_response(req);

    LOOP
        utl_http.Read_line(res, buffer);
        dbms_output.Put_line(buffer);
    END LOOP;

    utl_http.End_response(res);
EXCEPTION
    WHEN utl_http.end_of_body THEN
      utl_http.End_response(res);
END;

Test:

2. POST

DECLARE
    req     utl_http.req;
    res     utl_http.resp;
    urlrest VARCHAR2(4000) := 'http://jsonplaceholder.typicode.com/posts';
    name    VARCHAR2(4000);
    buffer  VARCHAR2(4000);
    json    VARCHAR2(4000) := '{"title":"foo","body":"bar","userId":1}';
BEGIN
    req := utl_http.Begin_request(urlrest, 'POST', ' HTTP/1.1');
    utl_http.Set_header(req, 'user-agent', 'mozilla/4.0');
    utl_http.Set_header(req, 'content-type', 'application/json');
    utl_http.Set_header(req, 'Content-Length', Length(json));
    utl_http.Write_text(req, json);
    res := utl_http.Get_response(req);

    BEGIN
        LOOP
            utl_http.Read_line(res, buffer);

            dbms_output.Put_line(buffer);
        END LOOP;

        utl_http.End_response(res);
    EXCEPTION
        WHEN utl_http.end_of_body THEN
          utl_http.End_response(res);
    END;
END;

Test:

Saturday, October 17, 2020

Kotlin - Multi-Threading in parallel and wait until all threads finish.

I share example how to execute Multi-Threading in parallel on Kotlin, is very useful if you want send a lot process simultaneously, In java you can use parallel stream and exist a lot examples, but it's almost the same.

I use an ExecutorService to manage pools of threads.

import java.util.concurrent.Executors
import java.util.concurrent.TimeUnit

fun main(args: Array<String>) {
    println("Started.....!")

    var listStr = arrayOf("test1", "test2", "test3",
            "test4", "test5", "test6", "test7", "test8", "test9", "test10")

    var es = Executors.newCachedThreadPool()

    for (item in listStr) {
        es.execute {
            println("item -> $item")
        }
    }

    es.shutdown()
    var finished = es.awaitTermination(1, TimeUnit.MINUTES)
    println("\\nFinished all threads $finished")

}

Run test: check test 3 execute after test 4 and test 2 execute after test 3.

References:

https://winterbe.com/posts/2015/04/07/java8-concurrency-tutorial-thread-executor-examples/

https://howtodoinjava.com/java/multi-threading/executorservice-invokeall/

https://crunchify.com/how-to-run-multiple-threads-concurrently-in-java-executorservice-approach/

https://stackoverflow.com/questions/7939257/wait-until-all-threads-finish-their-work-in-java

Kotlin - Spring Data JPA calling Oracle Function

There are two way to call function in Spring Data JPA with kotlin, I shares examples.

Create function in oracle.

CREATE OR replace FUNCTION Myfunction(value IN VARCHAR2)
RETURN NUMBER
IS
  a NUMBER;
  b NUMBER;
BEGIN
    RETURN a + b;
END;

Example 1, it won't work if your function is using DML statementes, but you can add annotation @Modifying annotation.

import org.springframework.data.jpa.repository.Query
import org.springframework.data.repository.CrudRepository
import org.springframework.data.repository.query.Param

interface MyRepository: CrudRepository<YOUR_MODEL, YOUR_ID_MODEL>{
    @Query(nativeQuery = true, value = "SELECT myfunction(:value) FROM dual")
    fun myfunction(@Param("value") value: String?): Int?
}

Example 2: Create customer repository.

2.1 create custom repository

interface MyRepositoryCustom {

    fun myFunction(param1: String?): Int?
}

2.2 create a class, i use SimpleJdbcCall.

import org.springframework.beans.factory.annotation.Autowired
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource
import org.springframework.jdbc.core.namedparam.SqlParameterSource
import org.springframework.jdbc.core.simple.SimpleJdbcCall


class MyRespositoryImpl : MyRepositoryCustom {

    @Autowired
    lateinit var jdbcTemplate: JdbcTemplate

    override fun myFunction(param1: String?): Int? {
        val jdbcCall = SimpleJdbcCall(jdbcTemplate).withFunctionName("myfunction")

        val paramMap: SqlParameterSource = MapSqlParameterSource()
                .addValue("value", param1)

        return jdbcCall.executeFunction(Int::class.java, paramMap)
    }
}

2.3 I extend customer repository on principal repository.

interface MyRepository: CrudRepository<YOUR_MODEL, YOUR_ID_MODEL>, MyRepositoryCustom {

That it is.

References:

https://stackoverflow.com/questions/45867348/spring-data-jpa-calling-oracle-function

Monday, September 14, 2020

Connect Oracle Database From Shell Script

There are two ways to connect Oracle from shell script.

1. You need to install SQL Plus Client on your server

2. You'll have install ORACLE on your server.

I share example for both.

Create file Oracle-ShellScript.sh

sudo nano Oracle-ShellScript.sh

Example with $ORACLE_HOME, add next lines into file. Check your path ORACLE_HOME

#!/bin/sh

#parameters DB
DATABASE_USER=$1
DATABASE_PASS=$2
IP=$3
SID=$4


DATE=`date +"%d%m%y"`
u="$USER"
echo "User name $u"

cd /home/$u

LOCATION=$PWD
echo "path ${LOCATION}"


ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_HOME
echo $ORACLE_HOME

dbConexion() {   
$ORACLE_HOME/bin/sqlplus -s <<EOF> ${LOCATION}/log${DATE}.out
$DATABASE_USER/$DATABASE_PASS@//$IP:1521/$SID
select sysdate from dual;

EOF

}

dbValidate() {
p=`cat ${LOCATION}/log${DATE}.out | grep SYSDATE`
if [ -n "$p"  ]
then
        echo "Ok conexion"
        rm ${LOCATION}/log${DATE}.out
else
        echo "Bad conexion."
        exit
fi
}

echo "Start process" 
dbConexion
echo "Check conexion on DB"
dbValidate
echo "End process" 

Example with SQL Plus client add next lines into file:

#!/bin/sh

#parameters DB
DATABASE_USER=$1
DATABASE_PASS=$2
IP=$3
SID=$4


DATE=`date +"%d%m%y"`
u="$USER"
echo "User name $u"

cd /home/$u

LOCATION=$PWD
echo "path ${LOCATION}"

dbConexion() {
sqlplus64 -s <<EOF> ${LOCATION}/log${DATE}.out
$DATABASE_USER/$DATABASE_PASS@//$IP:1521/$SID
select sysdate from dual;

EOF

}

dbValidate() {
p=`cat ${LOCATION}/log${DATE}.out | grep SYSDATE`
if [ -n "$p"  ]
then
        echo "Ok conexion"
        rm ${LOCATION}/log${DATE}.out
else
        echo "Bad conexion."
        exit
fi
}

echo "Start process" 
dbConexion
echo "Check conexion on DB"
dbValidate
echo "End process" 

Test: Send the next argument to shell script user_db, password_db, ip and sid

sh Oracle-ShellScript.sh YOUR_USER_DB YOUR_PASSWORD_DB YOUR_IP YOUR_SID

Code on GitHub: https://github.com/HenryXiloj/Connect-to-oracle-shell-script

Friday, September 11, 2020

How to Install Apache2 + PHP 7.2 + Joomla on Ubuntu 20.04

In this post, you will learn how to install Apache2 + PHP 7.2 + Joomla 3.9.21, for write this post i get two references, you checks links in the end post. let's start.

1. Update and upgrade system packages

 sudo apt-get update && sudo apt-get upgrade

2. Install apache

 sudo apt install apache2

The comands below, you need to stop, start, status, reload and enable apache2 service.

 sudo systemctl start apache2.service

sudo systemctl enable apache2.service

3. Install PHP 7.2 is not be avaible in ubuntu default repositories, for that run next commands.

 sudo apt-get install software-properties-common

sudo add-apt-repository ppa:ondrej/php

 sudo apt-get update && sudo apt-get upgrade

  sudo apt install libapache2-mod-php7.2 openssl php-imagick php7.2-common php7.2-curl php7.2-gd php7.2-imap php7.2-intl php7.2-json php7.2-ldap php7.2-mbstring php7.2-mysql php7.2-pgsql php-smbclient php-ssh2 php7.2-sqlite3 php7.2-xml php7.2-zip

 sudo systemctl start apache2.service

 sudo systemctl enable apache2.service

4. Check version PHP

 php -v

Go to browser type this (localhost or your_ip)

http://localhost/

5. Install MariaDB

 sudo apt install mariadb-server

In this command, Set the password for the root and you choose the other options that it will ask you according to what you need.

 sudo mysql_secure_installation

6. Create Database for joomla

 sudo mysql -u root -p

 CREATE DATABASE YOUR_DATABASE_NAME;

 GRANT ALL ON YOUR_DATABASE_NAME.* TO 'YOUR_USER'@'localhost' IDENTIFIED BY 'YOUR_PASSWORD';

 FLUSH PRIVILEGES;

 EXIT;

7. Download Joomla 3-9-21

  sudo wget https://downloads.joomla.org/cms/joomla3/3-9-21/Joomla_3-9-21-Stable-Full_Package.zip

 sudo mkdir /var/www/html/joomla

 sudo unzip Joomla_3-9-21-Stable-Full_Package.zip -d /var/www/html/joomla

Change the permissions

 sudo chown -R www-data:www-data /var/www/html/joomla

 sudo chmod -R 755 /var/www/html/joomla

8. Restar apache2

 sudo systemctl restart apache2

9. Configure apache for joomla

 sudo nano /etc/apache2/sites-available/joomla.conf

Copy the lines into file and then crtl + o and ctrl + x.

<VirtualHost *:80>
     ServerAdmin admin@example.com
     DocumentRoot /var/www/html/joomla/
     ServerName example.com
     ServerAlias www.example.com

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

     <Directory /var/www/html/joomla/>
            Options FollowSymlinks
            AllowOverride All
            Require all granted
     </Directory>
</VirtualHost>

10. Enable Virtual host

 sudo a2ensite joomla.conf

 sudo a2enmod rewrite

 sudo systemctl restart apache2

11. Great the joomla stand up on Ubuntu 20, check

 http://YOUR_IP/joomla

First configuration general add your Site Name, Description, email, username i recomend wrote the same and the last add password

Second Database configuration, choose MySQLI, Hostname: localhost, username: YOUR_USER, password: YOUR_PASSWORD, Database Name: YOUR_DATABASE_NAME