CODEWITHSUNDEEP
oracle-database
user3488955
user3488955

Reputation:

Executing oracle procedure without parameter

create or replace PROCEDURE emav_input_check
   (last_bus_day IN DATE, price_date IN DATE, o_param OUT NUMBER)
AS
bbg_yest       NUMBER;
bbg_today      NUMBER;
drates_yest    NUMBER;
drates_today   NUMBER;
equity_yest    NUMBER;
equity_today   NUMBER;
index_yest     NUMBER;
index_today    NUMBER;
retval         INTEGER;
retval1        INTEGER;
retval2        INTEGER;
retval3        INTEGER;
retval4        INTEGER;
BEGIN

SELECT COUNT (*) INTO bbg_yest
FROM Crd_Own.Bbg_Oiv v
WHERE asof_date = TO_DATE ('last_bus_day', 'mm/dd/yyyy');

SELECT COUNT (*) INTO bbg_today    
FROM Crd_Own.Bbg_Oiv
WHERE asof_date = TO_DATE ('price_date', 'mm/dd/yyyy');


SELECT COUNT (*) INTO drates_yest
FROM pm_own.deposit_rates
WHERE     asof_date = TO_DATE ('last_bus_day', 'mm/dd/yyyy')         
AND currency_code IN (' USD', 'EUR', 'GBP', 'JPY ')
AND TERM_WEEKS = 0;

SELECT COUNT (*) INTO drates_today
FROM pm_own.deposit_rates
WHERE     asof_date = TO_DATE ('price_date', 'mm/dd/yyyy')
AND currency_code IN (' USD', 'EUR', 'GBP', 'JPY ');

SELECT COUNT (*) INTO equity_yest
FROM pm_own.BDVD_FORECAST_EQUITY
WHERE asof_date = TO_DATE ('last_bus_day', 'mm/dd/yyyy');

SELECT COUNT (*) INTO equity_today
FROM pm_own.BDVD_FORECAST_EQUITY
WHERE asof_date = TO_DATE ('price_date', 'mm/dd/yyyy');


SELECT COUNT (*) INTO index_yest
FROM pm_own.BDVD_FORECAST_INDEX
WHERE asof_date = TO_DATE ('last_bus_day', 'mm/dd/yyyy');

SELECT COUNT (*) INTO index_today
FROM pm_own.BDVD_FORECAST_INDEX
WHERE asof_date = TO_DATE ('price_date', 'mm/dd/yyyy');

IF bbg_yest != 0
THEN
SELECT ( (bbg_today - bbg_yest) * 100) / bbg_yest INTO retval1
FROM DUAL;
ELSE
retval1 := 0;
END IF;

IF drates_yest != 0
THEN
SELECT ( (drates_today - drates_yest) * 100) / drates_yest INTO retval2
FROM DUAL;
ELSE
retval2 := 0;
END IF;

IF equity_yest != 0
THEN
SELECT ( (equity_today - equity_yest) * 100) / equity_yest INTO retval3
FROM DUAL;
ELSE
retval3 := 0;
END IF;

IF index_yest != 0
THEN
SELECT ( (index_today - index_yest) * 100) / index_yest INTO retval4
FROM DUAL;
ELSE
retval4 := 0;
END IF;


retval := 0;

IF retval1 < 0 AND ABS (retval1) > 20
THEN
retval := 1;
ELSIF retval2 < 0 AND ABS (retval2) > 20
THEN
retval := 2;
ESIF retval3 < 0 AND ABS (retval3) > 20
THEN
retval := 3;
ELSIF retval4 < 0 AND ABS (retval4) > 20
THEN
retval := 4;
END IF;

o_param := retval;
END;

The code shown above is the procedure. When I execute it as-

declare
return_val number;
BEGIN
pm_own.emav_input_check(TO_DATE('05/19/2014','MM/DD/YYYY'),TO_DATE('05/20/2014','MM/DD/YYYY'),return_val);
dbms_output.put_line(return_val);
END;

I receive the following error:

Error report:

ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "PM_OWN.EMAV_INPUT_CHECK", line 19
ORA-06512: at line 4
01858. 00000 -  "a non-numeric character was found where a numeric was expected"
*Cause:    The input data to be converted using a date format model was
           incorrect.  The input data did not contain a number where a number was
           required by the format model.
*Action:   Fix the input data or the date format model to make sure the
           elements match in number and type.  Then retry the operation.

Any idea how to fix this error?

Upvotes: 0

Views: 193

Answers (2)

mason
mason

Reputation: 32693

SELECT COUNT (*) INTO bbg_yest
FROM Crd_Own.Bbg_Oiv v
WHERE asof_date = TO_DATE ('last_bus_day', 'mm/dd/yyyy');

These are not valid queries. The to_date function expects a string representing a date, and a format string that says what format the first parameter is. Last_bus_day does not match mm/dd/yyyy so it fails. An example of a matching string would be 05/19/2014.

Since your incoming parameter is a date, you should change your queries to be like this:

SELECT COUNT (*) INTO bbg_yest
FROM Crd_Own.Bbg_Oiv v
WHERE asof_date = last_bus_day;

because you don't need to convert the string to a date, then to a date again.

Upvotes: 1

vav
vav

Reputation: 4684

you problem is in how you use your INCOMING parameters. Here is your code:

SELECT COUNT (*) INTO bbg_yest
FROM Crd_Own.Bbg_Oiv v
WHERE asof_date = TO_DATE ('last_bus_day', 'mm/dd/yyyy');

'last_bus_day' is a constant string. Oracle tries to get mm (month as a number) out of it and fails.

your parameter: last_bus_day IN DATE

Therefore you don't need any more conversions:

SELECT COUNT (*) INTO bbg_yest
FROM Crd_Own.Bbg_Oiv v
WHERE asof_date = last_bus_day;

Upvotes: 1

Related Questions