SQL Collection

/* SQL Command within postgres: */

SELECT now() - pg_postmaster_start_time() uptime

/* using psql command line utility deployed with Postgres: */
 
$> psql -c "SELECT now() - pg_postmaster_start_time() uptime"

$> sudo apt-get install freetds-bin

#At Lazarus:
#Put TZConnection component (ZConnection1) and set LibraryLocation as shown:

#  ZConnect1.LibraryLocation:=libsybdb.so.5;

#  and we're done!

#!/bin/bash
#
#/etc/init.d/oracledb
#
#Run-level Startup script for the Oracle Listener and Instances
#It relies on the information on /etc/oratab

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbname_1
export ORACLE_OWNR=oracle
export PATH=$PATH:$ORACLE_HOME/bin

if [ ! -f $ORACLE_HOME/bin/dbstart -o ! -d $ORACLE_HOME ]
then
  echo "Oracle startup: cannot start"
  exit 1
fi

case "$1" in
  start)
    #Oracle listener and instance startup
    echo -n "Starting Oracle: "
    su $ORACLE_OWNR -c "$ORACLE_HOME/bin/lsnrctl start"
    su $ORACLE_OWNR -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
    touch /var/lock/oracle
    echo "OK"
    ;;
  stop)
    #Oracle listener and instance shutdown
    echo -n "Shutdown Oracle: "
    su $ORACLE_OWNR -c "$ORACLE_HOME/bin/lsnrctl stop"
    su $ORACLE_OWNR -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
    rm -f /var/lock/oracle
    echo "OK"
    ;;
  reload|restart)
    $0 stop
    $0 start
    ;;
  *)
    echo "Usage: `basename $0` start|stop|restart|reload"
    exit 1
esac

exit 0
##################################################

# let's create a backup from remote postgresql database using pg_dump:
#
#   pg_dump -h [host address] -Fc -o -U [database user] <database name> > [dump file]
#
# later it could be restored at the same remote server using:
#
#   sudo -u postgres pg_restore -C mydb_backup.dump
#
#Ex:

pg_dump -h 67.8.78.10 -p 5432 -Fc -o -U myuser mydb > mydb_backup.dump

pg_restore -C mydb_backup.dump



#complete (all databases and objects)

pg_dumpall -U myuser -h 67.8.78.10 -p 5432 --clean --file=mydb_backup.dump


#restore from pg_dumpall --clean:

psql -f mydb_backup.dump postgres #it doesn't matter which db you select here

# Edit your /etc/postgresql/9.3/main/postgresql.conf, and change the lines as follows:

# Note: If you didn't find the postgresql.conf file, then just type 

$> locate postgresql.conf 

# in a terminal

1) change #log_directory = 'pg_log' to log_directory = 'pg_log'
2) change #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' to log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
3) change #log_statement = 'none' to log_statement = 'all'
4) change #logging_collector = off to logging_collector = on

# Optional: SELECT set_config('log_statement', 'all', true);

sudo /etc/init.d/postgresql restart or sudo service postgresql restart

#Fire query in postgresql: select 2+2

# Find current log in /var/lib/pgsql/9.2/data/pg_log/

#The log files tend to grow a lot over a time, and might kill your machine. For your safety, write a bash script that'll delete logs and restart postgresql server.

---Sql:
    
CREATE EXTENSION dblink;

---Then you can use it like this:
    
select * from dblink('dbname=otherdbname','select field1, field2 from otherdb_table') as foreign_table(field1 varchar, field2 integer)

##src: https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps#configure-the-master-server

############## Master:

psql -c "CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'yourpassword';"

#//at file /etc/postgresql/9.5/main/pg_hba.conf 

	host    replication     rep     IP_address_of_slave/32   md5

#//at file /etc/postgresql/9.5/main/postgresql.conf

	listen_addresses = 'localhost,IP_address_of_THIS_host'
	wal_level = 'hot_standby'
	archive_mode = on
	archive_command = 'cd .'
	max_wal_senders = 1
	hot_standby = on

service postgresql restart


############### Slave:

service postgresql stop

#//at file /etc/postgresql/9.5/main/pg_hba.conf 

	host    replication     rep     IP_address_of_master/32  md5

#//at file /etc/postgresql/9.5/main/postgresql.conf

	listen_addresses = 'localhost,IP_address_of_THIS_host'
	wal_level = 'hot_standby'
	archive_mode = on
	archive_command = 'cd .'
	max_wal_senders = 1
	hot_standby = on


################## Master:

psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.5/main/ slave_IP_address:/var/lib/postgresql/9.5/main/
psql -c "select pg_stop_backup();"


################### Slave:

cd /var/lib/postgresql/9.5/main/recovery.conf

	standby_mode = 'on'
	primary_conninfo = 'host=master_IP_address port=5432 user=rep password=yourpassword'
	trigger_file = '/tmp/postgresql.trigger.5432' ##When we want to set SLAVE db to Master (because of original MASTER fail) creating this file is enough. With the existence of this file db will act like MASTER.

service postgresql start

## we check if no problem:

less /var/log/postgresql/postgresql-9.5-main.log

#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
requires:

1) Install Linux dependences (search for specific linux distro instructions for this):
python-dev, python-pip, freetds-dev, freetds-bin, libaio1

2) Install instantclient-basic-lite
follow these instructions:

http://www.oracle.com/technetwork/database/features/linuxx86-64soft-092277.html?printOnly=1
(go to "Installation of ZIP files" section at the bottom of document)

3) Install python includes:

sudo -H pip install cx_Oracle
sudo -H pip install pymssql

"""

import cx_Oracle
import pymssql

""" ====== let's connect to Oracle DB Server ====="""

orcl_host = "host1"
orcl_port = 1521
orcl_user = "user1"
orcl_pwd  = "password1"
orcl_dbn  = "service_name"

connstr = orcl_user+"/"+orcl_pwd+"@"+orcl_host+":"+str(orcl_port)+"/"+orcl_dbn
orcl = cx_Oracle.connect(connstr)

#If all is correct we will see and object print:

print(orcl)

"""===== let's connect to sqlsvr: ====="""

sql_host = "host2"
sql_user = "user2"
sql_pwd  = "password2"
sql_dbn  = "database_name"

