You can initialize PL/SQL arrays with FOR loops itype constructors from 21c array := arr_type ( FOR i IN ... ) These can loop through Values => IN x .. y A cursor => IN ( SELECT ... ) Use the optional INDEX clause to set custom indices Try these at buff.ly/J1aH20F

Brand new to Oracle AI Database? @anders-swanson.bsky.social shows you how to get started by Running a Free database on Docker Get must have dev tools SQL Developer extension for VS Code & SQLcl command line interface And points to free resources to help you learn

Transparent Data Encryption is an efficient way to secure data in Oracle AI Database But you need to ensure you keep track of your encryption password! David Fitzjarrell shows you how to setup an autologin wallet to store this password buff.ly/xjE8VD1

You can now have managed, serverless MCP Servers for your Oracle AI Databases in the Cloud These enable you to talk to your database But for critical reports you may want verified #SQL @thatjeffsmith.com shows how to use predefined reports with the MCP server

Oracle Autonomous AI Database can read data in Snowflake Iceberg Tables direct from S3 Sathishkumar Rangaraj shows the steps: Add ACL for Horizon Snowflake endpoint Create AWS S3 storage credentials Create OAuth credential Create an external table buff.ly/EENP4cp

Tired of hitting: CREATE ... ORA-01031: insufficient privileges errors? Give access in Oracle AI Database 26ai with one role: GRANT db_developer_role TO ... And you're able to create all the standard database objects

#SQL injection enables hackers to modify queries run by your app But just how bad is it? @mikepound.bsky.social shows how it enables you to get data from any table you can access e.g. usernames and (hashed!) passwords Keep your data safe - use bind variables!

Unsure what Oracle optimization terms such as: Adaptive Statistics Cardinality Bind Variable Peeking Predicate Pushing mean? @vldbb.bsky.social has built a glossary to help you lookup and understand these concepts

The clustering factor in Oracle Database measures The similarity in the orders of Logical index values Physical table row locations Lower value => closer match => the index is more effective @chandlerdba.bsky.social explains & suggests ways to improve it

A lost update happens when one user unknowingly overwrites another's changes @vladmihalcea.bsky.social details How lost updates happen How to prevent them using pessimistic (stateful) & optimistic (stateless) locking Most apps now are stateless => use optimistic

You can create indexes for ad-hoc searches of #JSON and #XML data Oracle AI Database 26ai simplified the syntax to CREATE SEARCH INDEX ... ON ... ( { xmlcol | jsoncol } ) Giving indexed full-text searches of these values Try it on FreeSQL buff.ly/rxbwNMb

#SQL queries can sort by unselected columns, e.g. SELECT a FROM t ORDER BY b But it wasn't always this way! @winand.at takes a tour of ORDER BY's history, covering Positional references Non-selected columns Expressions Null ordering Subqueries

There are joins that can only return rows from the left table Semi join => rows matching the right (IN or EXISTS) Anti join => rows not matching the right (NOT IN or NOT EXISTS) @lukaseder.bsky.social makes the case these should have their own #SQL syntax

A #SQL trace in Oracle Database captures run time stats vital for optimizing queries You can view these in V$DIAG_SQL_TRACE_RECORDS But the details can be cryptic @ppiipuu.bsky.social shows how to parse these to something useful with JavaScript in the database

Giving your app direct access to database tables means Increased risks for schema changes A heavy maintenance burden @jasminfluri.bsky.social discusses how accessing views & stored procedures instead gives Encapsulated schema changes Security & governance Improved versioning

Check string similarity in Oracle AI Database 26ai with FUZZY_MATCH ( <algo>, <str1>, <str2> ) By default the result is scaled 0-100 (no-full match) Algorithms include Levenshtein Jaro-Winkler Bigram/Trigram Longest common substring Try it on FreeSQL buff.ly/RQunDH5

With Oracle AI Database you can turn natural language into SQL with SELECT AI <your natural language query> e.g. SELECT AI how many customers exist This is available for Autonomous and on-premises, 19c and 26ai Mark Hornick covers release differences buff.ly/ZRiqYfw

