This discussion is archived
6 Replies Latest reply: Nov 22, 2012 6:32 AM by Solomon Yakobson RSS

Return the past timestamp values on the base of input hour to the user func

hitgon Expert
Currently Being Moderated
Dear Oracle Gurus

Could you please direct me to resolve issue while modified the user function code for returns the values as timestamps

---function code

create or replace
function fun_test_timestamp(P_HOUR varchar2) return varchar2
is
sql_stmt varchar2(1000);
begin

sql_stmt:='SELECT CURRENT_TIMESTAMP - INTERVAL ''' || P_HOUR || ''' HOUR FROM DUAL;';
--execute immediate sql_stmt into my_time;
dbms_output.put_line(sql_stmt);
return sql_stmt;
end;
/


Input:-
select fun_test_timestamp('5') from dual;

Output:-

SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;


Modified Fun Code:

create or replace
function fun_test_timestamp(P_HOUR varchar2) return timestamp
is
sql_stmt varchar2(1000);
my_time timestamp;
begin

sql_stmt:='SELECT CURRENT_TIMESTAMP - INTERVAL ''' || P_HOUR || ''' HOUR FROM DUAL;';
execute immediate sql_stmt into my_time;
dbms_output.put_line(sql_stmt);
return my_time;
end;
/


Input:-
select fun_test_timestamp('5') from dual;

Output:-

ORA-00911: invalid character
ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8
00911. 00000 - "invalid character"
*Cause:    identifiers may not start with any ASCII character other than
letters and numbers. $#_ are also allowed after the first
character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes
(q'#...#') cannot use spaces, tabs, or carriage returns as
delimiters. For all other contexts, consult the SQL Language
Reference Manual.
*Action:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 1. Re: Return the past timestamp values on the base of input hour to the user func
    BluShadow Guru Moderator
    Currently Being Moderated
    sql_stmt:='SELECT CURRENT_TIMESTAMP - INTERVAL ''' || P_HOUR || ''' HOUR FROM DUAL;';
    change to:
    sql_stmt:='SELECT CURRENT_TIMESTAMP - INTERVAL ''' || P_HOUR || ''' HOUR FROM DUAL';
    you shouldn't include ";" at the end of the SQL statement

    Edit to add... you should also consider binding in the value rather than concatenating it in.
  • 2. Re: Return the past timestamp values on the base of input hour to the user func
    Solomon Yakobson Guru
    Currently Being Moderated
    Semi-colon at the end of SQL statement is SQL*Plus way to indicate end of statement. It is not needed and creates issue when used in dynamic SQL. Change:
    sql_stmt:='SELECT CURRENT_TIMESTAMP - INTERVAL ''' || P_HOUR || ''' HOUR FROM DUAL<font size=3 color=red>;</font>';
    to
    sql_stmt:='SELECT CURRENT_TIMESTAMP - INTERVAL ''' || P_HOUR || ''' HOUR FROM DUAL';
    SY.
  • 3. Re: Return the past timestamp values on the base of input hour to the user func
    hitgon Expert
    Currently Being Moderated
    Thanks a lots, Sir

    SELECT fun_test_timestamp('5') FROM DUAL;

    18-JAN-00 11.24.27.552142000 PM

    Regards
    Hitgon
  • 4. Re: Return the past timestamp values on the base of input hour to the user func
    hitgon Expert
    Currently Being Moderated
    Hi,

    This is the correct code

    Workaround-1

    create or replace
    function fun_test_timestamp(P_HOUR varchar2) return timestamp
    is
    sql_stmt varchar2(1000);
    my_time timestamp;
    begin

    sql_stmt:='SELECT CURRENT_TIMESTAMP - INTERVAL ' ||chr(39) ||P_HOUR||chr(39) || ' HOUR FROM DUAL';
    execute immediate sql_stmt into my_time;
    dbms_output.put_line(sql_stmt);
    return my_time;
    end;
    /

    Workaround-2

    create or replace
    function fun_test_timestamp(P_HOUR varchar2) return timestamp
    is
    sql_stmt varchar2(1000);
    my_time timestamp;
    begin

    sql_stmt:='SELECT CURRENT_TIMESTAMP - INTERVAL ''' ||P_HOUR||''' HOUR FROM DUAL';
    execute immediate sql_stmt into my_time;
    dbms_output.put_line(sql_stmt);
    return my_time;
    end;
    /

    Input:- SELECT fun_test_timestamp('5') FROM DUAL
    Output:- 18-JAN-00 11.28.02.466058000 PM

    Regards
    Hitgon
  • 5. Re: Return the past timestamp values on the base of input hour to the user func
    Nicosa Expert
    Currently Being Moderated
    Hi,

    Not sure to understand what you want, but my feeling is that the query you're doing "execute immediate" on, shouldn't end with semi-colon :
    create or replace
    function fun_test_timestamp(P_HOUR varchar2) return varchar2
    is
    sql_stmt varchar2(1000);
    begin
    
    sql_stmt:='SELECT CURRENT_TIMESTAMP - INTERVAL ''' || P_HOUR || ''' HOUR FROM DUAL';
    execute immediate sql_stmt into my_time;
    dbms_output.put_line(sql_stmt);
    return sql_stmt;
    end;
    /
  • 6. Re: Return the past timestamp values on the base of input hour to the user func
    Solomon Yakobson Guru
    Currently Being Moderated
    BluShadow wrote:

    Edit to add... you should also consider binding in the value rather than concatenating it in.
    OP should consider getting rid of dynamic sql alltogether:
    SQL> create or replace
      2    function fun_test_timestamp(
      3                                p_hour number
      4                               )
      5    return timestamp 
      6    is
      7    begin 
      8        return current_timestamp - numtodsinterval(p_hour,'hour');
      9  end;
     10  /
    
    Function created.
    
    SQL> select fun_test_timestamp(5) from dual
      2  /
    
    FUN_TEST_TIMESTAMP('5')
    ----------------------------------------------------------------------
    22-NOV-12 04.31.13.968000000 AM
    
    SQL> 
    And, in fact, no function is needed, just plain current_timestamp - numtodsinterval(n,'hour').

    SY.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points