6 Replies Latest reply: Nov 22, 2012 8:32 AM by Solomon Yakobson RSS

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

    hitgon
      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
          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
            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
              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
                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-Oracle
                  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
                    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.