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
– Jeffrey Kemp, Commented Apr 16, 2014 at 3:56
possible duplicate of SQL*Plus how to accept text variable from prompt? — user272735
– user272735, Commented Apr 16, 2014 at 5:08
Thx Jeffrey, but there is not risk for injection. The script was used temporary by team members only. — user2360915
– user2360915, Commented Aug 4, 2014 at 22:45

user2360915 · Accepted Answer · 2014-04-16 01:10:20Z

2

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;

answered Apr 16, 2014 at 1:10

user2360915

1,13911 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related