conexion_sql = pymssql.connect(sql_host, sql_user, sql_pwd, sql_dbn)

#If all is correct we will see and object print:

print(conexion_sql)

SELECT TRUNC (SYSDATE - ROWNUM) dt
  FROM DUAL CONNECT BY ROWNUM < :ndates;
  
/* from now on */
SELECT TRUNC (SYSDATE + ROWNUM - 2) dt
  FROM DUAL CONNECT BY ROWNUM < 100

select * from json_to_recordset( (
  select array_to_json(array_agg(row_to_json(t)))
    from (
      select field1, field2, field3,... from mytable
    ) t
)) as x(
  "field1" integer,
  "field2" integer,
  "field3" text,
  :
)

--- ejemplo de texto json a recordset:
select * from json_to_recordset((select detalles from d_factura_caja where id=4216)::json) as x(
  "seq" integer,
  "padre" integer,
  "tipo" text,
  "idx" integer,
  "nivel" integer,
  "descripcion" text,
  "cantidad" numeric(6,2),
  "presentacion" text,
  "precio_unitario" numeric(15,2),
  "precio" numeric(15,2),
  "porc_impuesto" numeric(6,2),
  "impuesto" numeric,
  "neto"numeric
)

SELECT n.nspname AS schema_name
      ,p.proname AS function_name
      ,pg_get_functiondef(p.oid) AS func_def
      ,pg_get_function_arguments(p.oid) AS args
      ,pg_get_function_result(p.oid) AS result
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  p.proname ILIKE 'func_name%';

# Backup:
docker exec -t -u postgres your-db-container pg_dumpall -c > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql

# Restore:
cat your_dump.sql | docker exec -i your-db-container psql -U postgres

<?php

// sudo apt install php7.0-sybase


header("content-type: text/plain; charset=iso-8859-1");
ini_set("display_errors","1");
error_reporting(E_ALL);

$host="localhost";
$db="test";
$uid="sa";
$pwd="mypassword";

$query = "select top 10 * from testtable";

$conn = new PDO( "dblib:host=$host:1433;dbname=$db;", $uid, $pwd);
$stmt = $conn->prepare( $query );
$stmt->execute();

while ($r=$stmt->fetch(PDO::FETCH_ASSOC)){
	print_r($r);
}

?>

create function f_thetable_biud()
returns trigger as
$body$
--declare vars
begin
    -- new, old
    -- tg_op = INSERT,DELETE,UPDATE
    return new/old;
end;
$body$ language plpgsql;

create trigger t_thetable_biud
before/after insert or update or delete
on thetable
for each row execute procedure f_thetable_biud();

SELECT *, count(*) OVER() AS full_count
FROM   tbl
WHERE  /* whatever */
ORDER  BY col1
LIMIT  ?
OFFSET ?

<?php 

$sql = 'select fielda, fieldb, fieldc from table1
order by field, fieldb, fieldc
union all
select field1, field2, field3 from table2
order by field1, field2 desc, field3 asc';


print_r($sql2 = invert_order($sql));


function invert_order($str){
    $re = '/(?<=order\sby\s)(.*)(?=)/mi';

    preg_match_all($re, $str, $matches, PREG_SET_ORDER, 0);
    
    $mat = $matches[sizeof($matches)-1]; //-- get only last order field list
    $mat=$mat[sizeof($mat)-1];
    $mat=explode(",",$mat);
    
    
    for($i=0; $i<sizeof($mat); $i++){   //-- reverse each pair of order field/direction 
        $duet = preg_split("/\s+/", trim($mat[$i]));
        if (sizeof($duet)<2) $duet[1]="";
        switch(strtolower($duet[1])) {
            case "desc":
                $duet[1] = "asc";
                break;
            default:
                $duet[1] = "desc";
                break;
        }
        $mat[$i] = implode(" ",$duet);
    }
    
    $re2 = '/(order\s+by\s+.*)*$/i';    //-- replace last order by with new inverted order by:
    $subst = "order by ".implode(", ",$mat);
    $result = preg_replace($re2, "", $str);
    return $result . " $subst";
}


?>

select id, name from table 
where name ilike all(string_to_array(replace('%'||TRIM('JOHN DOE')||'%',' ','%,%'), ','))

-- 1) it converts spaces into commas (,)
-- 2) it converts string to array using resulting comma separated string
-- 3) it apply ilike to each word in search string

-- It finds (for example) name = 'DOE, JHON'  in table

create table <tablename>_log(
    id serial primary key,
    idx integer,
    antes json,
    despues json,
    id_m_usuario integer,
    fecha_registro timestamp default now(),
    tipo char(1) default 'U',
    unico varchar(36)
);

create function f_<tablename>_log()
returns trigger
LANGUAGE plpgsql as
$body$
begin
    case tg_op
        when 'INSERT' then
            insert into <tablename>_log (idx, despues, tipo)
            select new.id, row_to_json(new), 'I';
            return new;
        when 'UPDATE' then
            if (.. <condiciones new.campo1 <> new.campo2>... ) then
                insert into <tablename>_log (idx, antes, despues, tipo)
                select new.id, row_to_json(old), row_to_json(new), 'U';
            end if;
            return new;
        when 'DELETE' then
            insert into <tablename>_log (idx, antes, tipo)
            select new.id, row_to_json(old), 'D';
            return old;
    end case;
end;
$body$;

create trigger t_<tablename>_log
before insert or update or delete
on <tablename>
for each row execute procedure f_<tablename>_log();

select regexp_matches('FV{YY}{MM}{DD}-{UU}-{###}', '\{([A-Za-z#]+)\}', 'g');

<?php

/*==========  install at ubuntu: ==============

sudo apt install php-sybase


//==================================

append this into /etc/freetds/freetds.conf:
(192.168.0.250:2638 is an example:)

//==================================

sudo nano /etc/freetds/freetds.conf

[connName]
    host = 192.168.0.250  
    port = 2638
    tds version = 7.4
    database = MYDB
    
//======  php: */

$connName = "connName";
$user = "myuser";
$pass = "mypass";

$st="dblib:host=$connName;charset=iso8859-1";
$conn = new PDO($st,$user,$pass);

