Oracle dynamic SQL parameters cannot be executed due to date type

When I was writing a stored procedure, I used the start time and end time to create a view. So I wrote a process like this:

Procedure prc_CreateViewOBookTcert (p_startdate in date, -- start date of statistics
P_enddate in date) -- end date of statistics
Is
V_ SQL varchar2 (2000 );
Begin
V_ SQL: =
'Create or replace view v_obook_tcert
Select * from (
Select
Nvl (t11.region, nvl (t22.region, t33.region) region,
Nvl (t11.site, 0) site,
Nvl (t22.useland, 0) useland,
Nvl (t33.project, 0) project,
Nvl (t11.site, 0) + nvl (t22.useland, 0) + nvl (t33.project, 0), 0) subtotal -- calculate the sum of site selection, land use, project, and
From
(Select t1. project region, count (t1. project region) site -- site statistics
From ts_project t1
Where t1.ref _ business_id = 1 and t1. date of issuance between '| p_startdate | 'and' | p_enddate |'
Group by t1. project region) t11
Full join
(Select t2. project region, count (t2. project region) useland-Statistical land
From ts_project t2
Where t2.ref _ business_id = 3 and t2. date of issuance between '| p_startdate | 'and' | p_enddate |'
Group by t2. project region) t22
On t11.region = t22.region
Full join
(Select t3. project region, count (t3. project region) project -- Statistical project
From ts_project t3
Where t3.ref _ business_id in (5, 9, 11) and t3. Date of issue between '| p_startdate | 'and' | p_enddate |'
Group by t3. project region) t33
On t22.region = t33.region )';
Execute immediate v_ SQL;
End prc_CreateViewOBookTcert;

The yellow area is the key. The syntax of this process is correct. It is also effective to replace the parameter variable value with the actual value. However, it is invalid during the call. That is to say, it is invalid to replace the actual value with a variable. In fact, this situation often occurs, and this error occurs during collection query. Today, I suddenly thought about whether it was a space or not, so I changed it to this:

-- Two-certificate View
Procedure prc_CreateViewOBookTcert (p_startdate in date, -- start date of statistics
P_enddate in date) -- end date of statistics
Is
V_ SQL varchar2 (2000 );
Begin
V_ SQL: =
'Create or replace view v_obook_tcert
Select * from (
Select
Nvl (t11.region, nvl (t22.region, t33.region) region,
Nvl (t11.site, 0) site,
Nvl (t22.useland, 0) useland,
Nvl (t33.project, 0) project,
Nvl (t11.site, 0) + nvl (t22.useland, 0) + nvl (t33.project, 0), 0) subtotal -- calculate the sum of site selection, land use, project, and
From
(Select t1. project region, count (t1. project region) site -- site statistics
From ts_project t1
Where t1.ref _ business_id = 1 and t1. Date of issue between '| chr (39) | p_startdate | chr (39) | 'and' | chr (39) | p_enddate | chr (39) |'
Group by t1. project region) t11
Full join
(Select t2. project region, count (t2. project region) useland-Statistical land
From ts_project t2
Where t2.ref _ business_id = 3 and t2. Date of issue between '| chr (39) | p_startdate | chr (39) | 'and' | chr (39) | p_enddate | chr (39) |'
Group by t2. project region) t22
On t11.region = t22.region
Full join
(Select t3. project region, count (t3. project region) project -- Statistical project
From ts_project t3
Where t3.ref _ business_id in (5, 9, 11) and t3. Date of issue between '| chr (39) | p_startdate | chr (39) | 'and' | chr (39) | p_enddate | chr (39) |'
Group by t3. project region) t33
On t22.region = t33.region )';
Execute immediate v_ SQL;
End prc_CreateViewOBookTcert;

We can see that a chr (39) is added before and after the variable (there are spaces before). This function is used to get the corresponding characters through the ascii code, 39 corresponds to a space. After modification, the view can be created dynamically.

Most of the time, if everything in the syntax is okay, but you can't execute it dynamically or create it, think about whether it's a problem, for example, this can also happen when you use the set query after passing parameters to the cursor dynamically.

At present, I still don't know why the space entered by pressing the Space key cannot be used in this case, but what is the reason why the space can be used through chr (39. I also hope to get help from you.