CODEWITHSUNDEEP
oracle-databasesqlplus
Z.H
Z.H

Reputation: 25

How to use substitute variable in sqlplus? IN condition

I faced an error when i actually use ampersand for the input, it does not return the record

When I hardcoded the name, it was able to show the record. The name I entered for example is 'customer something', with a space in between.

Is it something to do with the input not being able to accept the space? So how can solve it to return the record inside IN?

WHERE
    name IN ('&name');

Upvotes: 1

Views: 293

Answers (1)

thatjeffsmith
thatjeffsmith

Reputation: 22457

Quote the substitution string when prompted, don't quote the substitution text in the query itself. 

SQL> select &message from dual;
Enter value for message: hello mom
old   1: select &message from dual
new   1: select hello mom from dual
select hello mom from dual
       *
ERROR at line 1:
ORA-00904: "HELLO": invalid identifier


SQL> select &message from dual;
Enter value for message: 'Hello, Mom!'
old   1: select &message from dual
new   1: select 'Hello, Mom!' from dual

'HELLO,MOM!
-----------
Hello, Mom!

SQL> create table so_ampersand (message varchar2(20));

Table created.

SQL> insert into so_ampersand values ('Hello, Mom!');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from so_ampersand where message in (&mess);
Enter value for mess: 'Hello, Mom!'
old   1: select * from so_ampersand where message in (&mess)
new   1: select * from so_ampersand where message in ('Hello, Mom!')

MESSAGE
--------------------
Hello, Mom!

SQL>

Or, the other way around..

SQL> select * from so_ampersand where message in ('&mess2');
Enter value for mess2: Hello, Mom!
old   1: select * from so_ampersand where message in ('&mess2')
new   1: select * from so_ampersand where message in ('Hello, Mom!')

MESSAGE
--------------------
Hello, Mom!

Upvotes: 1

Related Questions