Thanks for the question, BALASUBRAMANIAN.
Asked: June 24, 2001 - 10:23 pm UTC
Last updated: April 25, 2006 - 11:00 am UTC
Version: 8.0.6/8.1.5
Viewed 10K+ times! This question is 
You Asked
Hi Tom,
When I am executing a function to_date(date),it is working in the SQL prompt. But when I am executing the same function inside a stored procedure, it is giving the error 'PLS-00222: no function with name 'TO_DATE' exists in this scope'. What could be the reason ? But If I am using TRUNC(date) function it is working inside as well as outside the procedure. Why?
I will type my full procedure as follows:
QL> Create or Replace Procedure SP_ADDDEL
2 (from_date in date, to_date in date) IS
3 Cursor cur_mas is
4 select distinct empcode,emp_name,emp_grade,add_type
5 from tadd_del order by empcode,add_type;
6
7 Cursor cur_child(e_code varchar2,e_name varchar2,e_grade varchar2,a_type char)
8 is select from_date,to_date,days from tadd_del where empcode = e_code and
9 emp_name = e_name and emp_grade = e_grade and add_type = a_type;
10
11 ls_code char(5);
12 ls_name varchar2(40);
13 ls_grade varchar2(50);
14 ls_rem varchar2(250);
15 ls_type char(1);
16 ld_from date;
17 ld_to date;
18 li_days integer;
19 li_tmp integer;
20 ld_tfrom date;
21 ld_tto date;
22 ld_today date;
23 Begin
24 select to_date(sysdate) into ld_today from dual;
25 Open cur_mas;
26 Loop
27 Fetch cur_mas into ls_code,ls_name,ls_grade,ls_type;
28 Exit When cur_mas%notfound;
29 Open cur_child(ls_code,ls_name,ls_grade,ls_type);
30 li_days := 0;
31 ls_rem := '';
32 Loop
33 li_tmp :=0;
34 Fetch cur_child into ld_tfrom,ld_tto,li_tmp;
35 Exit When cur_child%notfound;
36 li_days := li_days + li_tmp;
37 if ld_tfrom = ld_tto then
38 ls_rem := ls_rem ||' '|| to_char(ld_tfrom) || ',';
39 else
40 ls_rem := ls_rem ||' '|| to_char(ld_tfrom) || ' to '||to_char(ld_tto) ||',';
41 end if;
42 End Loop;
43 Close cur_child;
44 ls_rem := substr(ls_rem,1,length(ls_rem) -1);
45 Insert into tadd_del_report values (ls_code,ls_name,ls_grade,li_days,ld_today,ls_rem,ls_type)
;
46 End Loop;
47 Close cur_mas;
48 Commit;
49 End;
50 /
Warning: Procedure created with compilation errors.
SQL> show error;
Errors for PROCEDURE SP_ADDDEL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
24/1 PL/SQL: SQL Statement ignored
24/8 PLS-00222: no function with name 'TO_DATE' exists in this scope
SQL> select to_date(sysdate) from dual;
TO_DATE(S
---------
23-JUN-01
SQL> select trunc(sysdate) from dual;
TRUNC(SYS
---------
23-JUN-01
and Tom said...
Well, the fact is that:
to_date(sysdate)
is a sure way to corrupt a date! In SQL -- it is converting SYSDATE to a STRING (there is NO to_date function that takes a DATE as an input and SYSDATE is already a DATE). So, it converts sysdate into the string '25-JUN-01'. Now, it uses the default date mask to convert that into a date. The problem is the century is typically GONE by now and you may get different results on different databases.
Lose the TO_DATE(sysdate) -- if you want a date without a TIME, simply use TRUNC(sysdate).
PLSQL is not doing this implicit conversion when it type checks the sql statement -- since there is no to_date that takes a date, it fails.
To make your coding easier, just code:
...
ld_today date DEFAULT trunc(sysdate);
....
lose the select into.
followup to comment
it won't cause problems but it could be considered a bad idea (sort of confusing having a column named to_date)
ops$tkyte@ORA8I.WORLD> create table t ( to_date date );
Table created.
ops$tkyte@ORA8I.WORLD> insert into t values ( sysdate );
1 row created.
ops$tkyte@ORA8I.WORLD> begin
2 for x in ( select to_date from t )
3 loop
4 dbms_output.put_line( x.to_date );
5 end loop;
6 end;
7 /
25-JUN-01
PL/SQL procedure successfully completed.
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment