Stored Procedure Problem

I'm trying to capture the date and time using a stored procedure. Everything compiles ok, but I'm having problems finding what is wrong. Can someone please let me know why I get this error when calling the procedure?

====================================================
create or replace procedure get_time
(
v_date out date)
as heute date;
BEGIN
select to_char(sysdate, 'MMDDYY') into heute from dual;
v_date := heute;
end get_time;

-----------------------------------------------------
declare
datum date;
begin
get_time(datum);
end;

-----------------------------------------------------
declare
*
ERROR in Line 1:
ORA-01861: Literal does not match format string
ORA-06512: in get_time, line 6
ORA-06512: in line 4

=========================================================

All help is greatly appreciated.

CIAO

JASSMAN

Is there a reason why you want to do it in a procedure ?, because you can just do



declare
datum date;
begin
datum := sysdate;
end;

The problem with your procedure is that the NLS_DATE_FORMAT in the session where this is run, is different than 'MMDDYY'. As a result, there is a type mismatch between the date you pass out of your procedure, and the type of the date variable you wish to put the return value into.

Either change NLS_DATE_FORMAT:

alter session set nls_date_format = 'MMDDYY';

OR

pass the current format

OR

pass a date-like string, and convert after you get it out.

hi jasman,

The problem is simple that u are converting the sysdate using to_char function then it will become a character
string before storing into date variable you have to reconvert
it into date format the problem is in procedure's SELECT
statement
the solution is

create or replace procedure get_time
(
v_date out date)
as heute date;
BEGIN
select to_date(to_char(sysdate, 'MMDDYY'),'MMDDYY') into heute from dual;
v_date := heute;
end get_time;

agin run the above script and then execute the pl/sql block
set serveroutput on

declare
datum date;
begin
get_time(datum);
dbms_output.put_line(datum);
end;

That solves ur problem
ok
bye
sarath

HI Sarath,
What your have posted doesn't work actually . If i am not wrong, jassman wants the date in 'mmddyy' format
When you assign to a variable which is declared as date. Then, if you display it, PL/SQL displays it in the default format 'dd-mon-yy' unless you change the nls_date_format to the required format. SO, your dbms_output will still show the default oracle date format unless you change it.
Thanks.
Giri