/* $conn->prepare($sql_query);
...etc
*/

SELECT procpid, age(clock_timestamp(), query_start), usename, current_query 
FROM pg_stat_activity 
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- show running queries (9.2)
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- kill running query
SELECT pg_cancel_backend(procpid);

-- kill idle query
SELECT pg_terminate_backend(procpid);

-- vacuum command
VACUUM (VERBOSE, ANALYZE);

-- all database users
select * from pg_stat_activity where current_query not like '<%';

-- all databases and their sizes
select * from pg_user;

-- all tables and their size, with/without indexes
select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc;

-- cache hit rates (should not be less than 0.99)
SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit)  as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
FROM pg_statio_user_tables;

-- table index usage rates (should not be less than 0.99)
SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

-- how many indexes are in cache
SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM pg_statio_user_indexes;

-- Dump database on remote host to file
$ pg_dump -U username -h hostname databasename > dump.sql

-- Import dump into existing database
$ psql -d newdb -f dump.sql

--(On 9.2+): Queries running more than 2 minutes
SELECT now() - query_start as "runtime", usename, datname, waiting, state, query
  FROM  pg_stat_activity
  WHERE now() - query_start > '2 minutes'::interval
 ORDER BY runtime DESC;

-- 
select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

--
select relname, n_dead_tup, last_vacuum, last_autovacuum from 
pg_catalog.pg_stat_all_tables
where n_dead_tup > 0 and relname =  ’table1' order by n_dead_tup desc;

--Tables and views used by a given view:
with recursive view_tree(parent_schema, parent_obj, child_schema, child_obj, ind, ord) as 
(
  select vtu_parent.view_schema, vtu_parent.view_name, 
    vtu_parent.table_schema, vtu_parent.table_name, 
    '', array[row_number() over (order by view_schema, view_name)]
  from information_schema.view_table_usage vtu_parent
  where vtu_parent.view_schema = '<SCHEMA NAME>' and vtu_parent.view_name = '<VIEW NAME>'
  union all
  select vtu_child.view_schema, vtu_child.view_name, 
    vtu_child.table_schema, vtu_child.table_name, 
    vtu_parent.ind || '  ', 
    vtu_parent.ord || (row_number() over (order by view_schema, view_name))
  from view_tree vtu_parent, information_schema.view_table_usage vtu_child
  where vtu_child.view_schema = vtu_parent.child_schema 
  and vtu_child.view_name = vtu_parent.child_obj
) 
select tree.ind || tree.parent_schema || '.' || tree.parent_obj 
  || ' depends on ' || tree.child_schema || '.' || tree.child_obj txt, tree.ord
from view_tree tree
order by ord;


--Check the size (as in disk space) of all databases:
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) 
    ELSE 'No Access' 
  END AS SIZE 
FROM pg_catalog.pg_database d 
ORDER BY 
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') 
    THEN pg_catalog.pg_database_size(d.datname)
    ELSE NULL 
  END;

--Check the size (as in disk space) of each table:
SELECT nspname || '.' || relname AS "relation",
   pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
   AND C.relkind <> 'i'
   AND nspname !~ '^pg_toast'
 ORDER BY pg_total_relation_size(C.oid) DESC;

--Script to kill all running connections of a current database
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()  
  AND pid <> pg_backend_pid();

select id, id2, debito, credito, 
  sum(debito-credito) over (partition BY id ORDER BY id, id2) saldo
from 
(
	select m.id, 0 id2, importe debito, 0 credito
		from m_cxc m 
		where m.id=:id
	union all 
	select c.id_m_cxc id, c.id id2, debito, credito
		from d_cxc c 
  		where c.id_m_cxc=:id
) a

WITH cte AS (
   SELECT *
   FROM   m_paciente mp 
   WHERE  apellido like 'P%'
   )
SELECT *
FROM  (
   TABLE  cte
   ORDER  BY apellido, nombre
   LIMIT  10
   OFFSET 190
   ) sub
RIGHT  JOIN (SELECT count(*) FROM cte) c(full_count) ON true;

-- Example table:

create table patient (
    id serial primary key,
    firstname varchar(100),
    lastname varchar(100)
);

insert into patient(firstname, lastname) values ('MARCO ANTONIO', 'PEREZ SANDERS');

-- Create field tsvector field with text columns (preferable using insert and update trigger);
alter table patient add tsv_name tsvector;

-- initially fill field.
update patient set tsv_name = to_tsvector(firstname || ' ' || lastname);

-- create index for fast search:
create index tsv_name_idx on patient using gin(tsv_name);

-- then you can search as:
select * from patient where tsv_name @@ to_tsquery('MARCO & PEREZ');
select * from patient where tsv_name @@ to_tsquery('MARCO & SANDERS');
select * from patient where tsv_name @@ to_tsquery('ANTONIO & PEREZ');

select * from m_paciente 
where tsv_nombre @@ to_tsquery(array_to_string(string_to_array('LEIRY SEVERINO',' '),':* & ')||':*');

-- other:

select * from patient where fullname ~ all(array['(?=^LEI.*|\s+LEI\w*)','(?=^ME.*|\s+ME\w*)']);

---$> psql: user my_user and database my_original_db must exists:

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'my_original_db' AND pid <> pg_backend_pid();

CREATE DATABASE my_new_db WITH TEMPLATE my_original_db OWNER my_user;

<?php

//===========notifier.sql:
    
/*
CREATE OR REPLACE FUNCTION public.notify_channel()
RETURNS trigger
AS $function$
  BEGIN
	  PERFORM pg_notify('channel_name', row_to_json(NEW)::text);
	  RETURN NULL;
  END;
$function$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_on_insert AFTER INSERT ON mytable
FOR EACH ROW EXECUTE PROCEDURE notify_channel();
*/

set_time_limit(0);

//-- using PDO:

$db = new PDO(
    'pgsql:dbname=dbname host=host port=5432;options=--application_name=APPLICATION_NAME',
    'user',
    'password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]
);

$db->exec('LISTEN channel_name');

while (true) {
    while ($db->pgsqlGetNotify(PDO::FETCH_ASSOC, 30000)) {
        echo json_encode($result).PHP_EOL;
    }
}

//-- using pg_connect:
//<?php

include '../conn.php';
set_time_limit(0);
ob_end_clean();
pg_query($conn, 'LISTEN table_changed;');

while(true){
    
    $notify = pg_get_notify($conn);
    
	if (!$notify) {
        echo json_encode(array('result'=>false, 'data'=>'No messages')).PHP_EOL;
        ob_flush();
        flush();
        sleep(1);
	} else {
        echo json_encode(array('result'=>true, 'process_id'=>$pid , 'pid' => pg_get_pid($conn), 'data' => $notify)).PHP_EOL;
	}
}

gsec -user sysdba -pass masterkey -add billyboy -pw sekrit66 -admin yes

WITH RECURSIVE PADRE AS (
	SELECT
		COD_CTA,
		DES_CTA
	FROM
		CATALOGO
	WHERE
		COD_CTA=:COD_CTA
	UNION ALL
		SELECT
			e.COD_CTA,
			e.DES_CTA
		FROM
			CATALOGO e
		INNER JOIN padre p ON POSITION(e.COD_CTA, p.COD_CTA) = 1
		  AND p.COD_CTA > COD_CTA
		ROWS 1
) SELECT
	DISTINCT cod_cta
FROM
	PADRE
WHERE COD_CTA < :COD_CTA

:> docker run -it --name fb --rm -v ~/tmp:/tmp almeida/firebird gbak -b -v 192.168.1.251:c:/host/path/database.fdb /tmp/backup.bak -user sysdba -pass XXXXX

#backup

gbak -t -v -user <username> -password "<password>" <host>:/path/to/db.fdb path/to/file.gbk

#restore

gbak -c -v -user <username> -password "<password>" path/to/file.gbk <host>:/path/to/db.fdb

alter role <username> set search_path = <schema1>, ..., <scheman>, public;

#put your databases on /svr/fb-databases

docker run -d --name fb -p 3050:3050 -v /srv/fb-databases:/db almeida/firebird

#connect using localhost:3050:db/<database file>.gdb

CREATE TABLE GAP(
    COUNTER INTEGER PRIMARY KEY
);

insert into gap (counter) values (1);
insert into gap (counter) values (2);
--::::: 3
--::::: 4
insert into gap (counter) values (5);
insert into gap (counter) values (6);
--::::: 7
insert into gap (counter) values (8);
insert into gap (counter) values (9);
insert into gap (counter) values (10);



--===== here the trick :

SELECT 
	list(CASE 
		WHEN N2-N1-2 > 0 THEN (N1+1) || '-'|| (N2-1)
		ELSE CAST(N1+1 AS VARCHAR(10))
	END) Missing
FROM (
SELECT a.counter N1, 
  (SELECT counter FROM gap WHERE counter > a.counter rows 1) N2 
FROM gap a
) B
WHERE N2-N1 > 1;



-- results:

Missing
=======
3-4,7

sudo mkdir -p /your/custom/path/oracle-19c/oradata/
sudo chmod -R 777 /your/custom/path/

docker run -d --name oracle19db \
  -p 1521:1521 \
  -e ORACLE_SID=ORCL \
  -e ORACLE_PDB=ORCLDB \
  -e ORACLE_PWD=Oracle123 \
  -e ORACLE_CHARSET=AL32UTF8 \
  -v /your/custom/path/oracle-19c/oradata:/opt/oracle/oradata \
  banglamon/oracle193db:19.3.0-ee

# Charset Value: WE8MSWIN1252, AL16UTF8

# ALTER SESSION SET NLS_DATE_FORMAT = 'RRRR-MM-DD';
# ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SS';
# ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'RRRR-MM-DD HH24:MI:SS';
# ALTER SESSION SET NLS_TIME_TZ_FORMAT = 'HH24:MI:SS TZR';
# ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'RRRR-MM-DD HH24:MI:SS TZR';

# docker exec -it oracle19db bash -c "source /home/oracle/.bashrc; sqlplus /nolog”
# connect sys as sysdba;

# alter session set "_ORACLE_SCRIPT"=true;
# create user sistemas identified by las36horas;
# GRANT CONNECT, RESOURCE, DBA TO sistemas;
# GRANT UNLIMITED TABLESPACE TO sistemas;

CREATE OR ALTER PROCEDURE GETINTEGERLIST(AINTEGERLIST VARCHAR(32000))
returns (
  ID integer
)
as
  declare variable IntegerList varchar(32000);
  declare variable CommaPos integer;
  declare variable IntegerVal varchar(10);
begin
  IntegerList = AIntegerList || ' ';
  CommaPos = Position(',', IntegerList);

  while (CommaPos > 0) do
  begin
    IntegerVal = Trim(SubString(IntegerList from 1 for CommaPos - 1));

    if (Char_Length(IntegerVal) > 0) then
    begin
      if (IntegerVal similar to '[0-9]*') then
      begin
        ID = Cast(IntegerVal as integer);
        suspend;
      end
    end

    if (Char_Length(IntegerList) > CommaPos) then
      IntegerList = SubString(IntegerList from CommaPos + 1);
    else
      IntegerList = '';

    CommaPos = Position(',', IntegerList);
  end

  IntegerList = Trim(IntegerList);

  if (Char_Length(IntegerList) > 0) then
  begin
    if (IntegerList similar to '[0-9]*') then
    begin
      ID = Cast(IntegerList as integer);
      suspend;
    end
  end
end;

/* use:

SELECT *
FROM CITY
WHERE ID_CITY IN (SELECT ID FROM GetIntegerList('1, 2, 12, 45, 75, 45'))

*/

#use oracle user from system:

sqlplus "/ as sysdba"

SQL> ALTER USER SYS IDENTIFIED BY [password]; 
SQL> ALTER USER SYSTEM IDENTIFIED BY [password];

DROP TABLESPACE MYSPACENAME INCLUDING contents;

CREATE TABLESPACE MYSPACENAME 
   DATAFILE 'ts1_filename.dbf' 
   SIZE 2000m 
   autoextend on NEXT 1000m maxsize unlimited;
   
 alter tablespace MYSPACENAME coalesce;

alter session set "_ORACLE_SCRIPT"=true;

CREATE USER SISTEMAS IDENTIFIED BY las36horas;

GRANT CREATE TABLE TO SISTEMAS;
GRANT CONNECT TO SISTEMAS;
GRANT CTXAPP TO SISTEMAS;
GRANT RESOURCE TO SISTEMAS;
GRANT CREATE ANY CONTEXT TO SISTEMAS;
GRANT CREATE ANY SYNONYM TO SISTEMAS;
GRANT CREATE VIEW TO SISTEMAS;
GRANT DROP ANY CONTEXT TO SISTEMAS;
GRANT QUERY REWRITE TO SISTEMAS;
EXECUTE ON SYS.DBMS_AQ_BQVIEW
EXECUTE ON SYS.DBMS_LOB
EXECUTE ON SYS.UTL_RAW

export ORACLE_SID=$1
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9
export USUARIO=system/org24h
export PATHBACKUP=/respaldo/o24/export
export FILENAME=CMLGDB`date +%d%m%Y%H%M`.DMP
export FILENAMELOG=CMLGDB`date +%d%m%Y%H%M`.log
echo  $PATHBACKUP

rm $PATHBACKUP/*.* -rf

if [ -a $PATHBACKUP ] ; then
	expdp $USUARIO FULL=yes DUMPFILE=dpump_dir1:$FILENAME LOGFILE=dpump_dir1:$FILENAMELOG
	#exp $USUARIO file=$PATHBACKUP/$FILENAME full=yes compress=yes indexes=no consistent=yes log=$PATHBACKUP/$FILENAMELOG
else
	echo "ERROR: Export no encontro el directorio de Respaldo"
	exit 1
fi

select * from 
( 
    select 
        sql_id, 
        elapsed_time, 
        executions, 
        sql_text 
    from v$sql  
    ORDER BY elapsed_time desc
) 
where rownum < 11;

You can determine the version of the primary MDF file of a database by looking at the two bytes at offset 0x12064

SQL Server Version	    Internal DB Version     DB Compat Level	    Supported DB Compatibility Levels
SQL Server 2022             ?                           160	                       ?
SQL Server 2019 CTP 3.2 / RC 1 / RC 1.1 / RTM	
                            904	                        150	        150,140,130,120,110,100
SQL Server 2019 CTP 3.0 / 3.1	
                            902	                        150	        150,140,130,120,110,100
SQL Server 2019 CTP 2.3 / 2.4 / 2.5	
                            897	                        150	        150,140,130,120,110,100
SQL Server 2019 CTP 2.1 / 2.2	
                            896	                        150	        150,140,130,120,110,100
SQL Server 2019 CTP 2.0	    895	                        150	        150,140,130,120,110,100
SQL Server 2017	            868 / 869	                140	        140,130,120,110,100
SQL Server 2016	            852	                        130         130,120,110,100
SQL Server 2014	            782	                        120	        120,110,100
SQL Server 2012	            706	                        110	        110,100,90
SQL Server 2012 CTP1
(a.k.a. SQL Server 2011 Denali)	
                            684	                        110	        110,100,90
SQL Server 2008 R2	        660 / 661	                100	        100,90,80
SQL Server 2008	            655	                        100	        100,90,80
SQL Server 2005 SP2+
with VarDecimal enabled	    612	                        90	        90,80,70
SQL Server 2005	            611	                        90	        90,80,70
SQL Server 2000	            539	                        80	        80,70
SQL Server 7.0	            515	                        70	        70
SQL Server 6.5	            408                     	65	        65
SQL Server 6.0	            406	                        60	        60

#Copy the image

$ docker pull doctorkirk/oracle-19c

#Create local directory

$ mkdir -p /your/custom/path/oracle-19c/oradata
$ cd /your/custom/path/
$ sudo chown -R 54321:54321 oracle-19c/

#Run the Container

docker run --name oracle-19c \
  -p 1521:1521 \
  -e ORACLE_SID=[ORACLE_SID] \
  -e ORACLE_PWD=[ORACLE_PASSWORD] \
  -e ORACLE_CHARACTERSET=[CHARSET] \
  -v /your/custom/path/oracle-19c/oradata/:/opt/oracle/oradata \
doctorkirk/oracle-19c

#Charset: WE8MSWIN1252(*default), AL16UTF8, US7ASCI
#* If omitted in docker run , the default characterset for this build will be WE8MSWIN1252.

#Backup

gbak -b -v -user SYSDBA -password "masterkey" D:\database.FDB E:\database.fbk

#Restore

gbak -c -user SYSDBA -password masterkey E:\database.fbk E:\database_restore.fdb

CREATE OR ALTER FUNCTION AGE(DFROM TIMESTAMP, DTO TIMESTAMP, CODED BOOLEAN = FALSE)
RETURNS VARCHAR(30)
AS
DECLARE y varchar(3);
DECLARE m varchar(2);
DECLARE d varchar(2);
BEGIN
	y = CASE 
			WHEN datediff(year, :DFROM, :DTO) <> datediff(day, :DFROM, :DTO)/365
				THEN datediff(year, :DFROM, :DTO)-1
			ELSE datediff(year, :DFROM, :DTO)
		END;
	m = CASE 
			WHEN datediff(year, :DFROM, :DTO) <> datediff(day, :DFROM, :DTO)/365
				AND datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO)<0
				THEN datediff(month, dateadd(year, datediff(year, :DFROM, :DTO)-1, :DFROM), :DTO)-1
			WHEN datediff(year, :DFROM, :DTO) <> datediff(day, :DFROM, :DTO)/365
				AND datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO)>=0
				THEN datediff(month, dateadd(year, datediff(year, :DFROM, :DTO)-1, :DFROM), :DTO)
			WHEN datediff(year, :DFROM, :DTO) = datediff(day, :DFROM, :DTO)/365
				AND datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO)<0
				THEN datediff(month, dateadd(year, datediff(year, :DFROM, :DTO), :DFROM), :DTO)-1
			ELSE datediff(month, dateadd(year, datediff(year, :DFROM, :DTO), :DFROM), :DTO)
		END;
	d = CASE 
			WHEN datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO)<0
				THEN datediff(day, dateadd(month, datediff(month, :DFROM, :DTO)-1, :DFROM), :DTO)
			ELSE datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO)
		END;
	RETURN 
		CASE 
			WHEN :CODED THEN lpad(Y,3,'0')||'-'||lpad(m,2,'0')||'-'||lpad(d,2,'0')
			ELSE Y||'y '||m||'m '||d||'d' 
		END;
END;

select 
  DATE_PART('year', AGE('2012-03-05', '2010-04-01')) AS years,
  DATE_PART('month', AGE('2012-03-05', '2010-04-01')) AS months,
  DATE_PART('day', AGE('2012-03-05', '2010-04-01')) AS days;

CREATE OR ALTER PROCEDURE date_range(
    startdate date, 
    enddate date, 
    interv integer DEFAULT 1, 
	unit varchar(6) DEFAULT 'DAY', 
	fromtime time DEFAULT null, 
	totime time DEFAULT null
) RETURNS (dateval timestamp)
AS
BEGIN
    dateval = startdate;
    while (dateval < enddate) do
    BEGIN
	    IF ((:fromtime IS NULL OR cast(:dateval AS time) >= :fromtime)
		  AND (:totime IS NULL OR cast(:dateval AS time) < :totime)) THEN 
		BEGIN 
			suspend;
		END 
		
  		SELECT 
	  		CASE  
		  		WHEN :unit = 'MINUTE' THEN dateadd(MINUTE, :interv, :dateval)
		  		WHEN :unit = 'HOUR' THEN dateadd(HOUR, :interv, :dateval)
		  		WHEN :unit = 'YEAR' THEN dateadd(YEAR, :interv, :dateval)
		  		WHEN :unit = 'MONTH' THEN dateadd(MONTH, :interv, :dateval)
		  		else dateadd(DAY, :interv, :dateval)
			END 
		FROM RDB$DATABASE 
		INTO :dateval;
    END
END;

/*
Example: ranges from today to next 10 days, from 8:00am to 6:00pm, in fraction of 30 minutes:

SELECT 
    cast(dateval AS date) TheDate, 
    CAST(dateval AS time) FromTime, 
    CAST(dateadd(MINUTE, 30, dateval) AS time) ToTime 
FROM date_range(
    current_date, ---- initial date
    current_date + 10, ---- next 10 days
    30, 'MINUTE', ---- fraction
    time '08:00', time '18:00') --- from time, to time
*/

