Why do I get Bind Variable "DeliveryDate_Variable" is NOT DECLARED(Completely New TO Oracle)
I have the following script in Oacle I do not understand why i get
Bind Variable "DeliveryDate_Variable" is NOT DECLARED
Everything looks ok to me
VARIABLE RollingStockTypeId_Variable NUMBER := 1;
VARIABLE DeliveryDate_Variable DATE := (to_date('2010/8/25:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
SELECT DISTINCT
rs.Id,
rs.SerialNumber,
rsc.Name AS Category,
(SELECT COUNT(Id) from ROLLINGSTOCKS WHERE ROLLINGSTOCKCATEGORYID = rsc.id) as "Nu开发者_如何学Cmber Owened",
(SELECT COUNT(rs.Id)
FROM ROLLINGSTOCKS rs
WHERE rs.ID NOT IN( select RollingStockId
from ROLLINGSTOCK_ORDER
WHERE :DeliveryDate_Variable BETWEEN DEPARTUREDATE AND DELIVERYDATE)
AND rs.RollingStockCategoryId IN (Select Id
from RollingStockCategories
Where RollingStockTypeId = :RollingStockTypeId_Variable)
AND rs.RollingStockCategoryId = rsc.Id) AS "Number Available"
FROM ROLLINGSTOCKS rs
JOIN RollingStockCategories rsc ON rsc.Id = rs.RollingStockCategoryId
WHERE rs.ID NOT IN(
select RollingStockId
from ROLLINGSTOCK_ORDER
WHERE :DeliveryDate_Variable BETWEEN DEPARTUREDATE AND DELIVERYDATE
)
AND rs.RollingStockCategoryId IN
(
Select Id
from RollingStockCategories
Where RollingStockTypeId = :RollingStockTypeId_Variable
)
ORDER BY rsc.Name
It is a definitely odd quirk of SQL*plus that the list of allowable datatypes for variables does not include DATE.
The solution is to declare "date" variables as varchar2(9) or barchar2(18) (depending on whether we want include the time element) and then cast the variables TO_DATE() as necessary.
I Managed to find the problem, for some reason Oracle didn't like the casting ov the sting to date (in line)
This is how i changed it
VARIABLE RollingStockTypeId_Variable NUMBER;
exec :RollingStockTypeId_Variable := 2;
VARIABLE DeliveryDate_Variable VARCHAR2(30);
exec :DeliveryDate_Variable := '2010/8/25:12:00:00AM';
SELECT DISTINCT
rs.Id,
rs.SerialNumber,
rsc.Name AS Category,
(SELECT COUNT(Id) from ROLLINGSTOCKS WHERE ROLLINGSTOCKCATEGORYID = rsc.id) as "Number Owened",
(SELECT COUNT(rs.Id)
FROM ROLLINGSTOCKS rs
WHERE rs.ID NOT IN( select RollingStockId
from ROLLINGSTOCK_ORDER
WHERE (to_date(:DeliveryDate_Variable, 'yyyy/mm/dd:hh:mi:ssam')) BETWEEN DEPARTUREDATE AND DELIVERYDATE)
AND rs.RollingStockCategoryId IN (Select Id
from RollingStockCategories
Where RollingStockTypeId = :RollingStockTypeId_Variable)
AND rs.RollingStockCategoryId = rsc.Id) AS "Number Available"
FROM ROLLINGSTOCKS rs
JOIN RollingStockCategories rsc ON rsc.Id = rs.RollingStockCategoryId
WHERE rs.ID NOT IN(
select RollingStockId
from ROLLINGSTOCK_ORDER
WHERE (to_date(:DeliveryDate_Variable, 'yyyy/mm/dd:hh:mi:ssam')) BETWEEN DEPARTUREDATE AND DELIVERYDATE
)
AND rs.RollingStockCategoryId IN
(
Select Id
from RollingStockCategories
Where RollingStockTypeId = :RollingStockTypeId_Variable
)
ORDER BY rsc.Name
I could be wrong, but I thought that you had to initialize VARIABLES without functions -- that is, I thought that TO_DATE was not allowed.
I think it is because the VARIABLE declaration is not really part of SQL -- it is specific to SQLPlus, and you can't drop back and forth between the tw.
The date
datatype is not allowed for variables declared in SQLPlus. You have to create the variable as varchar
and then use to_date
. SQLPlus variable syntax
精彩评论