Stored Procedure Problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Stored Procedure Problem

  1. #1
    Join Date
    Jun 2001
    Location
    Germany
    Posts
    1

    Question

    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

  2. #2
    Join Date
    Oct 2000
    Posts
    90
    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;


  3. #3
    Join Date
    Aug 2000
    Posts
    462
    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

  4. #4
    Join Date
    May 2001
    Posts
    11

    Cool 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


  5. #5
    Join Date
    Jun 2001
    Location
    NJ
    Posts
    118
    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
  •  


Click Here to Expand Forum to Full Width