Tuesday, October 21, 2025

Microsoft Excel - Modifying an excel worksheet

Hiding and Unhiding Excel Rows and Columns

Hiding Rows/Columns: Select them → Right-click → Hide.
Unhiding Rows/Columns: Select nearby rows/columns → Right-click → Unhide.



Renaming an excel sheet

  1. Double-click the sheet tab → Type new name → Press Enter.
  2. Right-click the sheet tab → Select Rename → Type new name → Press Enter.

Note: Similarly we can delete the sheet.

Changing the Width and Height of Cells

Drag row/column border or Right-click → Row Height / Column Width → Enter value.

Moving and Copying an Excel Worksheet

Move: Drag sheet tab to new spot.
Copy: Hold Ctrl and drag sheet tab.


Move or copy an excel sheet from one to another workbook

Right-click sheet tab → Move or Copy → Choose workbook → Check Create a copy (if needed) → Click OK.





Inserting and Deleting Rows and Columns

Insert:

  • Use Ctrl + + (plus).
  • Or Right-click → Insert.

Delete:

  • Use Ctrl + – (minus).
  • Or Right-click → Delete.

Tip: Ctrl + Shift + + inserts entire row/column.


Monday, October 20, 2025

Microsoft Excel - working with basic Excel Functions

Excel Function

A function in Excel is a predefined formula that performs a specific calculation using the values provided to it.
Functions help simplify complex calculations and save time.


Building Block of an Excel Function

Each Excel function has three main parts:

  1. Equal Sign (=) – Indicates that a formula or function is being entered.
  2. Function Name – The name of the specific operation (e.g., SUM, AVERAGE, MAX).
  3. Arguments – The values or cell references enclosed in parentheses that the function uses to perform calculations.

📘 Syntax:
=FunctionName(Arguments)

📊 Example:
=SUM(B4:B8) → Adds all the values from cells B4 to B8.


Function Argument Window 

The Function Argument Window is a dialog box in Excel that helps users enter and understand the arguments required for a function.
It appears when you click the “fx” button next to the formula bar or start typing a function.

This window displays:

  • The name of the function and a short description of what it does.
  • Input fields for each argument, showing whether they are required or optional.
  • A preview or result section at the bottom that shows the outcome of the calculation as you enter the data.

💡 It is especially helpful for beginners to correctly structure functions without remembering the exact syntax.

🧭 Formula Tab – Function Library

The Formula Tab in Excel is used to create, edit, and manage formulas and functions easily.
Within this tab, the Function Library group provides a quick way to insert various built-in Excel functions categorized by type.

It helps users find and apply formulas without remembering their exact names or syntax.


🗂️ Main Sections in the Function Library

1. AutoSum

  • Automatically adds a range of numbers.
  • Click the AutoSum (Σ) button to quickly insert functions like:
    • SUM – Adds numbers.
    • AVERAGE – Finds the average.
    • COUNT – Counts numeric cells.
    • MAX – Finds the highest value.
    • MIN – Finds the lowest value.

📘 Shortcut: Select a range and press Alt + = to insert SUM automatically.

2. Recently Used

  • Shows a list of functions you have used recently.
  • Makes it easy to reuse functions without searching again.

3. Financial

  • Contains functions used for financial calculations.
    • Examples: PMT, FV, PV, RATE, NPV.

4. Logical

  • Includes functions used for decision-making and condition checking.
    • Examples: IF, AND, OR, NOT.

5. Text

  • Functions for handling text and strings.
    • Examples: LEFT, RIGHT, MID, LEN, UPPER, LOWER, CONCAT.
6. Date & Time
  • Used to insert and calculate date and time values.
    • Examples: TODAY, NOW, DAY, MONTH, YEAR, DATEDIF.

7. Lookup & Reference

  • Functions used to search for and retrieve data from tables or ranges.
    • Examples: VLOOKUP, HLOOKUP, INDEX, MATCH, CHOOSE.

8. Math & Trig

  • Contains mathematical and trigonometric functions.
    • Examples: SUM, ROUND, PRODUCT, POWER, SQRT, ABS.

9. More Functions

  • Includes specialized function categories:
    • Statistical: AVERAGEIF, COUNTIF, MEDIAN, MODE
    • Engineering: CONVERT, COMPLEX
    • Information: ISERROR, ISBLANK
    • Compatibility: Older Excel functions for backward compatibility.

