Thursday, January 21, 2021

Oracle Package

Oracle package with procedure, function and global variable

CREATE OR REPLACE PACKAGE my_package AS
    g_fav_num NUMBER := 5;
    PROCEDURE my_proc (val INTEGER);
    FUNCTION  double_func (in_val IN NUMBER) RETURN NUMBER; 
END my_package;
/

CREATE OR REPLACE PACKAGE BODY my_package ASPROCEDURE my_proc (val INTEGER) IS
  BEGIN
      g_fav_num := g_fav_num + val;
      INSERT INTO my_table ( total ) VALUES ( double_func(g_fav_num) );
  END;

  FUNCTION double_func (in_val IN NUMBER) RETURN NUMBER IS 
  BEGIN 
    RETURN in_val*2;
  END;

END my_package;
/

exec my_package.my_proc(1000);

Thursday, December 17, 2020

Logging Table in Oracle

In Oracle 12c  you create table:

CREATE TABLE msg_log (
    id            NUMBER
        GENERATED BY DEFAULT ON NULL AS IDENTITY,
    ts            TIMESTAMP WITH LOCAL TIME ZONE,
    schema_name   VARCHAR2(15),
    message       VARCHAR2(250)
);

 Create procedure:

CREATE OR REPLACE PROCEDURE log_message (
    message IN VARCHAR2
) AS
    PRAGMA autonomous_transaction;
    schema_name VARCHAR2(15);
BEGIN
    SELECT
        sys_context('userenv', 'current_schema')
    INTO schema_name
    FROM
        dual;

    INSERT /*+ APPEND */ INTO msg_log (
        ts,
        schema_name,
        message
    ) VALUES (
        SYSDATE,
        schema_name,
        message
    );

    COMMIT;
END log_message;
Test
EXEC log_message('foo');

SELECT
    id,
    TO_CHAR(ts, 'YYYY-MM-DD HH24:MM:SS') AS log_date,
    message
FROM
    msg_log; -- where ts > '17-DEC-20 05.05.52.000000000 PM';

Wednesday, December 9, 2020

Oracle Stored Procedure

Sample of Oracle stored procedure with in & out params & exceptions.

CREATE OR REPLACE PROCEDURE proc_name (
  p1 IN VARCHAR2, 
  p2 OUT VARCHAR2
) 
AS 
  msg VARCHAR2(30) := 'hello ';
  ex_some_exception EXCEPTION;

BEGIN 
  IF length(p1) < 2 THEN RAISE ex_some_exception;
  END IF;
  p2 := msg || p1;
EXCEPTION 
  WHEN ex_some_exception THEN raise_application_error(-20001, 'name to short');
  WHEN OTHERS THEN raise_application_error(-20002, 'An error was encountered');
END proc_name;
/

Testing in & out params.

SET SERVEROUTPUT ON
 
DECLARE 
  full_hello VARCHAR(50);
BEGIN 
  proc_name('Mike', full_hello);
  DBMS_OUTPUT.put_line(full_hello);
END;
/

Sunday, August 30, 2020

Kill Session in Oracle

Launch SQLPlus:

[oracle@localhost ~]$ sqlplus
Enter user-name: hr

Enter password: 

EXEC HI -- HI is long running stored procedure

Connect to Oracle as DBA and replace HR with you user

sqlplus sys as sysdba

select sid, serial#, status, program from v$session where username='HR';

ALTER SYSTEM KILL SESSION 'sid, serial#';

In SQL Developer:

select s.username,s.sid,s.serial#,s.last_call_et running,q.sql_text from v$session s 
join v$sqltext_with_newlines q
on s.sql_address = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
and s.username = 'HR'
order by sid,serial#,q.piece;

Tuesday, June 30, 2020

OpenShift on Public IP

Install OpenShift Origin on cloud machine with public IP

Login to remote host

$ ssh -i private_key user@nnn.nnn.nnn.nn

Firewall settings cat /etc/firewalld/zones/public.xml

<?xml version="1.0" encoding="utf-8"?>
<zone>
  <short>Public</short>
  <description>For use in public areas..</description>
  <service name="ssh"/>
  <service name="dhcpv6-client"/>
  <service name="http"/>
  <service name="https"/>
  <port protocol="tcp" port="443"/>
  <port protocol="tcp" port="3000"/>
  <port protocol="tcp" port="3008"/>
  <port protocol="tcp" port="8000"/>
  <port protocol="tcp" port="8080"/>
  <port protocol="tcp" port="8443"/>
  <port protocol="udp" port="53"/>
</zone>

Install Docker

Docker version

# docker version
Client:
 Version:            19.03.11-ol



Download OpenShift and install as root

wget https://github.com/openshift/origin/releases/download/v3.7.2/openshift-origin-client-tools-v3.7.2-282e43f-linux-64bit.tar.gz
tar xf openshift-origin-client-tools-v3.7.2-282e43f-linux-64bit.tar.gz
cd openshift-origin-client-tools-v3.7.2-282e43f-linux-64bit/
mv oc /usr/bin
oc cluster up --public-hostname=


Friday, June 12, 2020

NodeJS Template Engine

Node file app.js

var nunjucks = require('nunjucks')

var res = nunjucks.render(
  'foo.html', {
    messages: ['foo', 'bar'],
    username: "mike"
  }
);

console.log(res)
template file foo.html
{% if username === 'mike' %}
    {{ username }}
{% endif %}

{% for message in messages %}
    {{ message }}
{% endfor %}
Run
node app.js
Practical example for oracle log message
var nunjucks = require('nunjucks')

var res = nunjucks.renderString(
  `
  '{{ "debug output " }}' || {% for var in variables %} '{{ var }} = ' || {{ var }} || {% endfor %}
`, {
    variables: ['foo', 'bar', 'baz']
  }
);

console.log(res.slice(0,-4))
Output
'debug output ' ||  'foo = ' || foo ||  'bar = ' || bar ||  'baz = ' || baz

Tuesday, December 17, 2019

Helm Chart for MySQL in GKE

Here is how to deploy and test connectivity of MySQL in Google Kubernetes Engine GKE

# connect to cluster in Google Kubernetes Engine
$ gcloud container clusters get-credentials <your-cluster> --zone <your-zone> --project <your-project>
$ git clone --depth=1 https://github.com/helm/charts.git
$ cd charts/stable/mysql
$ vim values.yaml
$ helm install --name dbserver -f values.yaml stable/mysql
$ kubectl get pods
$ MYSQL_ROOT_PASSWORD=$(kubectl get secret --namespace default dbserver-mysql -o jsonpath="{.data.mysql-root-password}" | base64 --decode; echo)
$ echo $MYSQL_ROOT_PASSWORD
$ kubectl run -i --tty ubuntu --image=ubuntu:16.04 --restart=Never -- bash -il
root@ubuntu:/# apt-get update && apt-get install mysql-client -y
root@ubuntu:/#  mysql -h dbserver-mysql -p
mysql> exit
root@ubuntu:/# exit
$ kubectl get pvc
$ kubectl get pv
 
# clean up
$ helm delete --purge dbserver
$ kubectl delete pod/ubuntu