CODEWITHSUNDEEP
sqloracle-sqldeveloper
user1279970
user1279970

Reputation:

Function in Oracle to Get time along with a Display Message :)

Hey I tried It for a lot of time but I cannot fix this simple program to work for my personal website.I want to extract the current time and display a message with it.

Like The current time is: 12:00 am

Here is my code.

Create Function A1SF_GETTIME Is

    Temptime Char(20);   
    Message  Varchar2(20) := 'The Current Time is :';
    Begin
    Select to_char(sysdate, 'HH24:MI:SS') As "Current Time"   
    Into   TempTime   
    From dual;
    dbms_output.put_line(Message || ' ' || TempTime);      
End;

Upvotes: 1

Views: 5119

Answers (4)

Big Ed
Big Ed

Reputation: 1244

How about this?

to_char(sysdate, '"The current time is "hh24:mi:ss')

as in 

SQL> select to_char(sysdate, '"The current time is "hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'"THECURRENT
----------------------------
The current time is 09:16:08

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231791

A function needs to have a RETURN clause in the function definition. And it has to have a RETURN statement so that the function actually returns something. If you fix that, along with the fact that others have pointed out that your local variable Message is too small, you could do something like this

SQL> ed
Wrote file afiedt.buf

  1  Create or replace Function A1SF_GETTIME
  2    return varchar2
  3  Is
  4    l_time    varchar2(10) := to_char( sysdate, 'HH24:MI:SS' );
  5    l_message Varchar2(30) := 'The Current Time is : ' || l_time;
  6  Begin
  7    dbms_output.put_line( l_message );
  8    return l_message;
  9* End;
SQL> /

Function created.

SQL> select a1sf_gettime from dual;

A1SF_GETTIME
--------------------------------------------------------------------------------
The Current Time is : 14:09:47

Upvotes: 2

paxdiablo
paxdiablo

Reputation: 882226

Not sure what the error is since you haven't shown it :-) but you can greatly simplify your code with something like:

create function A1SF_GETTIME is
    Temptime Char(50);
begin
    select 'The current time is ' || to_char (sysdate, 'HH24:MI:SS')
        into TempTime from dual;
    dbms_output.put_line (TempTime);
end;

You'll have to increase the size of the TempTime buffer since it holds all the data, and 50 should be big enough for the resultant string. This is offset by the fact that you can get rid of Message altogether.

Upvotes: 1

Francis P
Francis P

Reputation: 13665

Your code wasn't so bad... you're string buffer was simply too small for your message!

This would work:

    Message VARCHAR2(21)

Upvotes: 1

Related Questions