SQL commands for getting bind variable value

$ cat chk_bind_value.sql
Prompt Enter SQL_ID


select SQL_ID,name,VALUE_STRING,VALUE_ANYDATA,POSITION,DUP_POSITION,last_captured
from V$SQL_BIND_CAPTURE i
where SQL_ID = '&1'



$ cat chk_hist_bind_value.sql
set pages 1000
select SQL_ID,name,VALUE_STRING,VALUE_ANYDATA,POSITION,DUP_POSITION,last_captured  from dba_hist_sqlbind
where SQL_ID='&1' order by snap_id
/

Using bind variables in SQL*Plus

mark two links for quick reference

Declare bind variables in SQL*Plus

Using bind variables in SQL*Plus

You can declare a bind variable in SQL*Plus though, and select into that:

var l_test_quote varchar2(80); -- or whatever type/size you need
var l_test_id varchar2(80);

declare
    l_id varchar2(80) :='test123';
begin
    select test_quote, test_id
    into :l_test_quote, :l_test_id
    from order_link
    where id = l_id;
end;
/

print l_test_quote
print l_test_id
Note the : before the references to the variables defined outside the block, indicating they are bind variables. l_id is declared inside the block so it does not have a preceding :.

In this case you could also define l_id outside the block, and avoid PL/SQL while still using a bind variable for that:

var l_id varchar2(80);

exec :l_id := 'test123';

select test_quote, test_id
from order_link
where id = :l_id;
Because the main query isn't PL/SQL any more (although the exec is; that's just a shorthand for a one-line anonymous block), you don't need to select ... into so you don't need to declare those variables.