Oracle英文版培訓(xùn)課件之Implement Streams：les13

ApplyHandlersObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DescribethepurposeofanapplyhandlerListthedifferenttypesofapplyhandlersDescribethepurposeofanerrorhandlerCreateanduseanapplyhandlerManageapplyhandlersforadatabaseApplyAnapplyprocessdequeuesLCRsandusermessagesfromaspecificqueueanddoeseitherofthefollowing:HandleseachLCRindividuallythoughpartofthesametransactionPassestheeventasaparametertoauser-definedprocedureCommitsatransactionaftersuccessfulcompletionofallLCRswithinthetransaction.MessageProcessingMessagehandlerDMLhandlerDDLhandlerLCRsUser-enqueued

messagesApplychangesDDLLCRsDMLLCRsDMLhandlerDMLLCRsA001LCRsA002A003QueueLCRLCRUsermessageUsermessageLCRLCRUsermessageLCRLCR…PrecommithandlerApplyHandlersUser-writtencustomapplyproceduresWritteninPL/SQL,Java,C,orC++Java,C,andC++proceduresmustbewrappedinPL/SQLbeforetheycanbeused.Canbeusedfor:CustomtransformationsColumnsubsettingorrenamingNormalizingordenormalizingdataPopulatingrelatedfieldsortablesImplementingeventprocessing

logicthatcannotbeprogrammed

usingrulesApplyHandlersforLCRMessagesSingleDDLhandlerallowedMultipleDMLhandlerssupportedforeachtableandforeachapplyprocessPrecommithandlerworkswithaDMLormessagehandlerExecuteLCRDMLhandlerDDLhandlerDMLhandlerTargettableLCRPrecommithandlerCreatinganApplyHandlerProcedureADML,DDL,ormessagehandlermusttakethefollowingform:YoucanspecifyaseparateDMLhandlerforasharedtableforeachofthefollowingoperations:INSERTUPDATEDELETELOB_UPDATEPROCEDUREuser_procedure(

parameter_nameINSYS.AnyData);DMLHandler:ExampleCREATEORREPLACEPROCEDUREconv_order_totals(lcr_anydataINSYS.AnyData)ISvalSYS.AnyData;...BEGINt:=lcr_anydata.getObject(lcr);val:=(lcr.get_value('new','ORDER_TOTAL'));t:=val.getNumber(ordtotal);val:=(lcr.get_value('new','CUSTOMER_ID'));t:=val.getChar(custid);SELECTus_exchange_rateINTOcurr_exFROMoe.currency_exchangesex,oe.customerscWHEREc.customer_id=custidANDc.country=ex.cid;ordtotal:=ordtotal*curr_ex;lcr.set_value('new','ORDER_TOTAL',SYS.AnyData.ConvertNUMBER(ordtotal));lcr.execute(true);END;DMLHandler:ExampleCREATEORREPLACEPROCEDUREconv_order_totals(lcr_anydataINSYS.AnyData)ISvalSYS.AnyData;...BEGINt:=lcr_anydata.getObject(lcr);val:=(lcr.get_value('new','ORDER_TOTAL'));t:=val.getNumber(ordtotal);val:=(lcr.get_value('new','CUSTOMER_ID'));t:=val.getChar(custid);SELECTus_exchange_rateINTOcurr_exFROMoe.currency_exchangesex,oe.customerscWHEREc.customer_id=custidANDc.country=ex.cid;ordtotal:=ordtotal*curr_ex;lcr.set_value('new','ORDER_TOTAL',SYS.AnyData.ConvertNUMBER(ordtotal));lcr.execute(true);END;ImplementingaDMLHandlerSpecifyaDMLhandlerwiththeDBMS_APPLY_ADM.SET_DML_HANDLERprocedure.BEGINDBMS_APPLY_ADM.SET_DML_HANDLER(object_name=>'OE.ORDERS',object_type=>'TABLE',operation_name=>'INSERT',user_procedure=>'OE.CONV_ORDER_TOTALS',apply_name=>'APPLY_SITE1_LCRS'assemble_lobs=>true);END;/DDLHandler:ExampleCREATEORREPLACEPROCEDUREaudit_ddl_actions

(evtINSYS.ANYDATA)ISlcrSYS.LCR$_DDL_RECORD;rcPLS_INTEGER;ddl_textCLOB;BEGINrc:=evt.getObject(lcr);DBMS_LOB.CreateTemporary(ddl_text,TRUE);lcr.GET_DDL_TEXT(ddl_text);INSERTINTOstrmadmin.ddl_historyVALUES(SYSDATE,lcr.GET_SOURCE_DATABASE_NAME(),lcr.GET_COMMAND_TYPE(),lcr.GET_OBJECT_OWNER(),lcr.GET_OBJECT_NAME(),lcr.GET_OBJECT_TYPE(),ddl_text,lcr.GET_EXTRA_ATTRIBUTE('USERNAME'));lcr.EXECUTE();DBMS_LOB.FreeTemporary(ddl_text);END;ImplementingaDDLHandlerUsetheddl_handlerparameterineitherofthefollowingtwoprocedures:DBMS_APPLY_ADM.CREATE_APPLY

DBMS_APPLY_ADM.ALTER_APPLY

