Oracle user-defined functions

Determine the task expiration time:

Code
Create   Or   Replace   Function Geturgentstate (m_taskid Varchar2 ,
M_sendtime date,
M_flag Varchar2 )
Return   Varchar2   Is
Mydate date;
Expiretime date;
Strsql Varchar2 ( 200 );
Begin
Mydate: = M_sendtime;

Strsql:= 'Select max (expiretime) from t_wf_supervise where taskid =''' |
M_taskid| '''';

ExecuteImmediate strsql
IntoExpiretime;

-- It is normal if there is no expiration time
If Expiretime Is   Null   Then
If M_flag =   ' String '   Then
Return   ' Normal ' ;
End   If ;
If M_flag =   ' IMG '   Then
Return   ' Cb_execute.gif ' ;
End   If ;
End   If ;

-- If a task is not sent, the current time is determined.
If M_sendtime Is   Null   Then
Mydate: = Sysdate;
End   If ;

If Expiretime < Mydate Then
If M_flag =   ' String '   Then
Return   ' Expired ' ;
End   If ;
If M_flag =   ' IMG '   Then
Return   ' Cb_limit.gif ' ;
End   If ;
End   If ;

-- Task alert for less than 3 days
If Expiretime - Mydate <   3   Then
If M_flag =   ' String '   Then
Return   ' Warning ' ;
End   If ;
If M_flag =   ' IMG '   Then
Return   ' Cb_warning.gif ' ;
End   If ;
Else
If M_flag =   ' String '   Then
Return   ' Normal ' ;
End   If ;
If M_flag =   ' IMG '   Then
Return   ' Cb_execute.gif ' ;
End   If ;
End   If ;

End;

 

Query other table data:

Code
Create   Or   Replace   Function Getprenode (m_pretaskid Varchar2 ) Return   Varchar2   Is
Nodename Varchar2 ( 50 );
Strsql Varchar2 ( 200 );
Begin
If M_pretaskid Is   Null   Then
Return   '' ;
End   If ;
Strsql: =   ' Select max (nodename) from t_wf_tasklist where taskid = '''   |
M_pretaskid |   '''' ;

ExecuteImmediate strsql
IntoNodename;
ReturnNodename;
End;

 

Format the title output:

Code
Create   Or   Replace   Function Formattitle (m_title Varchar2 ,
M_length Number ,
M_fillchar Varchar2 ) Return   Varchar2   Is
Begin
If Lengthb (m_title) > M_length * 2   Then
Return Substr (m_title, 0 , M_length) | M_fillchar;
Else
Return M_title;
End   If ;
End ;