Powershell: basic listener

Found here

$Listener = [System.Net.Sockets.TcpListener]8080;
$Listener.Start();
$Listener.AcceptSocket();

Jenkins: print password

stage('Sandbox') {
withCredentials([usernamePassword(credentialsId: 'TestUser', passwordVariable: 'C_PASS', usernameVariable: 'C_USER')]) {
creds = "\nUser: ${C_USER}\nPassword: ${C_PASS}\n"
}
println creds
}

GIT: Run the changes from current branch

Run the difference between the current feature branch and target branch (configured to dev) to which the feature branches are merged in the current dev process.
The $file_list array is built with the files:

• from merge commit if feature branch is already merged
• from fork-point if the branch is not merged
  In my scenario all the files are run in SQL*PLUS, but $file_list variable can be used for any purpose

# Run changes made in current branch comparing to $traget_branch_name = dev branch in the database given in the first parameter
# If changes is already merged thus it merge-base --fork-point produce wrong results - the git log -m -1 --first-parent is used for merge commit to find difference
# If no merge commit present merge-base --fork-point is used to build the difference


$traget_branch_name = 'dev'
$repo = 'C:\code\myrepo'
cd $repo
$dbname=$args[0]
$script = "connect $dbname"

$merge_commit = git log HEAD..$traget_branch_name --ancestry-path --merges --reverse --pretty=format:%h | Select-Object -First 1

if ( $merge_commit -eq $null )
{
    '...No subsequent merges...'
    $file_list = (git diff --name-only $(git merge-base --fork-point $traget_branch_name))
} else {
    "Merge commit found $merge_commit"
    $file_list = git log -m -1 --first-parent --name-only --pretty="format:" $merge_commit
}
$file_list

#process file list in sql*plus
$file_list | 
    ForEach {
            $fullname = "@$repo$_"
            $script += "PROMPT Running $fullname`n"
            $script += "@$fullname`n"
        }

$script += 'EXIT'

echo $script 

echo $script | sqlplus /nolog

Print JSON attributes names and types

Script to traverse and print types (except one with null values) and change values to attribute names (useful for testing purposes)

DECLARE
   l_clob CLOB;
   l_json json_object_t;

   l_number NUMBER := 1;   
   
   FUNCTION process_object(p_json json_object_t, p_level NUMBER := 0, p_parent_object_name VARCHAR2 := NULL) RETURN json_object_t IS
      l_keys  JSON_KEY_LIST;
      l_padding VARCHAR2(100):= LPAD(' ', p_level * 3, ' ');
      
      l_result json_object_t := p_json;
   BEGIN
      l_keys := p_json.get_keys;
      
      FOR i IN 1 .. l_keys.count LOOP
         IF p_json.get(l_keys(i)).is_object THEN
            dbms_output.put_line(l_padding || l_keys(i) || ' is object');
            l_result.put(
               l_keys(i), 
               process_object(TREAT(p_json.get(l_keys(i)) AS json_object_t), p_level + 1, LTRIM(p_parent_object_name || '.' || l_keys(i), '.'))
               );
         ELSIF p_json.get(l_keys(i)).is_number THEN
            dbms_output.put_line(l_padding || l_keys(i) || ' is number');
            l_result.put(l_keys(i), l_number);
            l_number := l_number + 1;
            --             
         ELSIF p_json.get(l_keys(i)).is_string THEN
            dbms_output.put_line(l_padding || l_keys(i) || ' is string');
            l_result.put(l_keys(i), LTRIM(p_parent_object_name || '_', '_') || l_keys(i));
         ELSIF p_json.get(l_keys(i)).is_array THEN
            dbms_output.put_line(l_padding || l_keys(i) || ' is array');

            DECLARE
              l_arr JSON_ARRAY_T := TREAT(p_json.get(l_keys(i)) AS JSON_ARRAY_T);
              l_new_array JSON_ARRAY_T := NEW JSON_ARRAY_T;
            BEGIN  
              FOR j IN 0 .. l_arr.get_size - 1 LOOP
                IF l_arr.get(j).is_object THEN
                  l_new_array.append(
                     process_object(TREAT(l_arr.get(j) AS json_object_t), p_level + 1, LTRIM(p_parent_object_name || '.' || l_keys(i) || '[' || j || ']', '.'))
                     );
                END IF;
                -- check other types?
              END LOOP;
              l_result.put(l_keys(i), l_new_array);
            END;
         ELSE 
            dbms_output.put_line(l_padding || l_keys(i) || ' is unknown');
            l_result.put(l_keys(i), LTRIM(p_parent_object_name || '_', '_') || l_keys(i));
         END IF;
      END LOOP;
      
      RETURN l_result;
   END;
