Pavel Mi Pavel Mi - 5 months ago 9
SQL Question

Oracle. Select and function

I have function like this:

CREATE OR REPLACE FUNCTION IntervalToSec(Run_Duration interval day to second) RETURN NUMBER IS
vSeconds NUMBER ;
BEGIN
SELECT EXTRACT( DAY FROM Run_Duration ) * 86400
+ EXTRACT( HOUR FROM Run_Duration ) * 3600
+ EXTRACT( MINUTE FROM Run_Duration ) * 60
+ EXTRACT( SECOND FROM Run_Duration )
INTO
vSeconds
FROM DUAL ;
RETURN vSeconds ;
END;


That converts interval data to total seconds number
Then i have select query:

select RUN_DURATION from SYS.USER_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'WASTE_MESSAGES_JOB' and LOG_DATE > (systimestamp - INTERVAL '0 00:10:00.0'DAY TO SECOND(1) )
order by LOG_DATE desc;


Output like:+00 00:00:01.000000
Question is how can i pipe query result into function?
Thank you!

Answer

A user defined function doesn't behave any differently to built-in functions, so you call it as you would any other function - to_char, to_date, trunc, round etc.

You can write user-defined functions in PL/SQL, Java, or C to provide functionality that is not available in SQL or SQL built-in functions. User-defined functions can appear in a SQL statement wherever an expression can occur.

For example, user-defined functions can be used in the following:

  • The select list of a SELECT statement
  • ...

So just call the function as part of the query:

select IntervalToSec(RUN_DURATION)
from SYS.USER_SCHEDULER_JOB_RUN_DETAILS
WHERE JOB_NAME = 'WASTE_MESSAGES_JOB'
and LOG_DATE > (systimestamp - INTERVAL '0 00:10:00.0'DAY TO SECOND(1) )  
order by LOG_DATE desc;

As long as the queried column is the same data type the function expects (or can be implicitly converted to that type) it can be passed as the function's argument.