/ in asked user input variable name - sql plus - oracle

Question

I'm trying to do this:

     select * from tab where date= to_date('&date_dd/mm/yyyy','dd/mm/yyyy');

but it asks 'date_dd' and not 'date_dd/mm/yyyy'.

How can I escape / character? I think that the problem is here!

This is not a valid SQL statement... are you trying to select all dates from your table where it is the same as the user input? What user input are you expecting? — Ben
– Ben, Commented Jan 9, 2014 at 11:06
i just want that, when i ask a variable from input, the name specified of variable must contain a / character... isn't possible?? — paolo
– paolo, Commented Jan 9, 2014 at 11:16
Why does the name of the variable have to contain a /? Are you able to answer my questions? What data are you trying to select from your table and what are you expecting the user to input? — Ben
– Ben, Commented Jan 9, 2014 at 11:18
for example... select 'name','cod', '&DATA_dd/mm/yyyy' from tab; i want to ask the name and suggest to user who use this query, that he must insert date in dd/mm/yyyy format... — paolo
– paolo, Commented Jan 9, 2014 at 11:22
Probably it is easier to prompt something like: "Please enter the date (dd/mm/yyyy):" — Lajos Veres
– Lajos Veres, Commented Jan 9, 2014 at 11:56

Community · Accepted Answer · 2017-05-23 12:31:48Z

1

You can't have this "/" in a variable's name, so it won't appear to the prompt as you do it. Use Lajos solution:

PROMPT Please enter the date with format dd/mm/yyyy:
select * from tab where date= to_date('&date','dd/mm/yyyy');

Will ask it so:

SQL> @my_script
Please enter the date with format dd/mm/yyyy:
Enter value for date:

You could also use ACCEPT e.g. following inspirations from e.g. Oracle SQL*Plus ACCEPT Statements :

accept date PROMPT 'Please enter the date with format dd/mm/yyyy:'
select * from tab where date= to_date('&date','dd/mm/yyyy');

CommunityBot

11 silver badge

answered Jun 10, 2016 at 13:01

J. Chomel

8,44215 gold badges44 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1