Sqlplus pl/sql Date/Time user input understood as bind variable

Question

I am facing a strange issue.

I have a small pl/sql anonymous block which prompt the user for a date. I want to re-use this date in a query later, but I get an error like "SP2-0552: Bind variable "01" not declared.".

The issue is that ":01" in the time is interpreted as a bind variable. A workaround is to enter the date between quote '2014/04/16 01:01:01' and not directly 2014/04/16 01:01:01. However, I want to be able to enter my date without the quote.

here is a simple script:

declare
    adate VARCHAR2(20);
begin
    adate := &adate;
    query := 'select to_date(''' || adate ||''', ''YYYY/MM/DD HH24:MI:SS'') from dual';
    dbms_output.put_line(query);
end;

Enter value for adate: 2014/04/15 01:01:01
old   4:    adate := &adate;
new   4:    adate := 2014/04/15 01:01:01;
SP2-0552: Bind variable "01" not declared.

You're using a substitution variable - so the value will be pasted in as if it were code. That's why you need '`'quotes'`' around it. Note: Be aware of the SQL injection vulnerability here. e.g. the user could enter something like `'; execute immediate 'drop table bobby';` — Jeffrey Kemp, Apr 16 '14 at 03:56
possible duplicate of [SQL\*Plus how to accept text variable from prompt?](http://stackoverflow.com/questions/16674252/sqlplus-how-to-accept-text-variable-from-prompt) — user272735, Apr 16 '14 at 05:08
Thx Jeffrey, but there is not risk for injection. The script was used temporary by team members only. — user2360915, Aug 04 '14 at 22:45

score 2 · Accepted Answer · answered Apr 16 '14 at 01:10

I found the answer.

The variable must be between quote in the script.

declare
    adate VARCHAR2(20);
begin
    adate := '&adate';
    query := 'select to_date(''' || adate ||''', ''YYYY/MM/DD HH24:MI:SS'') from dual';
    dbms_output.put_line(query);
end;

Sqlplus pl/sql Date/Time user input understood as bind variable

1 Answers1