Oracle SQL Developer String variable binding

Question

I'm using Oracle SQL Developer 4.0.1.14 and trying to figure out some variable bindings. I started with the queries below:

SELECT *
FROM Ships
WHERE UniqueId = 17;

and

SELECT *
FROM Ships
WHERE UniqueId = :variable;

These both execute successfully. When you run the second one, Oracle SQL Developer prompts you for a value, of which i enter 17. However, when i try to do the same thing with a string parameter, I am unsuccessful (Query returns 0 rows). For example:

SELECT *
FROM Ships
WHERE ShipName = 'Atlantic Boat';

and

SELECT *
FROM Ships
WHERE ShipName LIKE :variable;

Only the first query is successful. I have tried entering into the popup window

Atlantic Boat

'Atlantic Boat'

"Atlantic Boat"

and other variations without success. How can i make this work?

EDIT: I have tried using the like statement and found some success.

SELECT *
FROM Ships
WHERE ShipName LIKE '%Atlantic Boat%';

and

SELECT *
FROM Ships
WHERE ShipName LIKE :variable;

Both actually work. The second requires %Atlantic Boat%, with no string quotes (''). I am still unable to get the = one to work however, even with % variations.

Answer 1

If the table is defined with the name as a varchar2 field then it works as expected:

create table ships (uniqueid number, shipname varchar2(20));
insert into ships values (17, 'Atlantic Boat');

SELECT *
FROM Ships
WHERE ShipName = 'Atlantic Boat';

  UNIQUEID SHIPNAME                     
---------- ------------------------------
        17 Atlantic Boat                  

var variable varchar2(20);

anonymous block completed

exec :variable := 'Atlantic Boat';

SELECT *
FROM Ships
WHERE ShipName LIKE :variable;

  UNIQUEID SHIPNAME                     
---------- ------------------------------
        17 Atlantic Boat                  

But if the table has a char column then it shows the behaviour you describe:

drop table ships;
create table ships (uniqueid number, shipname char(20));
insert into ships values (17, 'Atlantic Boat');

SELECT *
FROM Ships
WHERE ShipName = 'Atlantic Boat';

  UNIQUEID SHIPNAME                     
---------- ------------------------------
        17 Atlantic Boat                  


var variable varchar2(20);
exec :variable := 'Atlantic Boat';

anonymous block completed

SELECT *
FROM Ships
WHERE ShipName LIKE :variable;

no rows selected

That happens even if the bind variable is declared as char(20) as well.

char values are stored blank-padded to the field length, so in this case the stored value is actually 'Atlantic Boat ', with 7 spaces at the end. Normally when you do a comparison like WHERE ShipName = 'Atlantic Boat' the string literal is implicitly converted to the column type you are comparing against, and during comparison they are seen as equal. That seems not to be happening for the bind variable, but I can't immediately see any reference to that behaviour in the documentation.

When you use LIKE 'Atlantic Boat%' or pass Atlantic Boat% as the bind variable, the extra spaces are no longer relevant because the full 'Atlantic Boat ' is indeed like Atlantic Boat%.

There's no real reason to ever use char (some like short flags or fields which are always fixed length to be char, but even there is makes little difference). The best way to fix this is to change your table definition so the field is varchar2.

If you can't do that you can cast the bind variable in the query:

SELECT *
FROM Ships
WHERE ShipName LIKE cast(:variable as char(20));

  UNIQUEID SHIPNAME                     
---------- ------------------------------
        17 Atlantic Boat                  

Answer 2

I'm using SQL Developer 4.0.1.14 and it does work on my side passing only Atlantic Boat:

As @Alex Poole mentioned on his comment above, make sure ShipName is of type varchar2. Type desbribe Ships to check that info:

---

You can also check this SQL Fiddle. See that like 'Atlantic Boat' correctly fetches the row.