Community Tour '23 - Colombia July 28, 2023 Sean Scott Oracle ACE Director Managing Principal Consultant @oraclesean linkedin.com/in/soscott/ [email protected] @ViscosityNA www.viscosityna.com
to Development Development reviews the promotion records. They seem to be correct. Ticket is marked RESOLVED, Marketing is asked to confirm. Development investigates; this appears to be a known application issue. The DBA team has a script to fix it. Ticket is assigned to the DBA team. Marketing user can’t add a promotion. Contacts Help Desk DBA team reviews the ticket and runs script to fix the issue. Ticket reassigned to Development for review. Marketing confirms the issue is fixed. Ticket is closed. Solving this engages the end user, help desk, developers and DBAs Multiple steps take time, impacting external customers and internal teams
backend Backend calls PL/SQL package Marketing user receives confirmation of fix in channel Package interprets parameters, validates entries, confirms condition and runs automated fix OUT parameter passed to backend Marketing user can’t add a promotion Invokes a chatbot command in the #marketing channel with promotion data Backend processes OUT parameter Identifies success/failure Builds response package Passes response to Slack API Securely exposes endpoint to chat Automation validates and runs code End user empowered to fix issues when discovered Internal teams no longer engaged
& reactive • The recipient of an event may not be the audience • Example: Alerts sent to DBA for application deadlocking • DBA observes performance issues, forwards to developers • Developers may not recognize the urgency • Friction grows between DBAs, developers
Incoming Webhooks application to Slack • Available with the free tier • Install and configure Incoming Webhooks in a channel • Send REST message via POST • UTL_HTTP
and verifies the certificate is correct & slack.com can be accessed. select utl_http.request(:webhook_url, NULL, :wallet_dir) as x from dual; X ------------------------------------ invalid_payload
extending Slack • Message via Web API, Webhooks • Read messages via Web, RTM, and Events API • Respond via Event API • Create custom slash commands (/command) • Run bots • Build intelligent interactions
and builds SQL Sends SQL via cx_Oracle Slack displays payload in channel Oracle runs PL/SQL Returns results to Client User enters a command Slack sends JSON via HTTP POST Flask/Python reads and interprets result Returns JSON payload or webhook to Slack Webhook Many options for frameworks!
and builds SQL Sends SQL via cx_Oracle Slack displays payload in channel Oracle runs PL/SQL Returns results to Client User enters a command Slack sends JSON via HTTP POST Flask/Python reads and interprets result Returns JSON payload or webhook to Slack Testing Framework
... grant create session to slack; create user appuser identified by ... alter user appuser quota ... on <ts>; grant execute on appuser.<pkg> to slack; create synonym slack.<pkg> for appuser.<pkg>;
Flask app "dbdo.py" * Environment: production WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead. * Debug mode: off * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit) 127.0.0.1 - - [28/Jul/2023 00:52:28] "POST /dbdo HTTP/1.1" 200 - 127.0.0.1 - - [28/Jul/2023 00:52:42] "POST /dbdo HTTP/1.1" 200 -
online Account Sean Scott (Plan: Free) Version 3.3.1 Region United States (us) Latency 46ms Web Interface http://127.0.0.1:4040 Forwarding https://XXX.ngrok-free.app -> http://localhost:5000 Connections ttl opn rt1 rt5 p50 p90 7 0 0.02 0.02 0.18 3.06
5000 Session Status online Account Sean Scott (Plan: Free) Version 3.3.1 Region United States (us) Latency 46ms Web Interface http://127.0.0.1:4040 Forwarding https://XXX.ngrok-free.app -> http://localhost:5000 Connections ttl opn rt1 rt5 p50 p90 7 0 0.02 0.02 0.18 3.06
parameters and variables • Don’t hard-code anything! • Add logging to v0.1 • Not an afterthought • Who, what, when • Consider multiple levels/verbosity
role: • create session • execute on API packages • Limit permissions to the API role: • Separate from application or data owner • 18c+ Define as schema-only users • <18c: No create session or connect
applications to private channels • Validate users when necessary • Treat chat development as a “regular” project • Version control • Configuration management • Include log monitoring to operational footprint
oraclesean
cscengineer
moulongzhang
aeonpeople
demaecan
knih
yama3133
jacopen
whisaiyo
ysuzuki
line_developers_tw
tosho
kinunori
tonyrice
tamaranovitovic
lynnandtonic
tomoyanonymous
aleyda
inesmontani
iamctodd
addyosmani
eileencodes
sergeychernyshev
tenderlove
uxyall
![@ViscosityNA www.viscosityna.com Contact [email protected] https://linktr.ee/oraclesean](https://files.speakerdeck.com/presentations/4e03aad7f32344cda99923d17d886f58/slide_66.jpg){kind=link}