CREATE OR ALTER FUNCTION DATE_TO_CHAR(TDATE TIMESTAMP, FORMAT VARCHAR(100))
RETURNS VARCHAR(100)
AS
DECLARE SDATE VARCHAR(100);
DECLARE DD VARCHAR(2);
DECLARE MM VARCHAR(2);
DECLARE YY VARCHAR(4);
DECLARE HH VARCHAR(2);
DECLARE MI VARCHAR(2);
DECLARE SS VARCHAR(2);
DECLARE XFORMAT VARCHAR(100);
DECLARE AM VARCHAR(2);
BEGIN
	XFORMAT = UPPER(FORMAT);
	SDATE = CAST(TDATE AS VARCHAR(100));
	YY = SUBSTRING(SDATE FROM 1 FOR 4);
	MM = SUBSTRING(SDATE FROM 6 FOR 2);
	DD = SUBSTRING(SDATE FROM 9 FOR 2);
	HH = SUBSTRING(SDATE FROM 12 FOR 2);
	MI = SUBSTRING(SDATE FROM 15 FOR 2);
	SS = SUBSTRING(SDATE FROM 18 FOR 2);
	XFORMAT = REPLACE(XFORMAT, 'YYYY', YY);
	XFORMAT = REPLACE(XFORMAT, 'MM', MM);
	XFORMAT = REPLACE(XFORMAT, 'DD', DD);
	XFORMAT = REPLACE(XFORMAT, 'YY', SUBSTRING(YY FROM 3 FOR 2));
	XFORMAT = REPLACE(XFORMAT, 'HH24', HH);
	AM = 'AM';

	IF (HH='12') THEN
	BEGIN
		AM = 'M';
		IF (MI > '00') THEN 
		BEGIN
			AM='PM';
		END
	END
	
	IF (HH='00') THEN 
	BEGIN
		HH='12';
		AM='AM';
	END
	
	IF (HH>'12') THEN
	BEGIN
		HH = TRIM(CAST(CAST(HH AS INTEGER)-12 AS VARCHAR(2)));
		IF (CHAR_LENGTH(HH)<2) THEN 
		BEGIN
			HH='0'||HH;
		END
		AM='PM';
	END
	XFORMAT = REPLACE(XFORMAT, 'HH12', HH);
	XFORMAT = REPLACE(XFORMAT, 'HH', HH);
	XFORMAT = REPLACE(XFORMAT, 'MI', MI);
	XFORMAT = REPLACE(XFORMAT, 'SS', SS);
	
	RETURN XFORMAT;
END;

/* Examples:

    SELECT DATE_TO_CHAR(CURRENT_TIMESTAMP, 'DD/MM/YYYY HH24:MI:SS') FROM RDB$DATABASE;
    SELECT DATE_TO_CHAR(CURRENT_TIMESTAMP, 'DD/MM/YYYY HH12:MI AM') FROM RDB$DATABASE;
    SELECT DATE_TO_CHAR(CURRENT_TIMESTAMP, 'DD/MM/YY HH24:MI') FROM RDB$DATABASE;
    -- still uncomplete...
*/

/*use "sa" user*/

/*backup*/
BACKUP DATABASE [servicedesk] TO DISK = N'/var/opt/mssql/data/SDDB20230221.BAK';

/*restore*/
ALTER DATABASE [servicedesk] SET OFFLINE WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [servicedesk] FROM DISK = N'/var/opt/mssql/data/SDDB20230221.BAK' WITH REPLACE;

/*go online after restore*/
ALTER DATABASE [servicedesk] SET ONLINE;

SELECT 
    req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.cpu_time AS cpu_time_ms
    , req.total_elapsed_time - req.cpu_time AS wait_time
    , req.logical_reads
    , REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)  
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' ')  AS statement_text  
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;

