-
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.
Oracle DBA and Developer
-
cool
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|