You'll run into this shape in PL/SQL procedures that need to serialize work per row:

SELECT clientid
  INTO l_locked_client_id
  FROM dataela.clients
 WHERE clientid = p_client_id
 FOR UPDATE;

The selected value is never used — the variable name admits it. The query exists purely to take a row-level exclusive lock, turning that client row into a mutex so a read-modify-write sequence can't be clobbered by a concurrent session. It raises NO_DATA_FOUND for free if the client doesn't exist.

Two things about that lock are easy to get wrong.

The lock outlives the procedure. A PL/SQL block is not a transaction boundary. When the procedure returns, the lock is still held — it belongs to the caller's transaction and is released only at COMMIT or ROLLBACK (or when the session dies, or on the implicit commit from a stray DDL statement mid-transaction, which drops it silently). So hold time is decided by the caller, not by the procedure that took the lock. If the caller runs for five minutes, the row is locked for five minutes. Worth a comment on the procedure saying exactly that.

The WAIT clause is the waiter's patience, not the holder's timeout.

FOR UPDATE              -- wait forever (default)
FOR UPDATE NOWAIT       -- fail immediately, ORA-00054
FOR UPDATE WAIT 5       -- give up after 5s, ORA-30006
FOR UPDATE SKIP LOCKED  -- skip locked rows (queue consumers)

Each clause constrains only the statement it's attached to, so whatever the holder wrote has no effect on how long anyone else waits. Oracle has no holder-side "release after N seconds" and no global lock timeout to protect you — if you don't want connections piling up behind a lock, every call site that might wait needs its own NOWAIT or WAIT n plus a handler that retries or returns "try again later". Breaking a lock from outside means ALTER SYSTEM KILL SESSION.

That default infinite wait is also what makes the pattern deadlock-prone: two procedures locking several client rows in opposite orders will wait on each other until ORA-00060. Lock in a consistent order, or use NOWAIT with a retry.

One thing you don't have to worry about: a plain SELECT never blocks on any of this. Oracle rebuilds the pre-change version of the row from undo and reads that, so reporting queries pass straight through a locked row — none of the WITH (NOLOCK) reflex SQL Server teaches. Only FOR UPDATE, UPDATE, and DELETE against the same row queue up behind you, and only that row.

VARCHAR2(4000) means 4000 bytes, not characters. Most people know this. What's less obvious: VARCHAR2(4000 CHAR) doesn't guarantee 4000 characters either.

Under the default MAX_STRING_SIZE=STANDARD, the hard column cap is 4000 bytes regardless of whether you declared BYTE or CHAR. In AL32UTF8, a Chinese character takes ~3 bytes, so VARCHAR2(4000 CHAR) on a column storing CJK text will fail once the actual byte count exceeds 4000 — around ~1333 characters in.

To actually store 4000 CJK characters in a single VARCHAR2, the instance needs MAX_STRING_SIZE=EXTENDED (12c+), which raises the limit to 32767 bytes. This is not the default — not even in 19c — and it's a one-way migration that requires running utl32k.sql in upgrade mode. Oracle keeps it off by default precisely because it changes data dictionary behavior and breaks compatibility.

Quick check for your instance:

SELECT value FROM v$parameter WHERE name = 'max_string_size';

STANDARD = 4000-byte ceiling. EXTENDED = 32767-byte ceiling.

When scripting with Oracle SQLcl, the startup banner (version, copyright, connection info) clamps your output. The -S (silent) flag suppresses all of it:

sql -S user/password@connect_string @script.sql

This gives you clean output suitable for piping or log capture.

For even more control inside the session, pair it with:

set heading off
set feedback off
set pagesize 0
set echo off

-S is the entry-level switch. The set commands handle the rest.

1. Multiple Column Updates using IN clause:

UPDATE table_name
SET (col1, col2, col3) = 
    (SELECT val1, val2, val3 FROM source_table WHERE condition)
WHERE condition;

2. MERGE statement for conditional updates/inserts:

MERGE INTO target_table t
USING source_table s
ON (t.key = s.key)
WHEN MATCHED THEN
    UPDATE SET t.col1 = s.col1, t.col2 = s.col2
WHEN NOT MATCHED THEN
    INSERT (col1, col2) VALUES (s.col1, s.col2);

3. Using DEFAULT values in updates:

