This discussion is archived
12 Replies Latest reply: Dec 3, 2012 5:41 AM by hm RSS

How to apply the following prompt in SQL

user10566312 Newbie
Currently Being Moderated
Hi I want to apply a prompt condition which would ask user to enter the a date in a given format. I want to display the following message Enter created date (DD-MON-YYYY HH24:MI:SS).

Select *
From tb1
Where created_dt > to_date('&Enter created date (DD-MON-YYYY HH24:MI:SS)', 'DD-MON-YYYY HH24:MI:SS')
  • 1. Re: How to apply the following prompt in SQL
    hm Expert
    Currently Being Moderated
    When you use sqlplus you could use a prompt before your sql:
    prompt Enter created_date in format DD-MON-YYYY HH24:MI:SS
    
    Select *
    From tb1
    Where created_dt > to_date('&created_date', 'DD-MON-YYYY HH24:MI:SS');
    If you anyhow want to generate the prompt only the substitution variable you must use something like &Enter_create_date_in_format_DD_MM_YYYY, because a blank ' ' or a minus '-' will end the name of the variable.

    Edited by: hm on 03.12.2012 01:15
  • 2. Re: How to apply the following prompt in SQL
    user10566312 Newbie
    Currently Being Moderated
    I think Oracle should look into this in its next release. How can we suggest this to Oracle?
  • 3. Re: How to apply the following prompt in SQL
    Paul Horth Expert
    Currently Being Moderated
    user10566312 wrote:
    Hi I want to apply a prompt condition which would ask user to enter the a date in a given format. I want to display the following message Enter created date (DD-MON-YYYY HH24:MI:SS).

    Select *
    From tb1
    Where created_dt > to_date('&Enter created date (DD-MON-YYYY HH24:MI:SS)', 'DD-MON-YYYY HH24:MI:SS')
    Try
    Select *
    From tb1
    Where created_dt > to_date('&"Enter created date (DD-MON-YYYY HH24:MI:SS)"', 'DD-MON-YYYY HH24:MI:SS')
  • 4. Re: How to apply the following prompt in SQL
    hm Expert
    Currently Being Moderated
    This is not a bug. Names of substitution variables may only contain alphanumeric characters and the underscore '_'.

    (i just can't find it in the documentation)
  • 5. Re: How to apply the following prompt in SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    user10566312 wrote:
    I think Oracle should look into this in its next release. How can we suggest this to Oracle?
    I think you should learn to understand the difference between SQL and SQL*Plus, and other GUI interface design methods.

    You can't suggest that Oracle should change something that already works and is documented as such. If it's not a problem (to everyone else in the world) then why should they change something just for you because you don't have a good understanding of it?

    http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch5.htm#sthref1031

    There are very few (if any) software languages around that allow for space characters in variable names.
  • 6. Re: How to apply the following prompt in SQL
    Paul Horth Expert
    Currently Being Moderated
    hm wrote:
    This is not a bug. Names of substitution variables may only contain alphanumeric characters and the underscore '_'.

    (i just can't find it in the documentation)
    Oh yes they can contain spaces: see above.

    Edited by: Paul Horth on Dec 3, 2012 11:04 AM
  • 7. Re: How to apply the following prompt in SQL
    user10566312 Newbie
    Currently Being Moderated
    What I want to suggest Oracle is that there should be a SQL syntax where the user is prompted some message and the value entered by the user is stored in the variable which has no space.
  • 8. Re: How to apply the following prompt in SQL
    EdStevens Guru
    Currently Being Moderated
    user10566312 wrote:
    What I want to suggest Oracle is that there should be a SQL syntax where the user is prompted some message and the value entered by the user is stored in the variable which has no space.
    You are free to submit that to either Oracle or the ANSI standards committee. I wouldn't hold my breathe waiting for the implementation you want.
  • 9. Re: How to apply the following prompt in SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    user10566312 wrote:
    What I want to suggest Oracle is that there should be a SQL syntax where the user is prompted some message and the value entered by the user is stored in the variable which has no space.
    That defeats client server architecture.

    SQL is a process running on the database server. That process cannot (and never will) hack across the network onto the client computer and cause something to be displayed on that clients terminal/screen and then read input from the client computer keyboard, to get it back to the SQL running on the server.

    For output and input, that has to be done through client based software, so requires something like SQL*Plus, or operating system scripts, or GUI interfaces written in Application Express, Java, .NET etc.

    So, feel free to request that Oracle changes SQL to allow for prompting, but it won't happen.

    That's why I suggested you get an understanding of the difference between SQL, SQL*Plus and other Interfaces. It's basic client server architecture and network/operating system security.
  • 10. Re: How to apply the following prompt in SQL
    hm Expert
    Currently Being Moderated
    There is already such a syntax in sqlplus:

    Example:
    ACCEPT v_date CHAR PROMPT "Enter Date (DD-MM-YYYY HH24:MI:SS):"
    
    select '&v_date' from dual;
    
    select to_date('&v_date','DD-MM-YYYY HH24:MI:SS') from dual;
  • 11. Re: How to apply the following prompt in SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    user10566312 wrote:
    What I want to suggest Oracle is that there should be a SQL syntax where the user is prompted some message and the value entered by the user is stored in the variable which has no space.
    Thinking about it, perhaps it's not clear to you how it works, so let's just create a theoretical example for you, which may make it clearer...

    If I were to ask you to write a bit of software with the following specification, do you think you would be able to do it?

    a) the software should be installed and execute on the server
    b) the software should write display to a client computer (not the server)
    c) the software should accept input from the keyboard of a client computer (not the server)

    Do you think you'd be able to get that working?

    Think about how that software would have to go from the server to the client computer, and how it is going to cause that client computer to display something on it's screen (bearing in mind, your software isn't going to know what operating system is being run on that client computer, what other programs are running, what screen resolution it has etc.).
    Also, think about how that software is going to read input from the clients keyboard (again bearing in mind it doesn't know the operating system, what keyboard is installed etc.)

    When you think of it from the server side of things, then you are thinking of it from where SQL is running. Then you may be able to see why SQL itself cannot prompt for anything or accept any input.

    This is why we have client software... in the simplest case, SQL*Plus, which is installed on the client computer and can interact with the client computers display and keyboard/input. When you include a variable (specified with an &) in your SQL code, it isn't SQL itself that is requesting the input for that variable, it's SQL*Plus that has parsed your SQL script, picked out what variables need requesting, prompts for them and get's the input to say what those variables values are, and then puts those values (substitutes them) into the SQL statement, before passing the SQL statement to the SQL process on the server to be executed. It is then SQL*Plus that retrieves the results of the SQL from the server and displays the results to the screen.

    That is why those variables are called SQL*Plus substitution variables, because the values are got through SQL*Plus and Substituted in the query.

    In other interfaces for executing SQL code, you may have different methods of substituting values into the code before it is sent to the server. Something like TOAD can use the ":" character by default to be the substitution variable. You can typically turn them off, so they are treated as data/strings directly in the statement rather than getting substituted values, e.g. to do that in SQL*Plus you use "set define off" or "set define X" (where X is the character you want to use as the substitution variable indicator); in TOAD there is an option to turn it off from a popup menu.
  • 12. Re: How to apply the following prompt in SQL
    BluShadow Guru Moderator
    Currently Being Moderated
    hm wrote:
    There is already such a syntax in sqlplus:

    Example:
    ACCEPT v_date CHAR PROMPT "Enter Date (DD-MM-YYYY HH24:MI:SS):"
    
    select '&v_date' from dual;
    
    select to_date('&v_date','DD-MM-YYYY HH24:MI:SS') from dual;
    That's not a prompt in SQL though (as per the subject line of this thread). That's a funtionality of the SQL*Plus client software.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points