💡 Extra Option: Insert Function (fx)

  • Located at the left of the formula bar and also in the Function Library.
  • Opens the Function Arguments window, helping users insert functions step-by-step.
  • Provides descriptions, required arguments, and a preview of results.

🧮 How to Use a Function from the Formula Tab (Example: SUM Function)

Step 1: Open your Excel sheet

Make sure the worksheet has some numeric data (for example, numbers in cells B2 to B6).

Step 2: Select the cell for the result

Click on the cell where you want the total (for example, B7).

Step 3: Go to the Formula Tab

On the Excel ribbon, click the Formulas tab.
You’ll see several groups such as Function Library, Defined Names, Formula Auditing, etc.

Step 4: Choose Math & Trigonometry

In the Function Library group, click on Math & Trig.
A dropdown list of math-related functions will appear.

Step 5: Select the SUM function

Scroll and click on SUM from the list.
The Function Arguments window will open.

Step 6: Enter the cell range

In the Function Arguments window:

  • In the Number1 box, enter the range of cells you want to add (for example, B2:B6).
  • You can also select the range directly on your worksheet using the mouse.

Step 7: Preview the result

Excel will instantly show the calculated result at the bottom of the Function Arguments window, so you can confirm it’s correct.

Step 8: Click OK

After confirming the range and result, click OK.
The total sum will appear in the selected cell (B7).


📘 Final Formula Example

=SUM(B2:B6)

This adds all numbers from cells B2 to B6 and displays the total in B7.



🔎 Using fx (Insert Function) Option

  1. Click the fx button beside the formula bar.
  2. In the Insert Function box, type MIN and press Go.
  3. Select MIN from the list → click OK.
  4. Enter the cell range (e.g., B2:B6).
  5. Click OK → the smallest value appears in the selected cell.

📘 Example: =MIN(B2:B6)



We can also use MAX(), AVERAGE() AND COUNT() etc. functions.

Adjacent cells error in excel calculation



Using AutoSum Command

  1. Select the cell for the result.
  2. Click AutoSum (Σ) on the Home or Formulas tab.
  3. Excel auto-selects nearby cells — press Enter to confirm.

📘 Example: =SUM(B2:B6)


Note: we can also use shortcut key: Alt + = to perform autosum.

🔁 Using AutoFill to Copy Formulas

  1. Select the cell with the formula.
  2. Drag the fill handle (small square at the corner) across cells.
  3. Excel copies and adjusts the formula automatically.

Sunday, October 19, 2025

Microsoft Excel - Working with cell references + create basic formula + Order of Operation

📘 In this section, we will learn about:

  1. What is a Cell Reference – Understanding how Excel identifies each cell using column letters and row numbers.
  2. Relative vs Absolute Cell References – Knowing the difference between cell references that change when copied and those that stay fixed.
  3. Creating a Basic Formula – How to write simple formulas like addition, subtraction, multiplication, or division.
  4. Order of Operations (Order of Precedence) – The sequence Excel follows to calculate formulas correctly using the PEMDAS/BODMAS rule.

🔹 What is a Cell Reference?

A cell reference tells Excel which cell’s value to use in a formula. A cell reference is represented with the vertical column name and horizontal row number.
Example:
If cell A1 = 10, and in B1 you type

= A1 + 5

Excel adds the value from A1 (10) + 5 → Result = 15

Here, A1 is the cell reference.


🔹 Types of Cell References

1. Relative Cell Reference

👉 Example: =A1 + B1

  • Changes automatically when you copy the formula to another cell.
  • Excel adjusts the reference based on the new position.

Example:

Cell Formula Result
C1 =A1 + B1 Adds A1 & B1
C2 (copied down) =A2 + B2 Adds A2 & B2

Use when you want formulas to adjust for each row or column.


2. Absolute Cell Reference

👉 Example: =$A$1 + B1

  • The $ locks the reference — it won’t change when copied.

Meaning of $ signs:

  • $A$1 → Lock column A and row 1
  • A$1 → Lock only row 1
  • $A1 → Lock only column A

Example: If A1 = 10, and in B1 you type:

= $A$1 + C1

Then copy to B2 →
It becomes:

= $A$1 + C2

A1 stays fixed, only C1 changes to C2.


3. Mixed Cell Reference

As above ($A1 or A$1) — part fixed, part relative.


