ORA-01008: not all variables bound错误排查求助

Hey there, let’s break down why you’re hitting this error and walk through how to fix it.

Why the Error Happens #

First, let’s clear up what &c actually does: it’s a client-side substitution variable (used by tools like SQL*Plus, SQL Developer, etc.), not a database-level bind variable.

When you run the static SQL select 2*4 + &c from dual directly, your client tool first prompts you for a value for &c, replaces it in the SQL string, and sends the fully resolved query (like select 2*4 + 5 from dual) to the database. The database never sees the &c—it only gets the final, value-filled SQL.

But in your PL/SQL block, when you use EXECUTE IMMEDIATE, you’re sending the raw string containing &c straight to the database. The database doesn’t recognize &c as a client substitution variable—it treats it as an unbound bind variable (like :c but without a value provided). Since you don’t pass a value for it, you get the ORA-01008 error.

Solutions #

1. Use PL/SQL Bind Variables (Recommended) #

This is the safest and most efficient approach, as it prevents SQL injection and allows the database to reuse execution plans. We’ll swap the client substitution variable with a PL/SQL bind variable :c, then pass its value when running the dynamic SQL.

Option A: Update the script field in the table

If you can modify the formula_script table, change the script value from '1 - 1 * &c' to '1 - 1 * :c'. Then use this PL/SQL block:

declare 
  sql_query varchar2(100); 
  i number; 
  c_value number := 3; -- Replace with your desired value (or fetch from another source)
begin 
  select script into sql_query from formula_script where id = 1; 
  EXECUTE IMMEDIATE 'select ' || sql_query || ' from dual' into i using c_value; 
  dbms_output.put_line('Result: ' || i); -- Optional: print the calculation result
end;
/

Option B: Replace &c in PL/SQL (no table changes)

If you can’t edit the table, use the replace() function to swap &c with the bind variable syntax before executing:

declare 
  sql_query varchar2(100); 
  i number; 
  c_value number := 3; 
begin 
  select replace(script, '&c', ':c') into sql_query from formula_script where id = 1; 
  EXECUTE IMMEDIATE 'select ' || sql_query || ' from dual' into i using c_value; 
  dbms_output.put_line('Result: ' || i);
end;
/

2. Let the Client Handle the Substitution First #

If you still want to use the client’s prompt for &c, resolve the substitution before passing the SQL string to PL/SQL. Here’s how to do it in SQL*Plus:

-- Declare variables at the client level
variable sql_query varchar2(100);
variable i number;

-- Fetch the script (client will prompt for &c value here)
select script into :sql_query from formula_script where id = 1;

-- Execute the dynamic SQL with the already-resolved string
execute EXECUTE IMMEDIATE 'select ' || :sql_query || ' from dual' into :i;

-- Print the final result
print i;

When you run this, SQL*Plus will prompt you for a value for &c when fetching the script, replacing it in the string before it’s stored in :sql_query. The dynamic SQL then runs with no unbound variables.

内容的提问来源于stack exchange，提问作者HryEsf