BasicObjectsofOracleStreamsObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:Accesstheattributesandmethodsofuser-definedtypesListthedifferenttypesofeventsusedinOracleStreamsDescribeanLCRCreateaStreamsadministratoruserCreateaStreamsqueueQuerythedatadictionaryforStreamsqueueinformationFundamentalTerminologyMessageQueueEnqueueDequeueAgentSubscriberConsumerUser-defineddatatypesAttributesMethodsObjectTypesAnobjecttypeisauser-definedcomposite

datatype.Anobjecttypeencapsulatesadatastructurealongwiththemethodsthatareneededtomanipulateit.AttributeMethodRemoveTransferAddCheckStatusidnameaddresscountryEmployeetypeStructureofanObjectTypeMethodbodiesAttributedeclarationsMethodspecificationsSpecificationBodyPublicinterfacePrivateimplementationStructureofanObjectType:MethodsMethodsinatypedefinition:MemberStaticConstructorMethodRemoveTransferAddCheckStatusidnameaddresscountryEmployeetypeCREATEORREPLACETYPEname_typASOBJECT(first_name VARCHAR2(25),middle_initCHAR(1),last_name VARCHAR2(25), MEMBERFUNCTIONfull_nameRETURNVARCHAR2,MEMBERFUNCTIONinitialsRETURNVARCHAR2 );/CreatinganObject-TypeSpecificationExample12CreatinganObject-TypeBodyExampleCREATEORREPLACETYPEBODYname_typASMEMBERFUNCTIONfull_nameRETURNVARCHAR2ISBEGINRETURN(first_name||''||last_name);ENDfull_name;MEMBERFUNCTIONinitialsRETURNVARCHAR2ISBEGINRETURN(substr(first_name,1,1)||middle_init||substr(last_name,1,1));END;END;/CREATETABLEname_tableOFname_typ;INSERTINTOname_tableVALUES(

);SELECTnt.first_name,nt.full_name()NAME,nt.initials()INITIALSFROMname_tablent;FIRST_NAMENAMEINITIALS-----------------------------------------------MichaelMichaelFoxMJFCallingObjectMethodsExamplename_typ('Michael','J','Fox')SYS.AnyDataTypeTheSYS.AnyDatatypeisaself-describingtypethatcontainsthedataandadata-typecode.TheSYS.AnyDatatypehasnovisibleattributesanddoesnotuseaconstructor.YoucancreateanobjectoftypeSYS.AnyDatabydoingeitherofthefollowing:CallingtheConvert*()staticfunctionsCallingBeginCreate()tostart,Set*()foreachattribute,andEndCreate()tofinishThefollowingmembermethodsmanagetheaccesstodatathatiscontainedinaSYS.AnyDatatype:Get<datatype>functionsSet<datatype>functionsStreamsEventsTherearetwobasictypesofeventsinStreams:User-enqueuedeventsCapturedeventsLogicalChangeRecords(LCRs)LCRscanbecreatedbythecaptureprocess,ortheycanbecreatedmanuallybyauser.ThecaptureprocessreformatschangesthatarecapturedfromtheredologintotwotypesofLCRs:RowLCRsDDLLCRsThecaptureprocessimplicitlyenqueuesLCRs.User-createdLCRsmustbeexplicitlyenqueued.RowLCRsArowLCRdescribesaDMLchangetothedatainasingleroworaLOBcolumninarow:SingledatachangemayyieldmultipleLCRs.EachrowLCRisanobjectoftypeLCR$_ROW_RECORD.RowLCRs:DescriptionSTATICFUNCTIONCONSTRUCT(source_database_nameINVARCHAR2,command_typeINVARCHAR2,object_ownerINVARCHAR2,object_nameINVARCHAR2,tagINRAWDEFAULTNULL,transaction_idINVARCHAR2DEFAULTNULL,scnINNUMBERDEFAULTNULL,old_valuesINSYS.LCR$_ROW_LISTDEFAULTNULL,new_valuesINSYS.LCR$_ROW_LISTDEFAULTNULL)RETURNSYS.LCR$_ROW_RECORD;DDLLCRsADDLLCRdescribesadatadefinitionlanguagechange.EachDDLLCRisanobjectoftypeLCR$_DDL_RECORD.DDLLCRs:DescriptionSTATICFUNCTIONCONSTRUCT(source_database_nameINVARCHAR2,command_typeINVARCHAR2,object_ownerINVARCHAR2,object_nameINVARCHAR2,object_typeINVARCHAR2,ddl_textINCLOB,logon_userINVARCHAR2,current_schemaINVARCHAR2,base_table_ownerINVARCHAR2,base_table_nameINVARCHAR2,tagINRAWDEFAULTNULL,transaction_idINVARCHAR2DEFAULTNULL,scnINNUMBERDEFAULTNULL)RETURNSYS.LCR$_DDL_RECORD;TypesofUsersAssociatedwithStreamsSecurequeueuserCaptureuserApplyuserStreamsadministratoruserSecureQueueUserForuserstoenqueueordequeueeventsfromaSYS.AnyDataqueue,theymustbeconfiguredassecurequeueusers.TheSET_UP_QUEUEprocedureintheDBMS_STREAMS_ADMpackageconfiguresqueue_ownerandqueue_userassecurequeueusersautomatically.YoucanconfigurenewsecurequeueusersbyrerunningtheSET_UP_QUEUEprocedure.CaptureUserAcaptureuser:CapturesallDMLchangesandDDLchangesthatsatisfythecaptureprocessrulesetsHasenqueueprivilegesonthestagingqueueandisasecurequeueuserofthatqueueIsconfiguredwiththeALTER_CAPTUREprocedureoftheDBMS_CAPTURE_ADMpackageMustbeexplicitlygrantedprivilegeson

