I tried to run and execute the following block :

declare 
    sql_query varchar2(100);
    i number;
begin
select script into sql_query from formula_script where id = 1;
EXECUTE IMMEDIATE 
            'select ' || sql_query || ' from dual' into i; 
end;

sql_query is a cell with varchar2 datatype with this value : '1 - 1 * &c'

but couldn't get the result and it had the following error :

Error report -
ORA-01008: not all variables bound
ORA-06512: at line 6
01008. 00000 -  "not all variables bound"
*Cause:    
*Action:

and when I run the following code, It hasn't any error:

select 2*4   &c from dual; 

thank you for your helping!

CodePudding user response：

Substitution is performed by the client tool (SQL*Plus, SQL Developer etc) before sending the block to the database for processing, so you need to include a substitution variable explicitly.

Also, the &c in the generated expression is not a substitution variable but a bind variable. I must admit I was surprised that the compiler treated it as one, because normally bind variables are prefixed with :, and I couldn't find anything in the documentation about it, but it seems to treat & the same as :. Finally, bind variables in dynamic SQL are positional and not named, so :b1 would have the same meaning as &c within the generated string (and perhaps be less confusing).

declare
    sql_query varchar2(100);
    i  number;
begin
    select script into sql_query from formula_script where id = 1;

    execute immediate 'select '||sql_query||' from dual'
        into i
        using &input_number;

    dbms_output.put_line('Expression: '||sql_query);
    dbms_output.put_line('Result: '|| i);
end;
/

Enter value for input_number: 123
Expression: 1 - 1 * &c
Result: -122

CodePudding user response：

If you use:

DECLARE
  sql_query varchar2(100) := '2*4   &c';
  i number;
BEGIN
  EXECUTE IMMEDIATE 'select ' || sql_query || ' from dual' INTO i; 
  DBMS_OUTPUT.PUT_LINE(i);
END;
/

Then your query is:

select 2*4   &c from dual

And &c is a substitution variable. However, when you use EXECUTE IMMEDIATE without a USING clause then there is no input to specify the value of the substitution variable so it will be unbound and you will get the error:

ORA-01008: not all variables bound
ORA-06512: at line 5

From the EXECUTE IMMEDIATE documentation:

bind_argument

An expression whose value replaces its corresponding placeholder in dynamic_sql_stmt at run time.

Every placeholder in dynamic_sql_stmt must be associated with a bind_argument in the USING clause or RETURNING INTO clause (or both) or with a define variable in the INTO clause.

Therefore, if you have a placeholder, you MUST have a USING (or INTO or RETURNING INTO) clause in the EXECUTE IMMEDIATE statement.

A placeholder expression has the syntax:

placeholder_expression::=

The & syntax is used for referencing SQL/Plus substitution variables.

A substitution variable is preceded by one or two ampersands (&). When SQL*Plus encounters a substitution variable in a command, SQL*Plus executes the command as though it contained the value of the substitution variable, rather than the variable itself.

Thus substitution variable are typically processed by the client application and are never seen by the SQL engine (as the substitution variable will have been replaced by the value they are substituting for).

There is no obvious reason why EXECUTE IMMEDIATE supports the use of substitution variable placeholder expressions; it appears to be an undocumented feature as the syntax does not support its use. However, it does work in the same manner as a bind variable placeholder expression.

If you bind the variable with the USING clause:

DECLARE
  sql_query varchar2(100) := '2*4   &c';
  i number;
BEGIN
  EXECUTE IMMEDIATE 'select ' || sql_query || ' from dual' INTO i USING 1;
  DBMS_OUTPUT.PUT_LINE(i);
END;
/

Then the query works and the output is: 9.

db<>fiddle here