UPDATE employees
SET (salary, commission) = (DEFAULT, DEFAULT)
WHERE department_id = 90;

4. Updating with RETURNING clause to get modified values:

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 20
RETURNING employee_id, salary INTO :emp_id, :new_salary;

5. Using row constructor expression:

UPDATE table_name
SET (col1, col2) = (SELECT * FROM (VALUES (val1, val2)));

6. Conditional updates using CASE:

UPDATE employees
SET salary = CASE 
    WHEN department_id = 10 THEN salary * 1.1
    WHEN department_id = 20 THEN salary * 1.2
    ELSE salary
END;

7. Using WITH clause (Common Table Expression) in updates:

WITH avg_sal AS (
    SELECT dept_id, AVG(salary) avg_salary
    FROM employees
    GROUP BY dept_id
)
UPDATE employees e
SET salary = (
    SELECT a.avg_salary 
    FROM avg_sal a 
    WHERE e.dept_id = a.dept_id
)
WHERE condition;

These syntaxes are particularly useful when:

• You need to update multiple columns at once
• The update values come from another table or subquery
• You want to perform conditional updates
• You need to track what was updated
• You're dealing with complex data transformations

Remember that while these features provide more elegant solutions, it's important to:

1. Test performance with your specific data volumes
2. Ensure the business logic is clear to other developers
3. Consider maintainability of the code
4. Check if indexes are being used effectively

Navigating Oracle Foreign Key Constraint Deletion: A Developer's Comprehensive Guide

Understanding the "Child Record Found" Dilemma

As an Oracle database developer, you've likely encountered the frustrating ORA-02292: integrity constraint violated - child record found error. This message appears when you attempt to delete a record from a parent table that is still referenced by child records in another table.

What Causes This Error?

Referential integrity constraints prevent you from deleting records that are crucial to maintaining data relationships. While these constraints protect your data's consistency, they can also complicate deletion processes.

Systematic Troubleshooting Approach

Step 1: Identify the Constraint

When you encounter the error, note the specific constraint name. In our example:

ERROR at line 1:
ORA-02292: integrity constraint (ACME_CORP.FK_TRANSACTION_ACCOUNT) violated

Step 2: Locate Referencing Constraints

Use the following comprehensive query to find details about the problematic constraint:

SELECT a.table_name, 
       a.constraint_name, 
       a.r_constraint_name,
       b.column_name
FROM all_constraints a
JOIN all_cons_columns b ON a.constraint_name = b.constraint_name
WHERE a.r_constraint_name = 'FK_TRANSACTION_ACCOUNT';

Step 3: Investigate Child Records

Once you've identified the referencing table, query the specific records:

SELECT b.*
FROM HR.EMPLOYEE_BANK_ACCOUNTS a
JOIN ACME_CORP.FINANCIAL_TRANSACTIONS b 
ON b.employee_bank_account_id = a.account_id
WHERE a.account_id IN (
    -- Your deletion criteria here
);

Resolution Strategies

Option 1: Cascade Delete

If appropriate for your data model, use ON DELETE CASCADE:

ALTER TABLE ACME_CORP.FINANCIAL_TRANSACTIONS 
DROP CONSTRAINT FK_TRANSACTION_ACCOUNT;

ALTER TABLE ACME_CORP.FINANCIAL_TRANSACTIONS 
ADD CONSTRAINT FK_TRANSACTION_ACCOUNT 
FOREIGN KEY (employee_bank_account_id)
REFERENCES HR.EMPLOYEE_BANK_ACCOUNTS(account_id)
ON DELETE CASCADE;

Option 2: Selective Deletion

Manually delete or update child records before removing parent records:

-- First, delete or update child records
DELETE FROM ACME_CORP.FINANCIAL_TRANSACTIONS 
WHERE employee_bank_account_id IN (
    SELECT account_id 
    FROM HR.EMPLOYEE_BANK_ACCOUNTS 
    WHERE deletion_condition
);

-- Then delete parent records
DELETE FROM HR.EMPLOYEE_BANK_ACCOUNTS 
WHERE deletion_condition;

Best Practices

1. Always use transactions to ensure data consistency

2. Understand your data relationships before modifying constraints

3. Test deletion scripts in a staging environment

4. Consider soft delete strategies for complex data models