therulesandrulesetsusedbythe

captureprocessApplyUserAnapplyuser:AppliesallDMLstatementsandDDLstatementstodatabaseobjectsRunsuser-definedapplyhandlersIsconfiguredwiththeALTER_APPLYprocedureoftheDBMS_APPLY_ADMpackageMusthaveprivilegesondatabaseobjectsanduser-definedproceduresaccessedduringapplyTheuserwhocreatesthequeueisautomaticallyconfiguredasanapplyuser.StreamsAdministratorUserPerformsadministrativefunctionsinaStreamsenvironmentMustexistatallStreamssitesNeedsadefaultandtemporarytablespaceotherthanSYSTEMRequirestheCONNECT,RESOURCE,andDBArolesMaybegrantedotherprivilegesexplicitlyorwiththeDBMS_STREAMS_AUTHpackageCreatingaStreamsAdministratorUserCREATEUSERstrmadminIDENTIFIEDBYstreamsDEFAULTTABLESPACEUSERSTEMPORARYTABLESPACETEMP;CREATEDIRECTORYscriptsAS'/oracle/scripts';GRANTCONNECT,RESOURCE,DBATOstrmadmin;BEGINDBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'STRMADMIN',grant_privileges=>FALSE,file_name=>'make_admin.sql',directory_name=>'scripts');END;/ManagingStreamsAdministratorUsersSELECT*FROMDBA_STREAMS_ADMINISTRATOR;USERNAMELOCAL_PRIVILEGESACCESS_FROM_REMOTE------------------------------------------------STRMADMINYESNOEXECDBMS_STREAMS_AUTH.REVOKE_ADMIN_PRIVILEGE(-

grantee=>'STRMADMIN');StagingAreaStreamseventsareheldinastagingareaandimplementedasaqueue:Allevents,LCRs,andusermessagescanbestagedinthesamequeue.Eventsremaininthestagingareauntiltheyareconsumedbyallinterestedprocessesandapplications.StagingAreaArchitectureCaptureSpillInstanceSGASharedpoolJavapoolLargepoolStreamspoolStreams

dictionarySYS.AnyDataQueuesHaveatwo-partinfrastructureQueuetableQueueArecreatedwiththeSET_UP_QUEUEprocedureofDBMS_STREAMS_ADMDefaultname:STREAMS_QUEUE_TABLEType:SYS.AnyDataSecureCreatingaStagingQueueTocreateastagingqueueforOracleStreams,executetheSET_UP_QUEUEprocedureintheDBMS_STREAMS_ADMpackage.Youcanoptionallyspecifythefollowinginformation:ThequeuetableandqueuenameAstorageclauseforthequeuetableAqueueuserAcommentforthequeueTheSET_UP_QUEUEprocedureautomaticallystartsthequeueafteritiscreated.CreatingaStagingQueue:ExampleBEGINDBMS_STREAMS_ADM.SET_UP_QUEUE(queue_table=>'hr_streams_qt',queue_name=>'hr_queue',queue_user=>'hr');END;/RemovingaQueue:ExampleBEGINDBMS_STREAMS_ADM.REMOVE_QUEUE(queue_name=>'hr_streams_qt',cascade=>TRUE,drop_unused_queue_table=>TRUE);END;/ViewingQueueInformationToviewqueuetablesinthedatabase,query:ALL_QUEUE_TABLESDBA_QUEUE_TABLESUSER_QUEUE_TABLESToviewqueuesinthedatabase,query:ALL_QUEUESDBA_QUEUESUSER_QUEUESTodeterminewhatqueueprivilegesthecurrentuserhas,queryQUEUE_PRIVILEGES.BufferedMessagingENQUEUESpillInstanceSGASharedpoolJavapoolLargepoolStreamspoolBufferedPersistentViewInterfacetoQueueBuffersSELECTqueue_id,queue_schema,queue_name,

num_msgs,spill_msgs,cnum_msgs,cspill_msgs

FROMV$BUFFERED_QUEUES;

QUEUE_IDQUEUE_SCHEMAQUEUE_NAME--------------------------------------------NUM_MSGSSPILL_MSGSCNUM_MSGSCSPILL_MSGS-----------------------------------------36683STRMADMINSTREAMS_QUEUE51515151ViewInterfacetoQueueBuffers