🔹 Using in a Basic Function

Let’s say:

A B
10 2
20 3
30 4

If you want to multiply column A by a fixed number in D1 (say D1 = 5):

In C1, type:

= A1 * $D$1

Then drag down —
Result:

A B C (A × D1)
10 2 50
20 3 100
30 4 150

Here $D$1 ensures the multiplier stays fixed.


How to create a basic formula in Excel:

✅ Steps to Create a Basic Formula

  1. Click on the cell where you want the result.
  2. Type an equals sign (=).
  3. Type your numbers or cell references with the math operator (like + for addition).
  4. Press Enter.

💡 Example 1 – Add Numbers Directly

=10 + 5

👉 Result: 15

💡 Example 2 – Add Values from Cells

If cell A1 = 10 and B1 = 5,
type this in C1:

=A1 + B1

👉 Result in C1: 15

TipYou can also use the SUM function:

=SUM(A1, B1)

or for a range:

=SUM(A1:A5)

👉 Adds all numbers from A1 to A5.

Order of operations in excel:

In Excel, formulas follow the order of operations just like in math — based on PEMDAS (or BODMAS) rules.


🔢 Order of Operations in Excel

Step Operation Type Example Description
1️⃣ Parentheses / Brackets (P or B) (2 + 3) * 4 Excel first evaluates anything inside parentheses () or brackets [].
2️⃣ Exponents / Orders (E or O) 2^3 Then Excel evaluates powers (like ^ means “to the power of”).
3️⃣ Multiplication & Division (MD) 6 / 2 * 3 Next, Excel does multiplication and division from left to right.
4️⃣ Addition & Subtraction (AS) 10 - 4 + 2 Finally, Excel performs addition and subtraction from left to right.

🧠 PEMDAS = Parentheses, Exponents, Multiplication, Division, Addition, Subtraction

🧮 BODMAS = Brackets, Orders, Division, Multiplication, Addition, Subtraction


Example

Formula:

=10 + 2 * (6 / 3) ^ 2

Step-by-step:

  1. Parentheses → (6 / 3) = 2
  2. Exponents → 2 ^ 2 = 4
  3. Multiplication → 2 * 4 = 8
  4. Addition → 10 + 8 = 18

So Excel shows 18.




Saturday, October 18, 2025

Microsoft Excel - Fundamentals

Launching excel:

windows task bar >> start >> Search Excel >> Open 


When you open Excel, you'll see a start screen displaying a blank workbook, various templates to choose from, as well as options for account settings and recently opened workbooks. Click Blank Workbook to begin.

Basic Control and Parts of Excel Window 

1. **Quick Access Toolbar**: Located at the top left, this shows frequently used commands and can be customized.

2. **File Tab**: Replaces the Office button, allowing file operations like opening, saving, and printing.

3. **Title Bar**: Displays the name of the current document at the top of the window.

4. **Control Buttons**: Located at the top-right corner, these buttons help you minimize, maximize, or close the window.

5. **Menu Bar**: Contains tabs like File, Insert, and Data for accessing different commands.

6. **Ribbon/Toolbar**: Shows commands organized by tabs (e.g., Home, Insert) and groups within them (e.g., fonts, alignment).

7. **Dialog Box Launcher**: A small arrow in the lower-right of a command group that provides more options.

8. **Name Box**: Shows the location of the active cell or range.

9. **Formula Bar**: Allows you to view and edit the content or formula of the active cell.

10. **Scrollbars**: Used to move around the document vertically and horizontally.

11. **Spreadsheet Area**: Where you enter data, including rows, columns, and cells.

12. **Sheet Tab**: Located at the bottom, it shows the current sheet and lets you switch between sheets.

13. **Columns Bar**: Displays column labels (A, B, C, etc.) and is located below the formula bar.

14. **Rows Bar**: Displays row numbers (1, 2, 3, etc.) on the left side of the sheet.

15. **Cells**: Individual boxes in the spreadsheet, identified by their column letter and row number (e.g., A1).

16. **Status Bar**: At the bottom, shows summary info and lets you customize what’s displayed.

17. **View Buttons**: Three buttons for different views: Normal, Page Layout, and Page Break.

18. **Zoom Control**: Located at the bottom-right, lets you zoom in and out of the spreadsheet


Hide Excel Ribbon

Double clink on any selected tab to hide/unhide

The middle blank area is called a workbook, which contains multiple worksheets. A worksheet is where you enter and organize your data.

  • Total columns: XFD (16,384)
  • Total rows: 1,048,576
  • To quickly navigate:
    • Ctrl + → / ↓ → Move to the last column or row
    • Ctrl + Home → Return to cell A1

To save a Excel:

Quick Access Toolbar:

  • Use the Save button or press Ctrl + S to save your work quickly.

File Tab Options:

  • Save / Save As:
    • Choose Save As when creating a new file for the first time.
    • Use Save to update an existing workbook.
  • Open:
    • Access previously saved Excel files or browse to open a new one.

Shorcuts commands: 

Following are the most frequently used shortcuts in Excel for Windows. 


🔹 Basic File Operations

  • Ctrl + S : Save worksheet
  • Ctrl + O : Open worksheet
  • Ctrl + W : Close worksheet
  • Alt + F → Save / Save As : Save new or existing file
  • Ctrl + P : Print worksheet

🔹 Navigation & Tabs

  • Alt + H : Go to Home tab
  • Alt + N : Go to Insert tab
  • Alt + P : Go to Page Layout tab
  • Alt + M : Go to Formula tab
  • Alt + A : Go to Data tab
  • Alt + W : Go to View tab

🔹 Editing & Formatting

  • Ctrl + C : Copy
  • Ctrl + X : Cut
  • Ctrl + V : Paste
  • Ctrl + Z : Undo
  • Delete : Clear cell contents
  • Ctrl + B : Bold text
  • Alt + H, H : Choose fill color
  • Alt + H, A, C : Center align contents
  • Alt + H, B : Add borders
  • Alt + H, D, C : Delete column

🔹 Row & Column Management

  • Ctrl + 9 : Hide selected rows
  • Ctrl + 0 : Hide selected columns

🔹 Cell Navigation

  • Shift + Tab : Move to previous cell
  • Up Arrow : Move one cell up
  • Down Arrow : Move one cell down
  • Left Arrow : Move one cell left
  • Right Arrow : Move one cell right
  • Ctrl + Arrow key : Jump to edge of data region
  • End + Arrow key : Move to next nonblank cell
  • Ctrl + End : Go to last used cell
  • Ctrl + Shift + End : Extend selection to last used cell
  • Home + Scroll Lock : Move to upper-left corner
  • Ctrl + Home : Go to beginning (cell A1)

🔹 Worksheet Navigation

  • Page Down : Move one screen down
  • Page Up : Move one screen up
  • Alt + Page Down : Move one screen right
  • Alt + Page Up : Move one screen left
  • Ctrl + Page Down : Next sheet
  • Ctrl + Page Up : Previous sheet
  • Tab : Move right / next unlocked cell

🔹 Miscellaneous

  • Shift + F10 or Context Key : Open context menu

Excel extension:

Before 2007, Excel used the .xls binary file format; from 2007 onward, it uses the XML-based .xlsx format.

The maximum number of worksheets in a Microsoft Excel workbook is not explicitly fixed — it is limited only by your computer’s available memory. 

Data types:

  1. By deafult , text are left aligned
  2. By default, numbers right aligned.
  3. Date values are treated as numeric . For example if you mention Jan-2019 the backend value will be 1/1/2019(m/d/yyyy)

From the Home tab >>Number group and we can choose different date format or we can create custom one.




Friday, September 26, 2025

OIC - How to Encrypt and Decrypt Using AES Key and OCI Function in Oracle Integration Cloud (OIC)

 Working...

📌 Use Case

In real-world Oracle Integration Cloud (OIC) projects, sensitive data like passwords, API keys, or personal information must be transmitted securely. A common approach is to encrypt data before sending it to a target system and decrypt it upon retrieval.

By leveraging AES encryption/decryption inside an OCI Function and invoking it from OIC, we can:

  1. Securely encrypt payloads before sending to external applications.
  2. Decrypt incoming encrypted data from third-party systems.
  3. Ensure compliance with data security requirements (AES/CBC/PKCS5Padding or AES/ECB/PKCS5Padding).
This approach allows OIC to delegate cryptographic operations to OCI Functions, ensuring flexibility and security without exposing raw secrets in integration flows.

Solution Steps

1. Create an OCI Function for AES Encryption/Decryption

Develop a Java OCI Function (see full code below).

