Declare
--type definition
strSQL string (500);
V_errorcode number; --Variable to hold the error message code
V_errortext VARCHAR2 (200); --Variable to hold the error message text
Cursor C_eventstate
Is
Select Campaignid,phonenumber,policynumber from Mkt_eventstate a where synstate =0 and packagestate = 2 and ErrorCode = 6 ;
--Define a cursor variable v_cinfo c_emp%rowtype, which is a row of data type in the cursor c_emp
C_row C_eventstate%rowtype;
Begin
For C_row in C_eventstate loop
Begin
Strsql:= ' select * from Mkt_userprizeinfo t where t.campaignid= ' | | C_row. campaignid| | ' and t.userid= ' | | c_row.phonenumber| | ' and t.policynumber= ' | | C_row. policynumber| | ";
Execute immediate strSQL;
Dbms_output.put_line (C_row. campaignid| | ' -' | | c_row.phonenumber| | ' -' | | C_row. Policynumber);
--Raise_application_error (-20001, ' value cannot be 0 ');
EXCEPTION
--Catching exceptions
When others then
Begin
V_errorcode: = SQLCODE;
V_errortext: = SUBSTR (SQLERRM, 1, 200);
Dbms_output.put_line (' Caught the wrong begin ');
Dbms_output.put_line (C_row. campaignid| | ' -' | | c_row.phonenumber| | ' -' | | C_row. policynumber| | ' -' | | V_errorcode | | ':: ' | | V_errortext);
Dbms_output.put_line (' Catch error end ');
End
End
End Loop;
End
Oracle cursor loop, query, exception capture, continue execution