BEGIN
   l_json := json_object_t.parse(l_clob);
   dbms_output.put_line( process_object(l_json).stringify );
END;
/   

Generate queries for PK-FK data check

Given:

• table to be inspected
  • l_owner
  • l_table_name
• row in this table
  • l_table_condition
    Result: generated queries to extract parent rows (Reference to section) and child rows (Referenced by section)

DECLARE
   l_owner VARCHAR2(30) := UPPER('schema_name');
   l_table_name VARCHAR2(30) := UPPER('table_name');
   l_table_condition VARCHAR2(32767) := 't.id = 133';
   
   l_chi_cols VARCHAR2(32767);
   l_par_cols VARCHAR2(32767);
         
   FUNCTION get_constraint_columns(p_cons_owner VARCHAR2, p_cons_name VARCHAR2) RETURN VARCHAR2 IS
      l_result VARCHAR2(32767);
   BEGIN
      SELECT LISTAGG(cols.column_name, ', ') WITHIN GROUP (ORDER BY cols.position)
      INTO l_result 
      FROM dba_cons_columns cols
      WHERE cols.owner = p_cons_owner
        AND cols.constraint_name = p_cons_name;
      
      RETURN l_result;   
   END;
BEGIN
   dbms_output.put_line('-- Reference to');
   dbms_output.put_line(LPAD('-', 10, '-'));  
   FOR rec IN (
      SELECT c.owner chi_owner, c.table_name chi_table_name, c.constraint_name chi_constraint_name
        , p.owner par_owner, p.table_name par_table_name, p.constraint_name par_constraint_name
      FROM dba_constraints c,
        dba_constraints p
      WHERE c.owner = l_owner
        AND c.table_name = l_table_name
        AND c.constraint_type = 'R'
        AND p.owner = c.r_owner
        AND p.constraint_name = c.r_constraint_name
   ) LOOP
      l_chi_cols := get_constraint_columns(rec.chi_owner, rec.chi_constraint_name);
      l_par_cols := get_constraint_columns(rec.par_owner, rec.par_constraint_name);
      dbms_output.new_line; 
      dbms_output.put_line('-- ' || rec.chi_owner || '.' || rec.chi_table_name || '.' || l_chi_cols || ' -> ' 
         || rec.par_owner || '.' || rec.par_table_name || '.' || l_par_cols);
      dbms_output.put_line(
'SELECT * ' || CHR(10) ||
'FROM ' || rec.par_owner || '.' || rec.par_table_name || ' p' || CHR(10) ||
'WHERE (' || l_par_cols || ') IN (' || CHR(10) ||     
'  SELECT ' || l_chi_cols || CHR(10) ||
'  FROM ' || rec.chi_owner || '.' || rec.chi_table_name || ' t' || CHR(10) ||
'  WHERE ' || l_table_condition || CHR(10) ||
');'    
); 
   END LOOP;
   
   dbms_output.put_line('-- Referenced by');
   dbms_output.put_line(LPAD('-', 10, '-'));  
   FOR rec IN (
      SELECT c.owner chi_owner, c.table_name chi_table_name, c.constraint_name chi_constraint_name
        , p.owner par_owner, p.table_name par_table_name, p.constraint_name par_constraint_name
      FROM dba_constraints c,
        dba_constraints p
      WHERE p.owner = l_owner
        AND p.table_name = l_table_name
        AND p.constraint_type = 'P'
        AND p.owner = c.r_owner
        AND p.constraint_name = c.r_constraint_name
   ) LOOP
      l_chi_cols := get_constraint_columns(rec.chi_owner, rec.chi_constraint_name);
      l_par_cols := get_constraint_columns(rec.par_owner, rec.par_constraint_name);
      dbms_output.new_line; 
      dbms_output.put_line('-- ' || rec.chi_owner || '.' || rec.chi_table_name || '.' || l_chi_cols || ' -> ' 
         || rec.par_owner || '.' || rec.par_table_name || '.' || l_par_cols);
      dbms_output.put_line(
'SELECT * ' || CHR(10) ||
'FROM ' || rec.chi_owner || '.' || rec.chi_table_name || ' c' || CHR(10) ||
'WHERE (' || l_chi_cols || ') IN (' || CHR(10) ||     
'  SELECT ' || l_par_cols || CHR(10) ||
'  FROM ' || rec.par_owner || '.' || rec.par_table_name || ' t' || CHR(10) ||
'  WHERE ' || l_table_condition || CHR(10) ||
');'    
); 
   END LOOP;   
