CODEWITHSUNDEEP
oracle-databasedatestored-proceduresdate-conversion
Gaurav Sharma
Gaurav Sharma

Reputation: 2848

How do I run procedure in oracle with some date manipulation sql in it

I have created a procedure in oracle as follows:

create or replace PROCEDURE SP_X_AVERAGE
(
    profile out SYS_REFCURSOR,
    rx out SYS_REFCURSOR,
)
as
BEGIN


  open profile for
        select
            avg(to_number(profile_netassets)) AS netassets
        from 
            fgp;


  open rx for
      select 
          avg(to_number(a_price)) as twr 
      from 
          r_x
      where 
            gq_date <= add_months(to_date(sysdate, 'mm/dd/yyyy'), -12);


END SP_X_AVERAGE;

It doesn't run, giving the following error:

ORA-01843: not a valid month

If I remove the where condition in the second sql then it runs successfully.

Altering a session using an sql in the same procedure doesnot work too.

Please help.

I am running this procedure in sql-developer (ubuntu Oneiric 11)

Upvotes: 0

Views: 928

Answers (1)

APC
APC

Reputation: 146309

SYSDATE is already a DATE so you don't need to apply TO_DATE() to it. However, more recent versions of Oracle are tolerant of such things and handle them gracefully.

So that leaves the matter of r_x.gq_date: what data type is that? If it is a string then the chances are you have values in there which will not cast to a date, or at last don't match your default NLS_FORMAT.

"we have to keep it as "VARCHAR2(40 BYTE)" it is having date in it like this : '1/2/2003'"

Bingo. Is that the same as your NLS_DATE_FORMAT? If not you will need to cast the column:

to_date(gq_date, 'mm/dd/yyyy')  <= add_months(sysdate, -12);

This may not solve your problem if the column contains strings which aren't in that format. This is a common side-effect of using strings to hold things which aren't strings.

Upvotes: 3

Related Questions