Function accepts input parameters:

  • message → Text to encrypt or decrypt.
  • secretKeyBase64 → AES key (Base64 encoded).
  • ivBase64 → Initialization Vector (required for CBC mode).
  • aesMode → Cipher mode (e.g., AES/CBC/PKCS5Padding or AES/ECB/PKCS5Padding).
  • actionType → Either ENCRYPT or DECRYPT.

2. Deploy the Function in OCI

Use Fn Project CLI to deploy.

Make sure the function has appropriate IAM permissions to be invoked from OIC.

3. Invoke OCI Function from OIC

  • In OIC, create a REST Adapter to call the OCI Function.
  • Pass input parameters (message, aesMode, etc.) in the request body.
  • Receive encrypted/decrypted message in the response payload.

4. Use in Integration Flows

Encrypt sensitive data (like API credentials) before storing or transmitting.

Decrypt incoming messages before business logic processing.

📝 Function Code

Here’s the Java OCI Function code you can deploy:

package com.clp.fn;

import javax.crypto.Cipher;

import javax.crypto.spec.IvParameterSpec;

import javax.crypto.spec.SecretKeySpec;

import java.util.Base64;

import java.util.logging.*;

import java.security.SecureRandom;

public class AESEncryptDecrypt {

    private static final Logger logger = Logger.getLogger(AESEncryptDecrypt.class.getName());

    public static class Input {

        public String message;

        public String secretKeyBase64;

        public String ivBase64;

        public String aesMode;     // AES/CBC/PKCS5Padding or AES/ECB/PKCS5Padding

        public String actionType;  // ENCRYPT or DECRYPT

    }

    public static class Result {

        public String message;

        public String salt;

        public String wechataeskey;

        public String executionInfo;

    }

    public Result handleRequest(Input input) {

        logger.log(Level.INFO, "OIC - message:", input.message);

        logger.log(Level.INFO, "OIC - secretKeyBase64:", input.secretKeyBase64);

        logger.log(Level.INFO, "OIC - ivBase64", input.ivBase64);

        logger.log(Level.INFO, "OIC - aesMode:", input.aesMode);

        logger.log(Level.INFO, "OIC - actionType:", input.actionType);

        Result result = null;

        if ("DECRYPT".equals(input.actionType)) {

            result = decryptMyMessage(input);

        } else if ("ENCRYPT".equals(input.actionType)) {

            result = encryptMyMessage(input);

        } else {

            result = new Result();

            result.executionInfo = "ERROR: No proper action found , " +

                    "possible value is ENCRYPT or DECRYPT , recieved value:" + input.actionType;

        }

        return result;

    }

    // Generate random 16-byte IV for AES/CBC

    public static IvParameterSpec generateIV() {

        byte[] iv = new byte[16]; // 128-bit IV

        new SecureRandom().nextBytes(iv);

        IvParameterSpec ivParameterSpec = new IvParameterSpec(iv);

        return ivParameterSpec;

    }

