Skip to Main Content
  • Questions
  • pls-00222: no function with 'to_date' exists in this scope

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Charudatta.

Asked: April 06, 2017 - 8:13 am UTC

Last updated: April 07, 2017 - 2:34 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

I have written below query as embedded SQL in Cobol. the procobol compiler giver error -
pls-00222: no function with 'to_date' exists in this scope

But the query works fine in oracle SQL developer

Note : to_date is a column of PAY_SUM table. Is this an issue? if yes, then what is the solution?

select * from pay_sum
where ctrb_no = 94272317
and to_date(to_date,'YYYYMMDD') - add_months(to_date(from_date,'YYYYMMDD'),trunc(months_between(to_date(to_date,'YYYYMMDD'),to_date(from_date,'YYYYMMDD')))) = 30




and Chris said...

I'm not familiar with Pro Cobol, but having a column named "to_date" seems a likely cause. Which points to a couple of highly recommended changes:

- Store dates as dates, NOT strings! Then you don't have to to_date them...
- Don't use reserved words for database object names. Rename it to something else.

Assuming you aren't able to fix either of these issues immediately, you may be able to get around this by fully qualifying your references to "to_date".

This function is defined in standard, so place "sys.standard" before the function call and the table alias before the column name:

create table t (
  to_date varchar2(10)
);

insert into t values ('20170101');

select * from t
where  sys.standard.to_date(t.to_date, 'YYYYMMDD') = trunc(sysdate, 'y');

TO_DATE   
20170101 

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Charudatta Dhakras, April 06, 2017 - 12:45 pm UTC

Thanks a lot, it worked.

Really? A column named the same as an Oracle built[-in function?

Allen R Marshall, April 06, 2017 - 1:55 pm UTC

OMG. Please change this column name from TO_DATE. Why not name it DELETE or UPDATEor even better, TRUNCATE....


Connor McDonald
April 07, 2017 - 2:34 am UTC

:-)