Impact of defining VARCHAR2 column with greater length

In the database, there is no difference. VARCHAR2 are stored with variable length, the declared length is only a limitation.

However, some client applications will reserve 1000 bytes per column instead of 10 if they see that the column is defined as VARCHAR2(1000)

The answer depends on whether you're talking about a column in a database table, or a variable in a PL/SQL program.

Database column

The amount of storage used is proportionate to the size of the data stored.

PL/SQL variable

If the variable is declared with a size 1 to 4000 (11g+) / 1999 (10g or earlier), memory will be allocated for the maximum length (i.e. VARCHAR2(100) will require at least 100 bytes of memory).

If the variable is declared with a size 4001 (11g+) / 2000 (10g or earlier) or greater, memory will be allocated according to the size of the data stored. (an interesting side question would be, if the variable's value is changed, how is the memory resized - does it reallocate another buffer with the new size?)

Reference for 10g: PL/SQL Datatypes

Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.

Reference for 11g: Avoiding Memory Overhead in PL/SQL Code

Specify a size of more than 4000 characters for the VARCHAR2 variable; PL/SQL waits until you assign the variable, then only allocates as much storage as needed