    public Result encryptMyMessage(Input input) {

        Result result = new Result();

        try {

            byte[] decodedKey = null;

            if (input.secretKeyBase64 == null) {

                // Generate 16-digit random numeric string

                String keyString = generateRandomDigits(16);

                //System.out.println("Generated 16-digit AES key: " + keyString);

                // Convert to byte array (each digit becomes 1 byte, 16 bytes total = 128 bits)

                result.wechataeskey = keyString;

                decodedKey = keyString.getBytes("UTF-8");

            } else {

                // Decode the base64 encoded string

                decodedKey = Base64.getDecoder().decode(input.secretKeyBase64);

            }

byte[] messageBytes = input.message.getBytes("UTF-8");

        // Create a SecretKeySpec for the AES key

        SecretKeySpec secretKeySpec = new SecretKeySpec(decodedKey, "AES");

        // Create a Cipher instance for AES

        Cipher cipher = Cipher.getInstance(input.aesMode);

        if (input.aesMode.contains("CBC")) {

            // AES/CBC/PKCS5Padding required IV

            IvParameterSpec ivParameterSpec = generateIV();

            cipher.init(Cipher.ENCRYPT_MODE, secretKeySpec, ivParameterSpec);

            result.salt = Base64.getEncoder().encodeToString(ivParameterSpec.getIV());

        } else {

            // AES/ECB/PKCS5Padding do not require IV

            cipher.init(Cipher.ENCRYPT_MODE, secretKeySpec);

        }


        // Decrypt the message

        byte[] originalBytes = cipher.doFinal(messageBytes);

        String encodedString = Base64.getEncoder().encodeToString(originalBytes);

        result.message = encodedString;

        // String originalMessage = new String(originalBytes);

        result.executionInfo = "SUCCESS";

    } catch (Exception e) {

        result.executionInfo = e.getMessage();

        logger.log(Level.INFO, "Error Details:", e.getMessage());

    }

    return result;

}

public Result decryptMyMessage(Input input) {

    Result result = new Result();

    generateIV();

    try {

        // Decode the base64 encoded string

        byte[] decodedKey = Base64.getDecoder().decode(input.secretKeyBase64);

        byte[] encryptedBytes = Base64.getDecoder().decode(input.message);

        // Create a SecretKeySpec for the AES key

        SecretKeySpec secretKeySpec = new SecretKeySpec(decodedKey, "AES");

        // Create a Cipher instance for AES

        Cipher cipher = Cipher.getInstance(input.aesMode);

        if (input.aesMode.contains("CBC")) {

            // AES/CBC/PKCS5Padding required IV

            byte[] decodedIV = Base64.getDecoder().decode(input.ivBase64);

            IvParameterSpec ivParameterSpec = new IvParameterSpec(decodedIV);

            cipher.init(Cipher.DECRYPT_MODE, secretKeySpec, ivParameterSpec);

        } else {

            // AES/ECB/PKCS5Padding do not require IV

            cipher.init(Cipher.DECRYPT_MODE, secretKeySpec);

        }

        // Decrypt the message

        byte[] originalBytes = cipher.doFinal(encryptedBytes);

        String encodedString = Base64.getEncoder().encodeToString(originalBytes);

        result.message = encodedString;

        // String originalMessage = new String(originalBytes);

        result.executionInfo = "SUCCESS";

    } catch (Exception e) {

        result.executionInfo = e.getMessage();

        logger.log(Level.INFO, "Error Details:", e.getMessage());

    }

    return result;

}

// WeChat, generate a random string of digits for AES Key

public static String generateRandomDigits(int length) {

    SecureRandom random = new SecureRandom();

    StringBuilder sb = new StringBuilder(length);

    for (int i = 0; i < length; i++) {

        sb.append(random.nextInt(10)); //0-9

    }

    return sb.toString();

}

}

Code link:

https://drive.google.com/file/d/1pF3DGnyAr0M2F-HymQE-jJbC3kj-VBzi/view?usp=drivesdk

Key Benefits

  • Ensures data confidentiality across systems.
  • Supports multiple AES modes (CBC, ECB) with PKCS5Padding.
  • Can be reused across multiple OIC integrations via a single OCI Function.
  • No direct exposure of keys inside OIC flows


OIC - How to Use XSLT to Generate Namespaced Target Payload Blocks in OIC

Use Case

When working with Oracle Integration Cloud (OIC) SOAP-based integrations, we often need to map fault objects (like error details or reasons) into a target response payload that follows a specific namespace and schema.

In this example, the target system expects a payload with the following structure:

<ns2:AddMeterToInventoryResponse xmlns:ns2="turtletech.com/TS2/">
   <ns2:AddMeterToInventoryResult>
      <ns2:ErrorObj>
         <ns2:errorString>Meter Not Added</ns2:errorString>
      </ns2:ErrorObj>
   </ns2:AddMeterToInventoryResult>
</ns2:AddMeterToInventoryResponse>

The challenge is to generate this block dynamically from fault objects (like $GlobalFaultObject/nspr1:fault) while preserving the correct namespace (ns2) and nested structure.


Solution with XSLT Mapper

To achieve this in OIC mapper, we can leverage <xsl:apply-templates> and define templates that build the required XML block.


1. Declare the Namespace

In your stylesheet, ensure that the target namespace (ns2) is declared:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:ns2="turtletech.com/TS2/"
                xmlns:nspr1="http://schemas.oracle.com/faults"
                exclude-result-prefixes="nspr1"
                version="1.0">

2. Entry Template

Start with a root template that applies templates for faults:

