Stored Procedure functions

Who_am_i ()

CREATE OR REPLACE FUNCTION who_am_ireturn varchar2is   l_owner varchar2 (in);   L_name varchar2 (+);   L_lineno number;   L_type VARCHAR2 (30); BEGIN   Who_called_me (L_owner, L_name, L_lineno, l_type);   RETURN L_name; END;

Who_called_me

CREATE OR REPLACE PROCEDURE Who_called_me (owner out VARCHAR2, name out VA RCHAR2, Lineno out number, caller_t ou  T varchar2) as Call_stack varchar2 (4096) default dbms_utility.format_call_stack;  n number;  POS number;  Found_stack BOOLEAN default FALSE;  Line VARCHAR2 (255);  CNT Number: = 0;begin--dbms_output.put_line (call_stack);    Loop N: = InStr (Call_stack, Chr (10));    Exit when (cnt = 3 or n is NULL or n = 0);    -Line: = substr (Call_stack, 1, n-1); --dbms_output.put_line (' line: ' | |    SUBSTRB (line,1,255));    Call_stack: = substr (Call_stack, n + 1);      --if (not found_stack) then if (line like '%handle%number%name% ') then found_stack: = TRUE;    End If;      else cnt: = cnt + 1;       --CNT = 1 is ME--cnt = 2 is MY Caller--cnt = 3 are their Caller if (cnt = 3) Then --lineno: = To_number (substr (line, 13, 6));        --qiands Add 2005-6-16 20:13 pos: = INSTRB (line, ', 1);        Lineno: = To_number (substr (line, POS, 10));        -------------------------------/--line: = substr (line, 21);        --qiands Add 2005-6-16 20:13 Line: = substr (line, POS + 12);        -------------------------------/if (line like ' pr% ') then N: = Length (' procedure ');        elsif (line like ' fun% ') then N: = Length (' function ');        elsif (line like "package body%") then N: = Length (' package body ');        elsif (line like ' pack% ') then N: = Length (' package ');        elsif (line like ' anonymous% ') then N: = Length (' anonymous block ');        else n: = null;        End If;        if (n is not null) then caller_t: = LTrim (RTrim (Upper (substr (line, 1, n-1)));        else caller_t: = ' TRIGGER ';        End If;        Line: = substr (line, NVL (n, 1)); N     : = InStr (Line, '. ');        Owner: = LTrim (RTrim (substr (line, 1, n-1));      Name: = LTrim (RTrim (substr (line, n + 1)));    End If;  End If; End Loop;end;

 p_sys_procedure

CREATE OR REPLACE PROCEDURE p_sys_procedure (v_pro_id in VARCHAR2, V_pro_type in VARCHAR2, v_cycle In VARCHAR2,--processing period V_channel in number,--Channel # V_tag in #,--0, Process starts 1, process ends V_execcycle out VARCHAR 2, V_result_tag out CHAR)-------------------------------------------------------------------------procedure:p_s ys_procedure--Description: Fill in the Process execution log--author:linage_stat--date:2005/04/13--Editor:editplus   2.11-----------------------------------------------------------------------is Iv_err_code VARCHAR2 (20);   Iv_err_log VARCHAR2 (2000); IV_EXECPRO_ID number;   BEGIN V_result_tag: = ' N '; --the process execution period if v_cycle is NULL then if V_pro_type in (' 1 ', ' 2 ') then v_execcycle: = Envinfo.      Previous_day; ELSE v_execcycle: = Envinfo.      Previous_month;   END IF; ELSE IF V_pro_type in (' 1 ', ' 2 ') then v_execcycle: = To_char (To_date (v_cycle, ' yyyymmdd '), ' yyyyMmdd ');      ELSE v_execcycle: = To_char (To_date (v_cycle, ' yyyymm '), ' yyyymm ');   END IF;   END IF; --Determine if the process starts executing if V_tag = 0 then INSERT into Tf_s_pro_log (Execpro_id,session_id,session_user,pro_id,ex ec_cycle,channel_no,begin_date) SELECT f_sys_getseqid (), Envinfo. Sessionid,envinfo.      Session_user,v_pro_id,v_execcycle,v_channel,sysdate from DUAL;      COMMIT;   V_result_tag: = ' Y '; elsif V_tag = 1 then SELECT MAX (execpro_id) to iv_execpro_id from tf_s_pro_log WHERE pro_id = V _pro_id and End_date is NULL and NVL (channel_no,0) = NVL (v_channel,0) and exec_cycle = V_execcycle      ;      V_result_tag: = ' Y ';      Iv_err_code: = ' ORA-0000 '; UPDATE tf_s_pro_log SET (end_date,result_tag,err_code,err_log) = (SELECT sysdate,v_result_tag,iv_err_c      Ode, ' from DUAL ' WHERE execpro_id = iv_execpro_id;   COMMIT;   ELSE V_result_tag: = ' N '; END IF; EXCEPTION when OTHERS THEN Iv_err_log: = SQLERRM;      Iv_err_code: = SUBSTR (Sqlerrm,1,instr (SQLERRM, ': ')-1); INSERT into Tf_s_pro_log (Execpro_id,session_id,session_user,pro_id,exec_cycle,channel_no,begin_date,end_date, Result_tag,err_code,err_log) SELECT f_sys_getseqid (), Envinfo. Sessionid,envinfo.      Session_user,v_pro_id,v_cycle,v_channel,sysdate,sysdate, ' N ', iv_err_code,iv_err_log from DUAL; COMMIT; END;

Stored Procedure functions