Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION Control_Reports_Pg.daily_control_reports_fn () RETURNS bigint AS $body$
- DECLARE
- V_Return smallint;
- C_Function_Name varchar(200) := 'Daily_Control_Reports_Fn';
- BEGIN
- V_Return := control_reports_pg.control_reports_fn(1::bigint,'daily_log_control_file.txt'::text,'/PostgreSQL/comb_logs'::text);
- RETURN V_Return;
- EXCEPTION
- WHEN OTHERS THEN
- PERFORM control_reports_pg.send_error_mail(SQLERRM ,C_Function_Name);
- RETURN 1;
- END;
- $body$
- LANGUAGE PLPGSQL
- SECURITY DEFINER
- ;
- CREATE OR REPLACE FUNCTION Control_Reports_Pg.control_reports_fn (P_Report_Type bigint, P_Log_File_Name text,C_Path text) RETURNS bigint AS
- $body$
- DECLARE
- V_Return smallint;
- V_Function_Return smallint:=1;
- C_Daily_Reports varchar(300);
- C_Function_Name varchar(200) := 'Control_Reports_Fn';
- Rec_Daily_Reports CONTROL_REPORTS%ROWTYPE;
- G_Log_File_Type UTL_FILE.FILE_TYPE;
- BEGIN
- -- Open Log File
- --PERFORM control_reports_pg.send_error_mail(C_Path ,C_Function_Name);
- G_Log_File_Type := UTL_FILE.FOPEN(C_Path, P_Log_File_Name,'w');
- C_Daily_Reports := 'SELECT REPORT_ORDER,PROCEDURE_NAME,DIRECTORY_NAME,FILE_NAME,TITLE FROM CONTROL_REPORTS WHERE RUN_FLAG=1 AND REPORT_TYPE=' || P_Report_Type || ' ORDER BY REPORT_ORDER';
- -- RAISE NOTICE '%',C_Daily_Reports;
- FOR Rec_Daily_Reports IN EXECUTE C_Daily_Reports LOOP
- PERFORM UTL_FILE.PUT_LINE(G_Log_File_Type,'Procedure_Name = '||Rec_Daily_Reports.Procedure_Name);
- PERFORM UTL_FILE.PUT_LINE(G_Log_File_Type,'start time= '|| to_char(clock_timestamp(),'dd/mm/yyyy hh24:mi:ss'));
- V_Return := control_reports_pg.chrg_in_bill_not_in_crm_fn(Rec_Daily_Reports.Directory_Name,
- Rec_Daily_Reports.File_Name,
- Rec_Daily_Reports.Title);
- IF V_Return = 0 THEN
- V_Function_Return := 0;
- END IF;
- ............
- mydb=> select control_reports_pg.control_reports_fn(1::bigint,'daily_log_control_file.txt'::text,'/PostgreSQL/comb_logs'::text);
- NOTICE: FUNC : Control_Reports_Fn, SQLERRM: invalid input syntax for integer: "Chrg_In_Bill_Not_In_Crm_Fn"
- NOTICE: Message : invalid input syntax for integer: "Chrg_In_Bill_Not_In_Crm_Fn", Func : Control_Reports_Fn
- CONTEXT: SQL statement "SELECT control_reports_pg.send_error_mail(SQLERRM ,C_Function_Name)"
- PL/pgSQL function control_reports_pg.control_reports_fn(bigint,text,text) line 339 at PERFORM
- chrg_in_bill_not_in_crm_fn
- ----------------------------
- 0
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement