I'm on Oracle 11.2
In SQL Developer, I'm having hard time executing a SQL with a bind variable. Below is the test table.
create table TEST1 (TYP varchar2(3), NUM number(30,18)); insert into TEST1 values ('TTT', 45.15168794137111); commit;
Then I execute the below query and it returns no results. Why?
VARIABLE P1 number; EXEC :P1 := 45.15168794137111; select * from TEST1 where NUM = :P1;
The above works in SQL Plus.
That replaces the SQL before it executes it. It is just like using literals. I wanted to use bind variables for performance testing purposes.
See the sql developer documentation:
Run Script executes all statements in the Enter SQL Statement box using the Script
Runner. The SQL statements can include substitution variables (but not bind
variables) of type VARCHAR2 (although in most cases, VARCHAR2 is
automatically converted internally to NUMBER if necessary); a pop-up box is
displayed for entering substitution variable values.
Notice the 'but not bind variables'