Pavel Mi Pavel Mi -5 years ago 98
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download