Where did my zeroes go

Question:
I'm using native dynamic SQL to insert data into my table. I'm finding that if I concatenate my data into the table, I get different results than if I bind the values in the USING clause. Specifically, in the program below, when I concatenate my value of '000011111' for the value1 column is stored in the database as '11111'.
DROP TABLE otn_insert
/
CREATE TABLE otn_insert (CONTEXT VARCHAR2(100),
value1 VARCHAR2(100), value2 VARCHAR2(100))
/

DECLARE
l_insert VARCHAR2 (500);
l_value1 otn_insert.value1%TYPE;
l_value2 otn_insert.value2%TYPE;
BEGIN
l_value1 := '00001111';
l_value2 := '11110000';

INSERT INTO otn_insert
(value1, value2
)
VALUES ('Static INSERT', l_value1, l_value1
);

l_insert :=
'INSERT INTO otn_insert (value1, value2)
VALUES (''Dynamic Concatenation'','
|| l_value1
|| ','
|| l_value2
|| ')';

EXECUTE IMMEDIATE l_insert;

l_insert :=
'INSERT INTO otn_insert (value1, value2)
VALUES (''Dynamic Binding'',:r1, :r2)';

EXECUTE IMMEDIATE l_insert
USING l_value1, l_value2;
END;
/

SELECT *
FROM otn_insert
/
DROP TABLE otn_insert
/
Why is this happening?
Answer:
Junfei,
The problem you encountered points out very precisely one of the reasons that binding should be generally chosen over concatenation as the way to pass variable values into a dynamic SQL string: you will avoid unwanted implicit conversions that give you unintended results.
In the world of dynamic SQL, you construct a string at run-time and then pass that string to Oracle for parsing. So whenever you have a problem with dynamic SQL, the question you must ask yourself before any other is: What string is Oracle parsing? I added a call to DBMS_OUTPUT.PUT_LINE to display the l_insert value just before the EXECUTE IMMEDIATE of the concatenated string.
This is what displayed on my screen:
INSERT INTO otn_insert (context, value1, value2)
VALUES ('Dynamic Concatenation',00001111,11110000)
Notice that there aren't any single quotes around the values assigned to the value1 and value2 columns. You are, in other words, passing numbers, not strings. Leading zeroes in a number are not significant; they are ignored. Furthermore, because your columns are strings and your input values are numbers, Oracle must perform an implicit conversion. Thus, when Oracle deposits this row in the table, the 00001111 value has become 1111, and that is what you see when you query the contents of the table.
NOTE: As an alternative to adding a call to DBMS_OUTPUT.PUT_LINE, if your schema is able to read from the v$sqlarea view, you can also view the SQL statement executed by the above block of code with a query like this:
select sql_text from v$sqlarea
where sql_text like '%INSERT INTO otn_insert%'
and sql_text not like '%v$sqlarea%'
Now, it is true that you could fix this problem of "disappearing zeroes" by placing single quotes around your values in the concatenated insert, as follows:
l_insert :=
'INSERT INTO otn_insert (context, value1, value2) VALUES (''Dynamic Concatenation'','''
|| l_value1
|| ''','''
|| l_value2
|| ''')';
but the resulting code is hard to read and maintain. Binding, as I explain below, is a better way to go. Still, if you choose to perform concatenation and have to deal with embedding single quotes within your dynamic string, remember that in Oracle Database 10g, you can use a user-defined quote character to simplify things, as in:
l_insert :=
q'{INSERT INTO otn_insert (context, value1, value2) VALUES ('Dynamic Concatenation','}'
|| l_value1
|| q'{','}'
|| l_value2
|| q'{')}';
Rather than having to figure out how many single quotes you need to put together to get the correct results, however, you would be much better off simply binding the variable values with the USING clause. Your code is simpler and likely to run more efficiently as well, as even if the values you insert change, the SQL statement stays the same and will therefore not have to be parsed again. In addition, you avoid the problem of code injection, because a malicious user cannot execute undesirable and unexpected code through a bind variable.
Here is an example of performing the insert with bind variables (passed via the USING clause of EXECUTE IMMEDIATE):
DECLARE
l_insert VARCHAR2 (500) :=
q'{INSERT INTO otn_insert (context, value1, value2) VALUES ('Dynamic Binding',:r1, :r2)}';

l_value1 otn_insert.value1%TYPE;
l_value2 otn_insert.value2%TYPE;
BEGIN
l_value1 := '00001111';
l_value2 := '11110000';

EXECUTE IMMEDIATE l_insert
USING l_value1, l_value2;
END;
/