The BITAND function in Oracle is used to perform a bitwise AND operation between two integer values. It returns the bitwise logical AND of the two numbers. 🔹 Syntax: BITAND(number1, number2) number1 , number2 must be integers (or expressions th…
In Oracle, BIN_TO_NUM is a built-in bitwise utility function introduced in Oracle 11g (for bitmap operations). It is used to convert a series of binary flags (0s and 1s) into a single numeric value — basically converting a bit vector into its dec…
Identify Exact Position and Code in a Column of a table If you want to see the position and ASCII code of non-printable characters: SELECT LEVEL AS position, SUBSTR(n2, LEVEL, 1) AS character, ASCII(SUBSTR(n2, LEVEL, 1)) AS ascii_v…
Here is a complete SQL script to create and populate a custom ASCII lookup table with character codes (0–127) and their corresponding descriptions/names . ✅ Step 1: Create the table CREATE TABLE ascii_lookup ( ascii_code NUMBER PRIMARY KEY, …
To create an external table in Oracle that reads from multiple files , you simply list all the files under the LOCATION clause of the ORGANIZATION EXTERNAL clause. ✅ Example: External Table with Multiple Files Assume the files: data1.csv data2.csv…
✅ External Tables in Oracle – Overview & Usage External tables in Oracle allow you to query data in flat files (CSV, pipe-delimited, etc.) as if they are regular database tables , without loading the data into the database. They're read-onl…
Let's troubleshoot and ensure you're using EXCEPTIONS INTO correctly. Here’s a working example step-by-step , tested and compliant with Oracle behavior: ✅ 1. Create a table with duplicate data CREATE TABLE test_emp ( emp_id NUMBER, emp…
When you try to enforce a primary key on an existing table with duplicate values , Oracle will throw an error because primary keys must be unique and non-null . Here’s how you can identify and remove duplicates and then add the primary key constrai…
The ASCII function in Oracle returns the numeric ASCII code of the first character in a string. ✅ Syntax: ASCII(char) char can be a single character or a string — only the first character is considered. The return value is a NUMBER repre…
In Oracle, ADD_MONTHS is a date function used to add or subtract a number of months to a given date. ✅ Syntax ADD_MONTHS(date, number_of_months) date : A valid DATE or TIMESTAMP value. number_of_months : Can be positive (add months) or neg…
In Oracle, a ROWTYPE is a composite data type used in PL/SQL to declare a variable that represents a row of a table or row of a cursor . It allows you to easily manipulate and access an entire row of data without declaring each column individually.…
When a column in an Oracle table stores only two distinct values like 'Yes' or 'No' , creating a standard B-tree index on it is not efficient , because: The column has low cardinality (few distinct values), B-tree indexes are most…
To calculate the distance in meters between two points using their latitude and longitude in Oracle, you can use the Haversine formula with Earth’s radius set to 6,371,000 meters (6,371 km) . 1. Using the Haversine Formula in SQL SELECT 6371000 *…
-- scheduling at 01:00 am declare jobno number; begin dbms_job.submit(jobno, '<procedurename>;',TRUNC(sysdate)+1/24,'TRUNC(sysdate+1)+1/24'); end; -- every 2 hours declare jobno number; begin dbms_job.submit(jobno, …
In Oracle, to kill sniped sessions dynamically using SQL, you can use EXECUTE IMMEDIATE inside a PL/SQL block. Understanding Sniped Sessions A sniped session is a session that has been marked as inactive (due to profile settings like IDLE_TIME in …
To view current database parameters in Oracle, you can use the following methods: 🔹 1️⃣ View All Parameters Using V$PARAMETER Run the following SQL query to see all current database parameters : SELECT name, value FROM v$ parameter ORDER BY na…
ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00216: invalid character 231 (0xE7) Error at line 3108 The ORA-31011: XML parsing failed error, along with LPX-00216: invalid character 231 (0xE7) , indicates that Oracle…
