Script to update batch number for every 20 records of even numbers

 Hi All,

We recently had a request from user to group then item and split the shipment for every 20 quantities.

Below is a POC of how to create the shipment (Batch) based on type of item (Odd/even).

Let me insert 1 to 100 number in a table and create a unique batch number only for even numbers where every batch has 20 numbers.

-- Table Creation
CREATE TABLE batch_test
(
    id            NUMBER
   ,batch_name    VARCHAR2 (20)
);


-- Insert records into table
BEGIN
    FOR i IN 1 .. 100
    LOOP
        INSERT INTO batch_test (id)
             VALUES (i);
    END LOOP;

    COMMIT;
END;
/

-- Script to create Batch number

SET SERVEROUTPUT ON;

DECLARE
    v_count      NUMBER;
    loop_count   NUMBER := 0;
    v_limit NUMBER:=20; 
BEGIN
    SELECT COUNT (*)
      INTO v_count
      FROM batch_test
     WHERE MOD (id, 2) = 0
       AND batch_name IS NULL;

    DBMS_OUTPUT.put_line ('Even count ' || v_count);

    WHILE v_count > v_limit
    LOOP
        loop_count   := loop_count + 1;

        UPDATE batch_test
           SET batch_name   = TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS') || loop_count
         WHERE MOD (id, 2) = 0
           AND batch_name IS NULL
           AND ROWNUM <= v_limit;

        v_count      := v_count - v_limit;
    END LOOP;

    IF v_count > 0
    THEN
        loop_count   := loop_count + 1;

        UPDATE batch_test
           SET batch_name   = TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS') || loop_count
         WHERE MOD (id, 2) = 0
           AND batch_name IS NULL
           AND ROWNUM <= v_limit;
    END IF;
    COMMIT;
END;
/

Hide password getting printed in log during batch file execution for code migration

 Hi,

To avoid the password getting printed in the log during code migrations using batch file, we need to mask the password when DBA enters the password during file execution. Below is a sample code which helps to achieve the requirement. 

Note : To ensure whether the received password is correct I have reprinted in the script which you can comment.

@echo off
setlocal enabledelayedexpansion

echo Enter Password:
:: Call PowerShell to securely prompt, mask characters, and return plain text
for /f "delims=" %%A in ('powershell -Command "$secret = Read-Host -AsSecureString; $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($secret); [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR)"') do (
    set "PASSWORD=%%A"
)

echo Password: !PASSWORD!

pause