Tech Support & Travel

To set up Java and Apache Tomcat on your Windows PC for local development, follow these steps. Step 1 — Install Java (JDK) Tomcat requires Java. Recommended Version Use: Java 17 (LTS) Recommended vendors: Oracle JDK Eclipse Temurin (OpenJDK) I recomme…

Generic Dynamic Mail PL/SQL Procedure with Dynamic Query, Body and CSV Attachment CREATE OR REPLACE PROCEDURE FMS_ADMIN.SEND_DYNAMIC_MAIL_WITH_ATTACHMENT ( P_TO_MAIL IN VARCHAR2, P_FROM_MAIL IN VARCHAR2, P_SUBJECT IN VAR…

Removing non-printable / ASCII characters  oracle 🔹 1. Your Current Approach (Valid but Heavy) REGEXP_REPLACE(column, '[^[:print:]]', '') ✔ Removes all non-printable characters ❌ Expensive for large datasets (regex on every column) 🔹…

In Oracle, getting the first day of the month is very simple  ✅ Basic Query SELECT TRUNC(SYSDATE, 'MM') FROM dual; 🔎 Explanation TRUNC(date, 'MM') → returns 1st day of that month Time will be 00:00:00 🧪 Examples 🔹 Current month f…

You can create a month-wise pivot using Oracle PIVOT .  Since your period is FY 2025-26 (Apr-2025 to Mar-2026) , we first extract the month from CREATED_DATE and then pivot. Month-wise Pivot Query SELECT * FROM ( SELECT ZONE, INSTL_ADDR_STATE…

Here is a very useful production-style script to drop multiple tables automatically using a loop in Oracle. This is commonly used in data migration, backup cleanup, or refresh scripts . 1️⃣ Drop All Tables Matching a Pattern Example: Drop all backup…

PL/SQL block for dropping a table only if it exists. It safely ignores the error ORA-00942 (table or view does not exist) . Here is the clean version with proper formatting : BEGIN EXECUTE IMMEDIATE 'DROP TABLE CO_DATA_NZ_bkp PURGE'; EXCEPT…

Safe Drop Table in PL/SQL BEGIN EXECUTE IMMEDIATE 'DROP TABLE MAIN_SUB_LOC_NZ_BKP PURGE' ; EXCEPTION WHEN OTHERS THEN IF SQLCODE = -942 THEN NULL ; -- table does not exist, ignore ELSE RAISE; …

Indian Financial Year (April–March) ending logic in Oracle SQL . 🇮🇳 Financial Year (April–March) Financial Year: Starts → 01-APR Ends → 31-MAR (next year) ✅ 1. Get Financial Year End Date for a Given Date Example: '15-DEC-2025' SELECT CASE …

In Oracle SQL, you can use the built-in LAST_DAY function. ✅ If you have a date like '01-DEC-2025' SELECT LAST_DAY(TO_DATE('01-DEC-2025','DD-MON-YYYY')) AS LAST_DATE FROM dual; ✅ Output 31-DEC-2025 🔹 If the column is already …

production-safe logic to ensure SMS is sent only between 10:00 AM and 8:00 PM . ✅ Simple & reliable Oracle logic (recommended) AND TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) BETWEEN 10 AND 19 ✔ Why 19 and not 20? HH24 = 20 means 8:00–8:59 PM “B…

To check whether a particular procedure is scheduled (used) in DBMS_SCHEDULER in Oracle, use the following reliable methods . ✅ 1️⃣ Check Scheduler Jobs Calling That Procedure (MOST COMMON) SELECT owner, job_name, job_type, job_a…

A DBA_2PC unhandled exception while purging usually means Oracle is unable to clean up an in-doubt distributed transaction , even after COMMIT FORCE / ROLLBACK FORCE . This is a known and tricky DBA issue. Problem Error while executing: EXEC DBMS_TR…

Issue ORA-01591: lock held by in-doubt distributed transaction Error Message ORA-01591: lock held by in-doubt distributed transaction 2.96.17311 When Executing SELECT * FROM COMPLAINT WHERE case_no = 'AP1706098703'; Error Description ORA-015…

Problem Statement SELECT * FROM UTILIZATION_REPORT_DATA WHERE AMOUNT_PAID_ONT>0  ORA-01722: invalid number Solution identify rows where AMOUNT_PAID_ONT contains non-numeric characters , which usually cause ORA-06502 / invalid number errors. Howe…

To enter multiple lines (new line) in the same Excel cell : ✅ While typing in a cell Double-click the cell (or press F2 ) Type your first line Press: Windows: ALT + ENTER Mac: CTRL + OPTION + RETURN Continue typing on the next line Press Enter to …

Here is the correct and simplest way to restore an Oracle PACKAGE / PACKAGE BODY using Flashback features . Oracle does not flashback individual objects directly — but you can restore them with the following supported methods. Restore Package f…

To WRAP (encrypt/obfuscate) a PL/SQL procedure in Oracle, you can use the Oracle Wrap Utility or the DBMS_DDL.WRAP function. Below are the correct and practical methods. 1. Using Oracle WRAP Utility (Command Line Method) This is the most common…

The error ORA-01950: no privileges on tablespace 'USERS' means that the user does not have quota on the USERS tablespace to create tables, indexes, or store data. This is very common when creating a new user in Oracle. ✅ How to Fix ORA-0…

In Oracle, there is NO difference between COUNT(*) and COUNT(1) in terms of performance or result . Both return the number of rows in the result set. ✅ 1. COUNT(*) Counts all rows , including rows with NULL values. It does not read column va…

This is a very common Oracle issue when handling mobile numbers stored in a numeric field . Let’s go step by step on why it happens and how to avoid ORA-06502 safely 👇 ⚠️ Why the Error Happens If a mobile number is stored or entered as a char…