<xsl:template match="/">
   <nstrgmpr:InboundSOAPResponseDocument>
      <nstrgmpr:Body>
         <xsl:apply-templates select="$GlobalFaultObject/nspr1:fault"/>
      </nstrgmpr:Body>
   </nstrgmpr:InboundSOAPResponseDocument>
</xsl:template>

3. Fault Handling Template

Define how the fault is transformed into the required target structure:

<xsl:template match="nspr1:fault">
   <ns2:AddMeterToInventoryResponse>
      <ns2:AddMeterToInventoryResult>
         <xsl:choose>
            <xsl:when test="$GlobalFaultObject/nspr1:fault/nspr1:details = ''">
               <ns2:ErrorObj>
                  <ns2:errorString>
                     <xsl:value-of select="$GlobalFaultObject/nspr1:fault/nspr1:reason"/>
                  </ns2:errorString>
               </ns2:ErrorObj>
            </xsl:when>
            <xsl:otherwise>
               <ns2:ErrorObj>
                  <ns2:errorString>
                     <xsl:value-of select="$GlobalFaultObject/nspr1:fault/nspr1:details"/>
                  </ns2:errorString>
               </ns2:ErrorObj>
            </xsl:otherwise>
         </xsl:choose>
      </ns2:AddMeterToInventoryResult>
   </ns2:AddMeterToInventoryResponse>
</xsl:template>


4. Execution

With this template setup:

  • OIC picks up the fault object.
  • The <xsl:apply-templates> dispatches it to the fault template.
  • The correct namespace-prefixed block (ns2) is generated.

This ensures your output matches the required schema exactly and avoids namespace mismatches.


Key Takeaways

  • Always declare the target namespace (ns2) in the XSLT stylesheet.
  • Use <xsl:apply-templates> and <xsl:template match> to modularize transformation logic.
  • Use <xsl:choose> to conditionally pick reason or details from fault objects.
  • This approach guarantees a reusable and scalable mapping for multiple fault scenarios.

👉 This method is highly reusable for any SOAP service response transformation in OIC where a specific namespace-aligned block must be built dynamically.



Wednesday, September 24, 2025

OIC - How to Generate RSA Private Key for Oracle OIC Vault, Functions, and Connections Using ssh-keygen (Windows CMD)

📌 Use Case

When working with Oracle Integration Cloud (OIC), secure authentication often requires RSA private keys in PEM format. These keys are commonly used for:

  • Uploading secrets into OCI Vault.
  • Configuring OIC Functions with key-based authentication.
  • Creating SFTP, REST, or API Connections in OIC that use private keys instead of passwords.

If you are on Windows, you can use the built-in OpenSSH ssh-keygen tool (available in Windows 10/11 CMD or PowerShell) to generate or reformat RSA private keys.


⚙️ Solution Steps (Windows CMD)

Step 1: Ensure OpenSSH is Installed

  • Open Command Prompt.
  • Run:
    ssh -V
    
    If you see a version (e.g., OpenSSH_for_Windows_8.x), you’re good to go.
    If not, install OpenSSH from Windows Optional Features.

Step 2: Run the ssh-keygen Command in CMD

Use the following command to create or reformat a private key in PEM format:

ssh-keygen -p -f my_oic_key.pem -N "" -t rsa -m pem

🔎 Example:

ssh-keygen -p -f svc_oic_ccsvault_tst@clp.com.hk-2025-09-16T13_18_48.364Z.pem -N "" -t rsa -m pem


Parameters explained:

  • -p → Update or re-save the key file.
  • -f my_oic_key.pem → Path and name of your private key file.
  • -N "" → Empty passphrase (no password required).
  • -t rsa → Generate RSA type key.
  • -m pem → Export the key in PEM format (needed for OIC Vault, Functions, Connections).

Step 3: Verify the Key File

  • Only one private key file (.pem) will be created/updated.
  • It will be saved in PEM format.
  • No public key file (.pub) is generated in this mode.

Step 4: Use in OIC

  • Upload the .pem file into OCI Vault as a secret.
  • Configure OIC Functions to read this private key.
  • Use it in SFTP / REST / API Connections in OIC for key-based authentication.

Final Result: You now have an RSA private key in PEM format, created directly from Windows CMD, ready for use in Oracle Integration Cloud Vault, Functions, and Connections.


Featured Post

Microsoft Excel - List Functions

 Working... Introduction to Excel’s Database Functions Excel's Database Functions work with data ranges treated like a database table ...