END;
/

DB Link creation with direct insert

insert into sys.link$ (OWNER#, NAME, CTIME, HOST, USERID, PASSWORD, FLAG, AUTHUSR, AUTHPWD, PASSWORDX, AUTHPWDX)
SELECT user_id 
 ,'DB_LINK_NAME'
 ,SYSDATE 
 ,'CONNECTION_STRING',
 'TARGET_DATABASE_USERNAME',
 null, '2', null, NULL
 ,'hashed password can be extracted from sys.link$.PASSWORDX'
 , NULL
FROM dba_users
WHERE username = 'LINK_OWNER' -- DB link owner

Async using dbms_scheduler package

Simple async procession in Oracle can be implemented with dbms_scheduler package

-- dbms_scheduler.create_job makes explicit commit!!!
dbms_scheduler.create_job(  
         job_name      =>  'async_execution' || seq_async.nextval(),  
         job_type      =>  'PLSQL_BLOCK',  
         job_action    =>  'BEGIN some_asynk_proc(); END;',  
         start_date    =>  sysdate,  
         enabled       =>  TRUE,  
         auto_drop     =>  TRUE,  
         comments      =>  'Async procedure call' 
         );  

Competitive solution is implementation using dbms_aq queues, but as for me oracle’s queues from time to time start to raise unexpected exceptions. From other side for dbms_scheduler solution it is impossible to guarantee the order.

All privileges for all users

-- You can filter results in last lines of query
WITH all_user_roles AS (
     SELECT DISTINCT connect_by_root(grantee) username, granted_role, sys_connect_by_path(granted_role, '->') PATH, admin_option
     FROM dba_role_privs p
     START WITH grantee IN (SELECT username FROM dba_users)
     CONNECT BY PRIOR granted_role = grantee
  )
--SELECT * FROM all_user_roles;  
  , grantee AS (
    SELECT username , granted_role, PATH FROM all_user_roles
    UNION
    SELECT username, NULL granted_role, 'DIRECT' AS PATH FROM dba_users
  )
  --SELECT * FROM grantee;
  , priv_list AS (
    SELECT 'ROLE' priv_type, username, granted_role priv, NULL AS owner, NULL AS table_name, NULL AS column_name, admin_option grantable, PATH
    FROM all_user_roles
    UNION
    SELECT 'SYSTEM' priv_type, username, privilege priv, NULL AS owner, NULL AS table_name, NULL AS column_name, admin_option, PATH
    FROM dba_sys_privs, grantee
    WHERE grantee = grantee.username
    UNION
    SELECT 'TABLE' priv_type, username, PRIVILEGE, owner, table_name, NULL AS column_name, grantable, PATH
    FROM dba_tab_privs, grantee
    WHERE grantee = grantee.username
    UNION
    SELECT 'COLUMN' priv_type, username, PRIVILEGE, owner, table_name, column_name, grantable, PATH
    FROM dba_col_privs, grantee
    WHERE grantee = grantee.username)
SELECT * 
FROM priv_list
WHERE priv = 'ALTER USER'
; 

GIT housekeeping

Remove branches not present in remote script from stackoverflow:

git checkout master; git remote update origin --prune; git branch -vv | Select-String -Pattern ": gone]" | % { $_.toString().Trim().Split(" ")[0]} | % {git branch -d $_}

CONVERT for UTF-8

CONVERT function for clob input parameter in database with WE8MSWIN1252 character does not convert to UTF-8 - result is looking like UTF-16. Conversion to database character set works OK.
Test script

host chcp 65001

select CONVERT(CHR(246), 'AL32UTF8') from dual;
select CONVERT(TO_CLOB(CHR(246)), 'AL32UTF8') from dual;

select dump(to_char(CONVERT(TO_CLOB(CHR(246)), 'AL32UTF8'))) clob_convert, dump(CONVERT(CHR(246), 'AL32UTF8')) vc_convert 
from dual;
host chcp 1252 

select CONVERT(CHR(195) || CHR(182) , 'WE8MSWIN1252', 'AL32UTF8') from dual;
select CONVERT(TO_CLOB(CHR(195) || CHR(182)), 'WE8MSWIN1252', 'AL32UTF8') from dual;

Results

SQL> host chcp 65001
Active code page: 65001

SQL> select CONVERT(CHR(246), 'AL32UTF8') from dual;
ö
SQL> select CONVERT(TO_CLOB(CHR(246)), 'AL32UTF8') from dual;

SQL> select dump(to_char(CONVERT(TO_CLOB(CHR(246)), 'AL32UTF8', 'WE8MSWIN1252'))) clob_convert, dump(CONVERT(CHR(246), 'AL32UTF8')) vc_convert
  2  from dual;
Typ=1 Len=2: 0,246 <--- UTF_16????
Typ=1 Len=2: 195,182

SQL> host chcp 1252
Active code page: 1252

SQL> select CONVERT(CHR(195) || CHR(182) , 'WE8MSWIN1252', 'AL32UTF8') from dual;
ö

SQL> select CONVERT(TO_CLOB(CHR(195) || CHR(182)), 'WE8MSWIN1252', 'AL32UTF8') from dual;
ö

Quote from Oracle docs

Note:Oracle discourages the use of the CONVERT function in the current Oracle Database release. The return value of CONVERT has a character data type, so it should be either in the database character set or in the national character set, depending on the data type. Any dest_char_set that is not one of these two character sets is unsupported. The char argument and the source_char_set have the same requirements. Therefore, the only practical use of the function is to correct data that has been stored in a wrong character set.
Values that are in neither the database nor the national character set should be processed and stored as RAW or BLOB. Procedures in the PL/SQL packages UTL_RAW and UTL_I18N—for example, UTL_RAW.CONVERT—allow limited processing of such values. Procedures accepting a RAW argument in the packages UTL_FILE, UTL_TCP, UTL_HTTP, and UTL_SMTP can be used to output the processed data.

Jenkins: print script executed by sh

Put inside the script.

1. Print to trace

find /var/jenkins_home/jobs/My_Job/workspace@tmp/ \\
  -name 'script.sh' \\
  -exec cat '{}' \\;

2. Or copy whole folder

cp -r /var/jenkins_home/jobs/My_Job/workspace@tmp/ ~/tmp

Put the correct root folder in the script

Character set conversion

Based on this

db1>SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';

VALUE
----------------------------------------------------------------
WE8MSWIN1252

To return in the national character set of the database use UNISTR function:

db1>SELECT dump(UNISTR('\00E4'), 1016), UNISTR('\00E4') FROM DUAL;

DUMP(UNISTR('\00E4'),1016)               U
---------------------------------------- -
Typ=1 Len=2 CharacterSet=AL16UTF16: 0,e4 ä

To return in the database character set use TO_CHAR:

db1>SELECT DUMP(TO_CHAR(UNISTR('\00E4')), 1016), TO_CHAR(UNISTR('\00E4')) FROM DUAL;

DUMP(TO_CHAR(UNISTR('\00E4')),1016)       T
----------------------------------------- -
Typ=1 Len=1 CharacterSet=WE8MSWIN1252: e4 ä

If characters are not shown correctly in SQL*Plus use chcp and NLS_LANG as explained here

db1>host chcp
Active code page: 1252

db1>host echo %NLS_LANG%
.WE8MSWIN1252

Jenkins: fire on change of specific file in specific branch in Bitbucket

Perhaps different set of plugins can execute this task, but I’ve implemented with Generic Webhook trigger.

Final version is for Bitbucket’s Pull request merged webhook, but it also may be adopted to Repository push Webhook

In the Bitbucket webhook is configured that calls Jenkins.

In Jenkins build trigger is Generic Webhook Trigger.
Post content parameters

Variable	Expression	Comment
merge_commit_hash	$.pullRequest.properties.mergeCommit.id	For pull request merge
pull_request_branch	$.pullRequest.toRef.id	For pull request merge
commit_hash	$.changes[0].toHash	For repository push
push_branch	$.changes[0].refId	For repository push

Filter in Jenkins’ Generic Webhook Trigger to process only changes in specific branch:

Expression	Text
refs/heads/dev	$push_branch$pull_request_branch

Example of simple Execute shell step :

#!/bin/bash

echo "Looking for $FILE_NAME in Commit $commit_hash $merge_commit_hash"
# list of changed files for a commit
#filelist=$(git diff-tree --no-commit-id --name-only -r $commit_hash)
#commit is merge commit - must use different tactics
filelist=$(git log -m -1 --name-only --pretty="format:" $merge_commit_hash)
if [[ $filelist == *"$FILE_NAME"* ]]; then
$ process file
else
	echo "Skip"
fi

Find all utl_ packages in source code

Using grep utility

grep -rohP "(^|\W)\Kutl_\w*\." . | sort -u

Database CI pipeline idea

1. Prod release
2. Remote online clone of Prod database using this technic. See the link above to the list of restrictions, but most influenceable are: source database must be in archivelog mode and have local undo (otherwise it should be in read-only mode), same endianness in source and target. The database link should exists from target to source.
3. Keep created clone as golden source. Use PDB snapshot copy to create databases for testing
   – parameter clonedb=true should be set
   – filesystem should supports sparse files (ext4, NTFS, … are ok)
   – as spare files are used, whole process is very fast and don’t consumes a lot of space

UTL_FILE_DIR AND ORA-29280

utl_file_dir parameter was deprecated at least in 12c and completely removed at least in 19c.

For UTL_FILE procedures like fopen there are 2 different ways to specify directory:

1. Using Oracle DIRECTORY object
2. Using string with full path. This approach work if and only if the specified directory is present in the parameter UTL_FILE_DIR, otherwise it raises ORA-29280 (see example below). This way is not working in 19c as UTL_FILE_DIR does not exists anymore
   The second approach is work regardless UTL_FILE_DIR and this is the only way in Oracle 19c

SELECT NAME, VALUE FROM v$parameter WHERE NAME = 'utl_file_dir';
NAME            VALUE
utl_file_dir    c:\temp\utl_file_dir

======================================================
Using folder name with full path.
UTL_FILE_DIR does NOT contain folder - NOT WORKING!!!
======================================================
DECLARE
   v_fp           sys.utl_file.file_type;
BEGIN
   v_fp := sys.utl_file.fopen('c:\temp\non_utl_file_dir', 'test_file.csv', 'w', 32000);
   utl_file.fclose(v_fp);
END;
/
DECLARE
   v_fp           sys.utl_file.file_type;
BEGIN
   v_fp := sys.utl_file.fopen('c:\temp\non_utl_file_dir', 'test_file.csv', 'w', 32000);
   utl_file.fclose(v_fp);
END;
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 4

======================================================
USING DIRECTORY OBJECT - WORKING
======================================================
CREATE OR REPLACE directory REPORT_DIRECTORY AS 'c:\temp\non_utl_file_dir';
Directory created
DECLARE
   v_fp           sys.utl_file.file_type;
BEGIN
   v_fp := sys.utl_file.fopen('REPORT_DIRECTORY', 'test_file.csv', 'w',32000);
   utl_file.fclose(v_fp);
END;
/
PL/SQL procedure successfully completed

======================================================
Using folder name with full path.
UTL_FILE_DIR contains folder - WORKING
======================================================
DECLARE
   v_fp           sys.utl_file.file_type;
BEGIN
   v_fp := sys.utl_file.fopen('c:\temp\utl_file_dir', 'test_file.csv', 'w',32000);
   utl_file.fclose(v_fp);
END;
/
PL/SQL procedure successfully completed

Set ssh private key folder in GIT

Set variable HOME to the folder where .ssh folder with keys is created and windows GIT will pick up private key from this folder:

set HOME=x:\

Test connectivity on Linux

Using curl

curl -v telnet://host:port

Using netcat

nc -z -v host port

cURL file send to Oracle cloud

curl --write-out '%{time_total}' -X POST --data-binary "@5M.csv" -H "Content-Type:text/csv" --user <user>:<password> "https://....adb.us-ashburn-1.oraclecloudapps.com/ords/<user>/huge_csv/batchload?batchRows=5000&errorsMax=20"

Taken from here

USE_SHARED_SOCKET

For old versions as pointed in the note Troubleshooting Connectivity Issues From a Remote Client to a Database (Doc ID 271852.1)

For 9.2 and below, if the Operating System is Microsoft Windows NT,
and you are using the dedicated server connection, a spawned Oracle
thread communicates with the client through a randomly allocated port
(called a REDIRECT). To force the oracle process to communicate only
through the port in which Listener is listening, set the key,
USE_SHARED_SOCKET = TRUE, in the Windows Registry
NOTE:
This is no longer the case with 10g and up as a REDIRECT does not take place for DEDICATED connections.

Или, если верить ноте 124140.1, начиная с версии 10.2

This technique only involves the version 8.1 to 10.1 Oracle Database versions. From 10.2 onward the default changed to allow port sharing.

Причина этого

Microsoft WINSOCK V1.1 API did not allow one process to pass a TCP socket to another process and as a result did not allow port sharing like on UNIX systems

Знание уже абсолютно бесполезное, ибо:

1. С версии 10.2 USE_SHARED_SOCKET=TRUE в реестре или переменной окружения
2. Уже 100 лет не видел Oracle на Windows за исключением собственного ноута