Have Geek Brain

Why am I getting Bind Variable "DeliveryDate_Variable" NOT DECLARED (completely new TO Oracle)

I have the following script in Oacle I don't understand why I am getting

Bound variable "DeliveryDate_Variable" NOT STATED

Everything looks fine 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
+2


a source to share


4 answers


It's definitely an odd SQL * quirk plus that the list of valid data types for variables doesn't include DATEs.



The solution is to declare the "date" variables as varchar2 (9) or barchar2 (18) (depending on whether we want to include the time element) and then list the TO_DATE () variables as needed.

+3


a source


I managed to find the problem, for some reason Oracle didn't like casting ov sting to date (per 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
+1


a source


I could be wrong, but I thought you need to initialize variables without functions, i.e. I thought TO_DATE was not allowed.

I think this is because the VARIABLE declaration is not part of SQL - it is SQLPlus specific and you cannot cast back and forth between the two.

0


a source


The data type is date

not allowed for variables declared in SQLPlus. You have to create a variable like varchar

and then use to_date

. SQLPlus Variable Syntax

0


a source






More articles:


All Articles