CREATE OR ALTER PROCEDURE SP_SPLIT_INTO_WORDS (A_TEXT VARCHAR(32000), A_DELS VARCHAR(100) default ',.<>/?;:''"[]{}`~!@#$%^&*()-_=+\|/', A_SPECIAL CHAR(1) default ' ')
RETURNS (
	WORD VARCHAR(50), POSIC integer
)
AS
DECLARE norder integer;
BEGIN

	-- Aux SP, used only in oltp_data_filling.sql to filling table PATTERNS
	-- with miscelan combinations of words to be used in SIMILAR TO testing.
	
	norder = 0;

	for
	    with recursive
	    j as( -- loop #1: transform list of delimeters to rows
	        select s,1 i, substring(s from 1 for 1) del
	        from(
	          select replace(:a_dels,:a_special,'') s
	          from rdb$database
	        )
	        
	        UNION ALL
	        
	        select s, i+1, substring(s from i+1 for 1)
	        from j
	        where substring(s from i+1 for 1)<>''
	    )
	 
	    ,d as(
	        select :a_text s, :a_special sp from rdb$database
	    )
	    ,e as( -- loop #2: perform replacing each delimeter to `space`
	        select d.s, replace(d.s, j.del, :a_special) s1, j.i, j.del
	        from d join j on j.i=1
	 
	        UNION ALL
	 
	        select e.s, replace(e.s1, j.del, :a_special) s1, j.i, j.del
	        from e
	        -- nb: here 'column unknown: e.i' error will be on old builds of 2.5,
	        -- e.g: WI-V2.5.2.26540 (letter from Alexey Kovyazin, 24.08.2014 14:34)
	        join j on j.i = e.i + 1
	    )
	    ,f as(
	        select s1 from e order by i desc rows 1
	    )
	    
	    ,r as ( -- loop #3: perform split text into single words
	        select iif(t.k>0, substring(t.s from t.k+1 ), t.s) s,
	             iif(t.k>0,position( del, substring(t.s from t.k+1 )),-1) k,
	             t.i,
	             t.del,
	             iif(t.k>0,left(t.s, t.k-1),t.s) word
	        from(
	          select f.s1 s, d.sp del, position(d.sp, s1) k, 0 i from f cross join d
	        )t
	 
	        UNION ALL
	 
	        select iif(r.k>0, substring(r.s from r.k+1 ), r.s) s,
	             iif(r.k>0,position(r.del, substring(r.s from r.k+1 )),-1) k,
	             r.i+1,
	             r.del,
	             iif(r.k>0,left(r.s, r.k-1),r.s) word
	        from r
	        where r.k>=0
	    )
	    select word from r where word>''
	    INTO word
	do
	BEGIN
		posic = norder;
    	suspend;
    	norder = norder + 1;
    end
end;

CREATE TABLE alumno_srch_nombre(
	id integer NOT NULL,
	valor varchar(255) NOT NULL,
	orden integer
);

CREATE INDEX ndx_alum_srch_nombre ON alumno_srch_nombre(id, valor);

CREATE OR ALTER TRIGGER t_alumno_biu_ndx
FOR alumno 
BEFORE INSERT OR UPDATE 
AS
BEGIN
	DELETE FROM alumno_srch_nombre
	WHERE id = NEW.id;

	INSERT INTO alumno_srch_nombre
	SELECT NEW.id, word, posic FROM SP_SPLIT_INTO_WORDS(NEW.apenom);
END;

SELECT * FROM (
SELECT a.*,
  CASE 
	  WHEN a.apenom LIKE replace(:INPUT,' ','%') || '%' THEN 0
	  ELSE 1
  END NIVEL
FROM alumno a
INNER JOIN (
	SELECT id, count(*) cnt
	FROM (
		SELECT id, word, min(orden) orden 
		FROM SP_SPLIT_INTO_WORDS(:input) x
		INNER JOIN ALUMNO_SRCH_NOMBRE asn ON asn.valor LIKE word || '%'
		GROUP BY id, word
	)
	GROUP BY id
	HAVING count(*) = (SELECT count(*) FROM SP_SPLIT_INTO_WORDS(:input))
) b ON b.id=a.id
) ORDER BY NIVEL;


--- OTHER EXAMPLE:

SELECT a.* FROM alumno a
INNER JOIN (
	SELECT id, count(*) cant FROM alumno
	INNER JOIN SP_SPLIT_INTO_WORDS(:input) X ON 
		ALUMNO.APELLIDOS LIKE X.WORD||'%'
	  		OR ALUMNO.NOMBRES LIKE X.WORD||'%'
	  		OR ALUMNO.CEDULA LIKE X.WORD||'%'
	GROUP BY id
	HAVING count(*) = (SELECT count(*) FROM SP_SPLIT_INTO_WORDS(:input))
) x ON a.id = x.id 
UNION 
SELECT a.* FROM ALUMNO a
where a.cedula LIKE :input || '%'

CREATE TABLE SECUENCIA(
	ID INTEGER PRIMARY KEY,
	CODIGO VARCHAR(60) NOT NULL,
	PREFIJO VARCHAR(60),
	DESCRIPCION VARCHAR(255) NOT NULL,
	NUMERO INTEGER DEFAULT 0 NOT NULL
);

CREATE SEQUENCE SECUENCIA_ID_GEN;

CREATE TRIGGER T_SECUENCIA_BI
FOR SECUENCIA BEFORE INSERT
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
      NEW.ID = GEN_ID(SECUENCIA_ID_GEN, 1);
END;


CREATE OR ALTER PROCEDURE SP_GENERA_SECUENCIA(CODIGO VARCHAR(60)) 
RETURNS (
	SECUENCIA INTEGER
)
AS
DECLARE SEC INTEGER;
BEGIN
  UPDATE SECUENCIA 
  	SET NUMERO = NUMERO + 1 
  WHERE CODIGO = :CODIGO
  RETURNING NUMERO INTO :SECUENCIA;

  IF (SECUENCIA IS NULL) THEN
  BEGIN
  	INSERT INTO SECUENCIA(CODIGO, DESCRIPCION, NUMERO) VALUES (:CODIGO, :CODIGO, 1);
  	SECUENCIA = 1;
  END
  SUSPEND;
END;

-- EJ
/*
 * SELECT SECUENCIA FROM SP_GENERA_SECUENCIA('PRUEBA');
 * ó .. NEW.SECUENCIA = (SELECT SECUENCIA FROM SP_GENERA_SECUENCIA('PRUEBA'))
 */

CREATE OR ALTER PROCEDURE DATE_TO_CHAR(TDATE TIMESTAMP, FORMAT VARCHAR(100)) RETURNS (
	RESULT VARCHAR(100)
)
AS
DECLARE SDATE VARCHAR(100);
DECLARE DD VARCHAR(2);
DECLARE MM VARCHAR(2);
DECLARE YY VARCHAR(4);
DECLARE HH VARCHAR(2);
DECLARE MI VARCHAR(2);
DECLARE SS VARCHAR(2);
DECLARE ZZZ VARCHAR(3);
DECLARE XFORMAT VARCHAR(100);
DECLARE AM VARCHAR(2);
BEGIN
	XFORMAT = UPPER(FORMAT);
	SDATE = CAST(TDATE AS VARCHAR(100));
	YY = SUBSTRING(SDATE FROM 1 FOR 4);
	MM = SUBSTRING(SDATE FROM 6 FOR 2);
	DD = SUBSTRING(SDATE FROM 9 FOR 2);
	HH = SUBSTRING(SDATE FROM 12 FOR 2);
	MI = SUBSTRING(SDATE FROM 15 FOR 2);
	SS = SUBSTRING(SDATE FROM 18 FOR 2);
	ZZZ = SUBSTRING(SDATE FROM 21 FOR 3);

	XFORMAT = REPLACE(XFORMAT, 'YYYY', YY);
	XFORMAT = REPLACE(XFORMAT, 'MM', MM);
	XFORMAT = REPLACE(XFORMAT, 'DD', DD);
	XFORMAT = REPLACE(XFORMAT, 'YY', SUBSTRING(YY FROM 3 FOR 2));
	XFORMAT = REPLACE(XFORMAT, 'HH24', HH);

	AM = 'AM';

	IF (HH='12') THEN
	BEGIN
		AM = 'M';
		IF (MI > '00') THEN 
		BEGIN
			AM='PM';
		END
	END
	
	IF (HH='00') THEN 
	BEGIN
		HH='12';
		AM='AM';
	END
	
	IF (HH>'12') THEN
	BEGIN
		HH = TRIM(CAST(CAST(HH AS INTEGER)-12 AS VARCHAR(2)));
		IF (CHAR_LENGTH(HH)<2) THEN 
		BEGIN
			HH='0'||HH;
		END
		AM='PM';
	END
	XFORMAT = REPLACE(XFORMAT, 'HH12', HH);
	XFORMAT = REPLACE(XFORMAT, 'HH', HH);
	XFORMAT = REPLACE(XFORMAT, 'MI', MI);
	XFORMAT = REPLACE(XFORMAT, 'SS', SS);
	XFORMAT = REPLACE(XFORMAT, 'ZZZ', ZZZ);

	RESULT = XFORMAT;
	SUSPEND;
END;

/********* tables *************/

CREATE TABLE xsequences (
	id      int IDENTITY(1,1) NOT NULL,
	code    varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	prefix  varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT '' NOT NULL,
	digits  int DEFAULT 1 NOT NULL,
	currsec int DEFAULT 0 NOT NULL,
	PRIMARY KEY (id)
);

CREATE INDEX ndx_seq_code_prf ON xsequences(code, prefix);

CREATE TABLE HISDBTST.dbo.xsequences_format (
	id      int IDENTITY(1,1) NOT NULL,
	code    varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	prefix  varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT '' NOT NULL,
	digits  int DEFAULT 1 NOT NULL,
	PRIMARY KEY (id)
);

CREATE INDEX ndx_seq_fmt_code ON xsequences_format(code);




/************************ p_sequence: Sequence generator ***********/
/*
code            prefix          digits (zero filled)
--------------  -----------     ------
PATRECORD             yyMM-	    4
EMERGENCY       "EM"yyMMdd-	    3
OUTPATIENT	    "OP"yyMMdd-	    3
INPATIENT   	"IP"yyMMdd-	    3
REQUIREMENT	    "RQ"yyMMdd-	    3
*/

CREATE procedure [dbo].p_sequence(
	@code   varchar(20), 
	@date   datetime, 
	@result varchar(100) output
)
as
begin
	declare @currsec int;
	declare @prefix varchar(20);
	declare @digits int;

	select @prefix=prefix,
	        @digits=digits
	from xsequences_format sf 
	where code=@code;

	set @prefix = format(isnull(@date,getdate()), @prefix);
	
	insert into xsequences(code, prefix, currsec)
	select @code, @prefix, 0
	WHERE NOT EXISTS(SELECT ID FROM xsequences WHERE code=@code and prefix=@prefix);	

	update dbo.xsequences 
	set currsec=currsec + 1, @currsec = currsec + 1
	WHERE code=@code and prefix=@prefix;

	set @result = isnull(@prefix,'') + dbo.lpad(@currsec, @digits);
end;



/********************* trigger example **************************/

CREATE TRIGGER [dbo].t_patient_record  ON  [dbo].patient 
for insert not for replication 
AS 
BEGIN
	SET NOCOUNT ON;
	
	declare @RecordNo varchar(30)
	declare @Date datetime = GETDATE();

	-- select @Date = Creation from inserted;

    /***/
	exec p_sequence 'RECORD', @Date, @RecordNo output;
	/***/
	
	update Pacient set RecordNo=@RecordNo where PatientId =(select PatientId from inserted)
END

WITH RECURSIVE date_range AS (
    SELECT date'2019-01-03' dateval, 0 mes  /*start date, use cast(? as date) if you need a parameter */
    FROM rdb$database
    UNION ALL 
    SELECT DATEADD(MONTH,1,dateval), mes + 1
    FROM date_range
    WHERE mes < 12 /* end date */
)
SELECT *
FROM date_range;

create procedure generate_range(startvalue integer, endvalue integer)
    returns (seq integer)
as
begin
    seq = startvalue;
    suspend;

    while (seq < endvalue) do
    begin
        seq = seq + 1;
        suspend;
    end
end;

/* Ex: select seq from generate_range(1, 100); */