Calling a PL/SQL block is one statement => the database treats all DML within it as a one unit If you have BEGIN INSERT ... UPDATE ... DELETE ... END; If any of these raise an error they'll all be rolled back @connormcd.bsky.social discusses

The LONG data type in Oracle Database is tricky to work with Sadly the partition high value column uses LONG 26ai eased this by adding CLOB and JSON versions of this But what if you're on older releases? @tanelpoder.com shows how to convert LONG to VARCHAR2 with PL/SQL

Storing date values as strings is a BAAAAD idea! You can store invalid values The optimizer struggles to get accurate row estimates Stuck working on a schema that does this? Creating function-based indexes in Oracle Database can help Richard Foote shows

Oracle AI Database 26ai enhanced the RETURNING clause for DML You can get the before & after values with OLD & NEW Added support for MERGE So you can do UPDATE ... RETURN OLD col, NEW col INTO :before, :after MERGE INTO ... RETURN OLD col, NEW col INTO :before, :after

The Oracle SQLcl MCP server added a schema-information tool This introspects an Oracle schema and returns structured metadata about it This gives an LLM a map of your database to help it write accurate SQL @thatjeffsmith.com shows it in action

Oracle AI Database 26ai added enum domains - a series of name-value pairs CREATE DOMAIN e AS ENUM ( nm1 = v1, nm2 = v2, ... ) Use the names in #SQL to get the values @dbamarco.bsky.social shows how to use them to make payment card issuer and type enums

Keyset pagination An efficient way to get the next page when paging through results It uses a query like SELECT ... FROM ... WHERE col < :prevColVal ORDER BY col DESC FETCH FIRST ... @vladmihalcea.bsky.social shows how to do this with Spring Data WindowIterator

AI Vector Search in Oracle AI Database enables search based on meaning than exact keyword matches But what if you need to combine semantic and keyword search? Prashant Dixit shows how to combine these using WHERE clauses Hybrid vector indexes

Encode strings based on their pronunciation in Oracle AI Database 26ai with PHONIC_ENCODE ( DOUBLE_METAPHONE[_ALT], <str> ) This enables you to match strings with different spellings that sound the same e.g. "Knight" and "Night" Try it on FreeSQL buff.ly/qIstu22

You can count the unique values in a column in #SQL with COUNT ( DISTINCT col ) Ensure DISTINCT goes inside the COUNT If you do DISTINCT COUNT ( col ) The database counts the rows, then returns the unique counts Ben Brumm discusses

Oracle Spatial helps you answer questions like: What landmarks fall inside this map window? What is the exact distance between these two places? What objects are near this location? @anders-swanson.bsky.social shows you how to starting using it with JDBC

