Oracle Stored Procedures

Create or replace procedure p_insert_caz_lims as V_sqlcode tlimserrorinfo. Error_code%type; V_msg Tlimserrorinfo.error_msg%type;begininsert into Tlimserrorinfo (error_id, Error_code, error_msg, Error_date, Error_program) VALUES (Seq_tlimserrorinfo.nextval, ' 0 ', ' execution start ', sysdate, ' p_insert_caz_lims '); DECLARE CURSOR lims_info is select ZZDM, ZZMC, Cyd, YPMC, FXXM, R_units, QY, To_char (R_value) as R_value, To_char (R_text) As R_text, To_char (CYSJ) as Rtime, To_char (RZSJ) as Dtime, Batch_name, R_out_of_range, Mv_range, grade from [EMAIL PR Otected]_caz. Regress. Rdbms. DEV. RSI oracle.com where To_date (RZSJ, ' Yyyy-mm-dd HH24:mi:ss ') > Trunc (sysdate)-2 and RZSJ are NOT null; Limsstop Lims_info%rowtype; Data_count number (10); Begin open Lims_info; Loop fetch lims_info into limsstop; Exit when Lims_info%notfound; Begin select COUNT (ID) into Data_count from tlimsoriginaldata where rtime = To_date (Limsstop.rtime, ' Yyyy-mm-dd HH24:mi:ss ') and Dtime = To_date (Limsstop.dtime, ' yyyy-mm-dd HH24:mi:ss ') and LocationID = LIMSSTOP.ZZDM and LocationName = LIMSSTOP.ZZMC and Samplingpoint = Limsstop.cyd and samplename = Limssto P.YPMC and rname = limsstop.fxxm and qy = ' CAZ '; If Data_count = 0 then insert into Tlimsoriginaldata (ID, LocationID, locationname, Samplingpoint, Samplename, Rname, unit S, qy, Rvalue, Rtime, Dtime, Batch_name, Rtext, R_out_of_range, Mv_range, Grade) values (Seq_tlimsoriginaldata.nextval, Li MSSTOP.ZZDM, LIMSSTOP.ZZMC, Limsstop.cyd, LIMSSTOP.YPMC, LIMSSTOP.FXXM, Limsstop.r_units, limsstop.qy, Limsstop.r_ Value, To_date (Limsstop.rtime, ' yyyy-mm-dd hh24:mi:ss '), To_date (Limsstop.dtime, ' yyyy-mm-dd hh24:mi:ss '), Limsstop.batch_name, Limsstop.r_text, Limsstop.r_out_of_range, Limsstop.mv_range, Limsstop.grade); End If; End End Loop; Close Lims_info; Insert into Tlimserrorinfo (error_id, Error_code, Error_msg, error_date, Error_program) VALUES (seq_ Tlimserrorinfo.nextval, ' 1 ', ' execution success ', Sysdate, ' p_insert_caz_lims '); Exception when others then v_sqlcode: = Sqlcode; V_msg: = SQLERRM; Insert into Tlimserrorinfo (error_id, Error_code, Error_msg, error_date, Error_program) VALUES (seq_ Tlimserrorinfo.nextval, V_sqlcode, v_msg, Sysdate, ' p_insert_caz_lims '); End;end P_insert_caz_lims;