BEGINDBMS_APPLY_ADM.ALTER_APPLY(apply_name=>'APPLY_SITE1_LCRS',ddl_handler=>'STRMADMIN.AUDIT_DDL_ACTIONS');END;/PrecommitHandlerCanbeusedwithuser-enqueuedeventsorcapturedeventsReceivesasinputthecommitSCNgeneratedatthesourcedatabaseEnablesyoutoperformactionsattheendofatransaction,beforeitiscommittedCommitSCNPrecommithandlerPrecommitHandler:ExampleCREATEORREPLACEPROCEDUREstrmadmin.audit_commit(cscnINNUMBER)ISBEGIN--Insertcommitinformationintodml_historyINSERTINTOstrmadmin.dml_history(timestamp,commit_scn)VALUES(SYSDATE,cscn);END;/ImplementingaPrecommitHandlerUsetheprecommit_handlerparameterineitherofthefollowingtwoprocedures:DBMS_APPLY_ADM.CREATE_APPLY

DBMS_APPLY_ADM.ALTER_APPLY

BEGINDBMS_APPLY_ADM.ALTER_APPLY(apply_name=>'apply_site1_lcrs',precommit_handler=>'strmadmin.audit_commit');END;/ErrorHandlerUser-writtencustomapplyprocedureWritteninPL/SQL,Java,C,orC++Java,C,andC++proceduresmustbewrappedinPL/SQLbeforetheycanbeused.AssociatedwithaspecificDMLoperationonatableExecutedwhenarowLCRraisesanapplyprocesserrorOnepertableDefaultApplywithErrorHandlerAttempttoapplyLCR.Checkforconflictsorapplyerrors.Iferrorsoccur,callauser-specifiederrorhandlingprocedure.Afterresolvingtheerror,applythechange.Forunresolvederrors,allLCRsassociatedwiththetransactionareplacedintotheerrorqueue.TheentiretransactionisrolledbackandplacedintheerrorqueueApplychangesAnnnErrorhandlerLCRConflict

handlerExampleofErrorHandlerCREATEORREPLACEPROCEDUREregions_pk_error(messageINANYDATA,…)ISreg_idNUMBER;…BEGINIFerror_numbers(1)IN(1,2290)THENad:=DBMS_STREAMS.GET_INFORMATION('CONSTRAINT_NAME');ret:=ad.GetVarchar2(errlog_rec.text);ELSEerrlog_rec.text:=NULL;ENDIF;ad:=DBMS_STREAMS.GET_INFORMATION('SENDER');ret:=ad.GETVARCHAR2(errlog_rec.sender);apply_name:=DBMS_STREAMS.GET_STREAMS_NAME();ret:=message.GETOBJECT(lcr);errlog_rec.object_name:=lcr.GET_OBJECT_NAME();errlog_mand_type:=lcr.GET_COMMAND_TYPE();errlog_rec.errnum:=error_numbers(1);errlog_rec.errmsg:=error_messages(1);…ENDregions_pk_error;ImplementinganErrorHandlerSpecifyanerrorhandlerbysettingtheuser_procedureanderror_handlerparametersoftheSET_DML_HANDLERprocedureinDBMS_APPLY_ADM.ForaspecificdatabaseobjectForaspecificDMLoperationAnerrorhandlermustbeofthefollowingform:PROCEDUREuser_procedure(evt INSYS.AnyData,error_stack_depthINNUMBER,error_numbersINDBMS_UTILITY.NUMBER_ARRAY,error_messagesINemsg_array);RestrictionsforApplyHandlerProceduresDonotCOMMITorROLLBACKintheapplyhandlerprocedure.IfyouaremodifyingtheLCRandthenusingtheEXECUTEmemberprocedureintheapplyhandlerprocedure:Donotmanipulatemorethanonerowinarowoperation.ForUPDATEorDELETE,includetheentirekeyofthetargettableinthelistofoldvalues.ForINSERT,includetheentirekeyofthetargettableinthelistofnewvalues.DonotmodifyLONG,LONGRAW,orLOBcolumndatainanLCR.CustomizingApplyHandlerActionsUtilitiesintheDBMS_STREAMSpackageenableyoutofurthercustomizetheactionstakenbyapplyhandlers,rules,ortransformations:GET_INFORMATIONGET_STREAMS_NAMEGET_STREAMS_TYPEMemberfunctionsoftheLCRrecordtypesalsoprovideinformationforcustomizingactions:GET_COMMAND_TYPEGET_COMMIT_SCNGET_COMPATIBLELOBAssemblyINSERTLOB_UPDATELOB_UPDATELOB_TRIMUPDATELCRsINSERTLOB

locatorLCRsImplementingLOBAssemblyDBMS_APPLY_ADM.SET_DML_HANDLER(object_name=>'PM.AD_DATA',object_type=>'TABLE',operation_name=>'UPDATE',error_handler=>false,user_procedure=>'PM.STAMP_AD_IMAGES',assemble_lobs=>TRUE);LOB

assemblyLOB

locatorAD_DATAManagingApplyHandlersByusingtheDBMS_APPLY_ADM.ALTER_APPLYprocedure,youcan:SpecifyorremoveamessageorDDLhandlerforanapplyprocessSpecifyorremoveaprecommithandlerforanapplyprocessWiththeDBMS_APPLY_ADM.SET_DML_HANDLERprocedure,youcan:SpecifyorremoveaDMLhandlerforashareddatabaseobjectSpecifyorremoveanerror