Assign names to ORA exception numbers in PL/SQL with ex_name EXCEPTION; PRAGMA EXCEPTION_INIT ( ex_name, <err#> ) This enables you to trap these errors by name EXCEPTION WHEN ex_name THEN ... @shoumadipdas.bsky.social demos

You can autogenerate unique numbers in Oracle AI Database with Identity columns Sequences If you want to switch from identities to sequences, @oneoracledeveloper.bsky.social has built a script Create sequences with table max values Switch identities to sequence defaults

Update one table using a column from another with direct joins in 26ai: UPDATE <target_tab> SET <target_col> = <source_col> FROM <source_tab> WHERE <join_condition> You can also use these joins in DELETE statements

SELECT AI enables you to convert natural language to Oracle #SQL The challenge is natural language is ambiguous To help LLMs understand requests, Denio Flavio tries adding schema annotations These add key-value metadata with business context

In Oracle AI Database 26ai you can create tables or views with up to 4,096 columns To do this, you must set the system parameters MAX_COLUMNS = EXTENDED COMPATIBLE = 23.0.0 (or higher)

You can manipulate #JSON in PL/SQL with: APEX_JSON functions JSON object types Vaibhav Gajare shares examples of these, use cases, and best practices for working with JSON in the database buff.ly/4M6gmX8

Oracle AI Database added the FILTER clause for aggregates in 23.26.1 fn ( ... ) FILTER ( WHERE ... ) Harris Fungwi shows how to use it to solve LeetCode problem 1158: Count a customer's orders for 2019 Check their blog to see how and other solutions

Oracle AI Databaes 23.9 added a UUID() function These generates v4 UUIDs @pdevisser.bsky.social checks it out an looks at methods for generating v7 UUIDs

Convert PL/SQL records and arrays from and to #JSON in Oracle AI Database 26ai JSON_VALUE ( ..., '$' RETURNING <plsql type> ) => JSON to PL/SQL JSON ( <plsql var> ) => PL/SQL to JSON Try it at buff.ly/ewJPfCT

AI agent skills are a set of markdown instructions explaining how to do a task @krisrice.io has built a library of 100+ skills for working with Oracle AI Database with actionable examples, best practices, and common pitfalls Get them at

Stopping users storing overlapping periods is a challenge Oracle AI Database 26ai makes it simple with assertions Rob van den Berg show you how with CREATE ASSERTION ... CHECK ( ALL ( <query> ) t1 SATISFY ( NOT EXISTS ( <overlaps query> ) ) ) buff.ly/qQeuUH6

Make insert-only tables in Oracle AI Database with CREATE IMMUTABLE TABLE ... ( ... ) NO DROP UNTIL m DAYS IDLE NO DELETE UNTIL n DAYS AFTER INSERT Take care with the NO clauses They control when you can delete rows or drop the table @oraclebase.bsky.social demos

Triggers are a common way to track DML changes But it's easy to overlook these when you add new columns! @connormcd.bsky.social shows a compound trigger Oracle AI Database solution Store rowids in a private temp table Join these to the table Get every column with JSON_OBJECT(*)

JSON Schema in Oracle AI Databse 26ai enables you to validate the structure of JSON objects and arrays using DBMS_JSON_SCHEMA @martindba.bsky.social demos how you can use this to check data submitted to the database via REST calls

Sometimes its the small changes that make the big difference Oracle AI Database 26ai added HIGH_VALUE_CLOB HIGH_VALUE_JSON To the partitioning dictionary views => it's easier to query partition boundaries @danischnider.bsky.social discusses

Get the plan for #SQL in Oracle AI Database with dbms_xplan.display_cursor But what if you want to get many at once? @lukaseder.bsky.social demos: SELECT ... FROM v$sql s ,TABLE (dbms_xplan.display_cursor ( s.sql_id, s.child_number )) p WHERE s.sql_text LIKE ...

With SQL/PGQ in Oracle AI Database, you can Create property graphs Query them with GRAPH_TABLE This maps tables and rows to vertices and edges in a graph This enables you to traverse networks of relationships with #SQL Try it at buff.ly/89FnPaf

Preparing for an interview with #SQL questions? Intellipaat covers 15 questions & answers, including Constraints Normalisation DDL and DML commands GROUP BY vs ORDER BY WHERE vs HAVING UNION and UNION ALL Find 2nd highest salary

Want to analyze your Strava data with #SQL? @go-faster.co.uk shows 3 ways you can load it into Oracle AI Database to do this Directly through a JSON Duality View Convert each name-value pair in explicit code Extract Values from JSON in Virtual Columns

Duality views in Oracle AI Databse allow you to design a single, canonical model that satisfies both document and relational APIs @anders-swanson.bsky.social shows you how to use these in a Java app to do CRUD operations andersswanson.dev buff.ly/KHR6LES

Want to connect Python to Oracle AI Database? @antikyte.bsky.social has built a guide of installing & using cx-Oracle with Python 3.6.8 python-oracledb with Python 3.12 Showing how to select from a table execute a packaged procedure execute a packaged function

Override NULL on insert and updates in Oracle AI Database 26ai DEFAULT ON NULL FOR INSERT AND UPDATE The database will replace NULL with the default value in these statements This extends DEFAULT ON NULL added in 12c Try it at buff.ly/G5xNLm7