- Error code
- ORA-01008
- Product
- Oracle Database
- Versions
- Oracle Database 19c, 21c, 23ai and 26ai (wording differs: see below)
- Category
- PL/SQL & Triggers
- Last reviewed
- 24 October 2026
On this page
What it means
ORA-01008 means a SQL or PL/SQL statement with bind variable placeholders (such as :status or :1) was executed without a value for one of them.
A placeholder in a statement, written with a colon, is a slot that the caller fills when the statement runs. Oracle checks that every slot has a value before executing; if one is missing it raises ORA-01008. Current releases name the placeholder that has no value (or its position); 19c and 21c print only "not all variables bound". The placeholder shown on this page is illustrative.
How slots are matched to values depends on the binding style, and that is where most cases come from. Oracle's reference says: with named binding, one value per name is enough even if the name is repeated; with positional binding, every position needs a value, even if the same name appears twice. In PL/SQL dynamic SQL (EXECUTE IMMEDIATE, OPEN FOR), a SQL statement binds by position, while a dynamic anonymous block or CALL binds each unique name once.
Common causes
- The USING clause of EXECUTE IMMEDIATE or OPEN FOR has fewer values than the dynamic statement has placeholders.
- A placeholder name is repeated in a dynamic SQL statement (for example
:st IS NULL OR status = :st), which binds by position, but only one value is supplied. - Dynamic SQL is assembled from optional filters, and a filter added to the text did not add its value to the bind list.
- Application code binds by position with a driver while the SQL repeats a named placeholder, or a value is skipped when an optional parameter is empty.
- The statement text and the bind list come from different versions of the code, for example after a placeholder was added to the SQL only.
Diagnose it
Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.
Count placeholders against values
Log the exact statement text and the values bound, at the point of execution. Count every colon placeholder (each occurrence, if the statement is SQL bound by position) and compare with the number of values. Colons inside quoted string literals are not placeholders.
Find the dynamic SQL in stored code
SELECT name, type, line, text
FROM all_source
WHERE owner = 'SHOP'
AND (UPPER(text) LIKE '%EXECUTE IMMEDIATE%' OR UPPER(text) LIKE '%OPEN %FOR%')
ORDER BY name, line;Every place that runs dynamic SQL. Start with the unit and line from the ORA-06512 line under the error, and check its USING clause.
See the statement text the database parsed
V$SQL needs privileges such as SELECT_CATALOG_ROLE; ask a DBA if you cannot query it.
SELECT sql_id, parsing_schema_name, sql_text
FROM v$sql
WHERE parsing_schema_name = 'SHOP'
AND sql_text LIKE '%FROM orders%:%'
ORDER BY last_active_time DESC
FETCH FIRST 10 ROWS ONLY;The statement as the application sent it, with its placeholders, which you can compare with the values the code binds.
How to fix it
Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.
Supply one value for each placeholder in the USING clause
Changes schemaFor a dynamic SQL statement, list a bind value for every placeholder occurrence, in order. To use the same value twice, repeat the variable.
CREATE OR REPLACE FUNCTION shop.count_orders (p_status IN VARCHAR2)
RETURN NUMBER
IS
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM shop.orders WHERE (:st IS NULL OR status = :st)'
INTO v_count
USING p_status, p_status; -- two occurrences, two values
RETURN v_count;
END;
/Build optional filters and their binds together
Changes schemaWhen filters are optional, keep the statement fixed and let NULL switch a filter off, as above, so the number of placeholders never changes. For a truly variable number of placeholders, DBMS_SQL lets you bind each one by name as you add it.
CREATE OR REPLACE FUNCTION shop.count_customer_orders (
p_customer_id IN NUMBER,
p_status IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER
IS
v_sql VARCHAR2(400) := 'SELECT COUNT(*) FROM shop.orders WHERE customer_id = :cust';
v_count NUMBER;
c INTEGER := DBMS_SQL.OPEN_CURSOR;
ignore INTEGER;
BEGIN
IF p_status IS NOT NULL THEN
v_sql := v_sql || ' AND status = :status';
END IF;
DBMS_SQL.PARSE(c, v_sql, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c, ':cust', p_customer_id);
IF p_status IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(c, ':status', p_status); -- bound with the text that needs it
END IF;
DBMS_SQL.DEFINE_COLUMN(c, 1, v_count);
ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
DBMS_SQL.COLUMN_VALUE(c, 1, v_count);
DBMS_SQL.CLOSE_CURSOR(c);
RETURN v_count;
END;
/In application code, bind every placeholder, by name where possible
Client or network sideOracle's drivers support named or positional binding. Binding by name (a dictionary or named parameters) needs one value per name, which avoids the repeated-name trap. With positional binding, pass a value for every placeholder position, including NULL for an empty optional parameter, never by skipping it.
Examples
EXECUTE IMMEDIATE with a value missing from USING
Not reproduced here: no Oracle Database is installed for this site. The message format is the current wording in Oracle's error reference, which says it names the placeholder or its position; how the name is printed is illustrative. Oracle Database 19c prints "ORA-01008: not all variables bound".
DECLARE
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM orders WHERE customer_id = :cust AND status = :status'
INTO v_count
USING 4711; -- no value for :status
END;
/ORA-01008: value for bind variable placeholder :STATUS was not provided ORA-06512: at line 4
A repeated placeholder in dynamic SQL, bound once
Not reproduced here: no Oracle Database is installed for this site. Oracle's PL/SQL reference documents that a dynamic SQL statement (not a block or CALL) associates placeholders with bind values by position, so a repeated name needs its value repeated. The message layout is from the error reference; the details are illustrative.
DECLARE
v_count NUMBER;
v_status VARCHAR2(20) := 'OPEN';
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM orders WHERE (:st IS NULL OR status = :st)'
INTO v_count
USING v_status; -- SQL binds by position: two placeholders, one value
END;
/ORA-01008: value for bind variable placeholder :ST was not provided ORA-06512: at line 5
How to prevent it
Keep dynamic SQL statements fixed where you can, with NULL-tolerant filters, so the placeholder count never changes. When text must vary, add each placeholder and its bind in the same step. Prefer binding by name in application drivers.
Never fix ORA-01008 by pasting the value into the SQL text: placeholders are what protect dynamic SQL from SQL injection.
Common questions
How do I fix ORA-01008 not all variables bound?
Count the placeholders in the statement and the values your code supplies. Add the missing value, repeat a value for a repeated placeholder in dynamic SQL, or switch to named binding so each name needs only one value.
Why do I need to pass the same value twice?
A dynamic SQL statement in PL/SQL, and positional binding in drivers, match placeholders to values by position, not by name. Two occurrences of :st are two positions, so they need two values (which can be the same variable).
Why does 19c not say which variable is missing?
The wording changed. Oracle's 19c and 21c references give "not all variables bound"; the current reference names the placeholder without a value, or its position. The cause and fix are the same.
Learn more on CodeWithSQL
Official documentation
- ORA-01008 (Oracle Database Error Messages)
- Native dynamic SQL, repeated placeholder names (Oracle PL/SQL Language Reference 19c)
- EXECUTE IMMEDIATE statement (Oracle PL/SQL Language Reference 19c)
Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.