Why do I get Bind Variable "DeliveryDate_Variable" is NOT DECLARED(Completely New TO Oracle)

Question

Hi,

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 "Number 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                       

Answer 1

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       

Answer 2

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.

Answer 3

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.

Answer 4

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