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';

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;
/

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;

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=

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