Edit

Streaming in the database!

Postach.io OracleDB published

I had heard that you could do streaming and transformation in the Oracle Database with nothing but PL/SQL, and it turns out you can.

My friend Chris Beck shot me over some code that at a very basic level demonstrates the queueing portion of streams.

In a nutshell this code

  1. creates a database object type to represent our messages
  2. sets up the queue with the dbms_aqadm package
  3. creates a dummy source table and populates it with 1000 rows
  4. enques the 1000 rows with the dbms_aq package
  5. deques one of the rows and prints it’s message


your output should look something like this


for more info start here

Check out the dbms_aq package
https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_AQ.html

the Advanced Queueing User’s Guide
https://docs.oracle.com/en/database/oracle/oracle-database/18/adque/aq-operations-using-pl-sql.html

and Oracle Streams Concepts and Administration for more about streams
https://docs.oracle.com/cd/B28359_01/server.111/b28321/toc.htm


I’d really like to see the streaming capabilities of the database (or adwc) compared to alternatives like RabbitMQ or Apache Kafka. Althought performance might be hard to test I’d just like to see which one is the easiest to implement.

I wonder how we’d test performance between streaming platforms, and what would be our metrics? messages/second? ability to scale horizontally? ability to scale vertically? latency? other features like guarenteed ordering?

%23%20Streaming%20in%20the%20database%21%0A@%28Postach.io%29%5BOracleDB%2C%20published%5D%0A%0AI%20had%20heard%20that%20you%20could%20do%20streaming%20and%20transformation%20in%20the%20Oracle%20Database%20with%20nothing%20but%20%60PL/SQL%60%2C%20and%20it%20turns%20out%20you%20can.%20%0A%0AMy%20friend%20Chris%20Beck%20shot%20me%20over%20some%20code%20that%20at%20a%20very%20basic%20level%20demonstrates%20the%20queueing%20portion%20of%20streams.%20%0A%0AIn%20a%20nutshell%20this%20code%20%0A1.%20creates%20a%20database%20object%20type%20to%20represent%20our%20messages%20%0A2.%20sets%20up%20the%20queue%20with%20the%20%60dbms_aqadm%60%20package%0A3.%20creates%20a%20dummy%20source%20table%20and%20populates%20it%20with%201000%20rows%0A4.%20enques%20the%201000%20rows%20with%20the%20%60dbms_aq%60%20package%20%0A5.%20deques%20one%20of%20the%20rows%20and%20prints%20it%27s%20message%0A%0A%0A%0A%5Bgist%20url%3D%22https%3A//gist.github.com/sblack4/8ec8590846100a20037cd304d31e7478%22%20file%3D%22dbms_rls_demo.md%22%5D%0A%0A%20____%0A%20%0A%20your%20output%20should%20look%20something%20like%20this%0A%20%0A%20%5Bgist%20url%3D%22https%3A//gist.github.com/sblack4/8ec8590846100a20037cd304d31e7478%22%20file%3D%22dbms_rls_demo_out.txt%22%5D%0A____%0A%0Afor%20more%20info%20start%20here%0A%0ACheck%20out%20the%20%60dbms_aq%60%20package%20%20%0A%5Bhttps%3A//docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_AQ.html%5D%28https%3A//docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_AQ.html%29%0A%0Athe%20Advanced%20Queueing%20User%27s%20Guide%20%20%0A%5Bhttps%3A//docs.oracle.com/en/database/oracle/oracle-database/18/adque/aq-operations-using-pl-sql.html%5D%28https%3A//docs.oracle.com/en/database/oracle/oracle-database/18/adque/aq-operations-using-pl-sql.html%29%0A%0Aand%20*Oracle%20Streams%20Concepts%20and%20Administration*%20for%20more%20about%20streams%0A%5Bhttps%3A//docs.oracle.com/cd/B28359_01/server.111/b28321/toc.htm%5D%28https%3A//docs.oracle.com/cd/B28359_01/server.111/b28321/toc.htm%29%0A___%0A%0AI%27d%20really%20like%20to%20see%20the%20streaming%20capabilities%20of%20the%20database%20%28or%20adwc%29%20compared%20to%20alternatives%20like%20%5BRabbitMQ%5D%28https%3A//www.rabbitmq.com/%29%20or%20%5BApache%20Kafka%5D%28https%3A//kafka.apache.org/%29.%20Althought%20performance%20might%20be%20hard%20to%20test%20I%27d%20just%20like%20to%20see%20which%20one%20is%20the%20easiest%20to%20implement.%20%0A%0AI%20wonder%20how%20we%27d%20test%20performance%20between%20streaming%20platforms%2C%20and%20what%20would%20be%20our%20metrics%3F%20messages/second%3F%20ability%20to%20scale%20horizontally%3F%20ability%20to%20scale%20vertically%3F%20latency%3F%20other%20features%20like%20guarenteed%20ordering%3F%20%0A