Row-by-row processing with cursors instead of bulk operations

Use FORALL and BULK COLLECT for array processing

Cursors process rows one-by-one, causing context switches between PL/SQL and SQL engines. Each context switch costs 1-2ms. For 10,000 rows, that's 10-20 seconds just in context switches. Bulk operations process entire array in single operation (1 context switch).

Example - Cursor (SLOW):

BEGIN

FOR r IN (SELECT order_id FROM orders WHERE status = 'PENDING') LOOP

UPDATE orders SET status = 'PROCESSED' WHERE order_id = r.order_id;

END LOOP;

COMMIT;

END;

-- 10,000 rows: 30 seconds

-- CORRECT: Bulk operations (FAST)

DECLARE

TYPE t_order_ids IS TABLE OF NUMBER;

v_order_ids t_order_ids;

BEGIN

SELECT order_id BULK COLLECT INTO v_order_ids

FROM orders WHERE status = 'PENDING';

FORALL i IN 1..v_order_ids.COUNT

UPDATE orders SET status = 'PROCESSED' WHERE order_id = v_order_ids(i);

COMMIT;

END;

-- 10,000 rows: 0.3 seconds (100x faster)

Always use FORALL and BULK COLLECT for array processing. Use cursors only when you need to call external API per row or have complex per-row logic.

Critical

10-100x slower performance, 10,000 rows takes 30 seconds instead of 0.3 seconds

SQL injection in dynamic SQL by concatenating user input

Always use bind variables in EXECUTE IMMEDIATE

Concatenating user input directly into SQL string allows SQL injection attacks. Attackers can inject malicious SQL to access/modify unauthorized data.

Example - SQL Injection (DANGEROUS):

DECLARE

v_user_input VARCHAR2(100) := "100 OR 1=1"; -- Malicious input

v_sql VARCHAR2(1000);

v_count NUMBER;

BEGIN

v_sql := 'SELECT COUNT(*) FROM orders WHERE customer_id = ' || v_user_input;

EXECUTE IMMEDIATE v_sql INTO v_count;

-- Executes: SELECT COUNT(*) FROM orders WHERE customer_id = 100 OR 1=1

-- Returns all orders (security breach)

END;