Metabase 的 Oracle SQL 方言提示词文件MetaBee 编写 Oracle 兼容原生查询的规则体系【免费下载链接】metabaseThe easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:项目地址: https://gitcode.com/GitHub_Trending/me/metabase本篇技术指南以 Metabase 仓库中的 Oracle 方言提示词文件 为主体完整拆解其中面向 LLM 的 Oracle SQL 语法规则——从标识符引号、DUAL表、TRUNC日期截断、NVL/DECODE到ROWNUM分页陷阱与MERGEUpsert——并结合 Metabase 的 skill 注册机制、llm-sql-dialect-resource驱动映射与 Oracle 驱动的实际 SQL 生成代码说明这份文档是如何在 MetaBeeMetabase 的 AI 助手的 SQL 编辑器会话中被预加载、并最终转化为可执行 SQL 的。读完本文你既能把它当作一份独立的 Oracle SQL 速查手册也能理解 Metabase 如何通过“按数据源方言动态注入提示词”的架构为每种数据库引擎定制 AI 生成行为。文件定位一份被程序化注册的“隐藏 skill”oracle.md 位于resources/metabot/prompts/dialects/目录下与 postgresql.md、mysql.md、snowflake.md 等 14 份方言文件并列。它不是一般的用户文档而是 MetaBee 的SQL 方言技能SQL dialect skill正文当用户在某个数据库的原生 SQL 编辑器中与 MetaBee 交互时MetaBee 会把当前数据源对应的方言文件整体注入上下文指导模型生成符合该引擎语法的 SQL。从源码结构看其装载链路涉及三处关键实现驱动声明方言资源路径。Oracle 驱动通过多方法派发声明自己的方言文件;; modules/drivers/oracle/src/metabase/driver/oracle.clj (L896-897) (defmethod driver/llm-sql-dialect-resource :oracle [_] metabot/prompts/dialects/oracle.md)该多方法在 driver.clj 中定义defmulti llm-sql-dialect-resource0.59.0 引入:default方法返回nil即未声明者没有方言专属指令。值得注意的映射细节是llm-sql-dialect-resource的返回值并不是与驱动名一一对应——例如:sparksql驱动复用的就是databricks.md。skill 注册表程序化收集方言文件。skills.clj 的命名空间文档L14-18明确写道SQL dialect skills are registered programmatically from theresources/metabot/prompts/dialects/files and surfaced only bydialect-preload-parts.具体实现在 dialect-skills扫描metabot/prompts/dialects/目录下的所有.md文件文件名去扩展名即引擎名并生成形如:sql-dialect-oracle的隐藏 skill id。这些 skill 被skills-for-profile显式过滤掉L238 的remove :dialect因此不会出现在 skill 目录清单里模型无法通过猜 id 去load_skill加载它们——只能由系统预加载。按会话上下文预加载。dialect-preload-parts 接收从 viewing context 中提取的 SQL 引擎名例如oracle通过driver/llm-sql-dialect-resource解析出对应的方言文件并读取其正文然后合成一对load_skill工具调用及其结果注入消息流L316-L322且置于系统缓存断点之下——这样跨数据库切换时缓存的系统提示前缀保持字节一致节省 prompt cache 开销。调用点见 messages.clj。相关行为有完整测试覆盖见 skills_test.clj 中的dialect-preload-parts-test含“解析裸驱动名”与“未知引擎返回空”等断言。除 MetaBee agent 链路外一次性的 LLM SQL 生成端点也复用同一映射llm/api.clj 的load-dialect-instructions带 memoize按引擎读取方言文件全文作为dialect-instructions变量渲染进 one-shot SQL 生成的 mustache 提示词模板。也就是说oracle.md 的每一行都会原样进入 LLM 的上下文。文档开篇的定位语是Oracle Database uses PL/SQL-extended SQL with unique syntax. Follow these dialect-specific rules.以下逐节展开其规则体系。标识符引号与 DUAL 表Oracle 有两条基础但极易踩坑的规则标识符引号标识符用双引号包裹MyColumn、table-name不加引号的标识符会被自动大写SELECT mycolumn实际查找的是MYCOLUMN字符串字面量只能使用单引号string value。SELECT CamelCaseColumn, reserved-word FROM My TableDUAL 表Oracle 不允许省略FROM子句。对不涉及任何表的表达式常量运算、函数调用必须从DUAL伪表中“选择”SELECT SYSDATE FROM DUAL SELECT 1 1 FROM DUAL SELECT constant FROM DUAL这条规则解释了为什么后文几乎所有示例查询都以FROM DUAL结尾——它既是 Oracle 的语法强制要求也是给 LLM 的显式模板。字符串操作拼接优先使用||运算符CONCAT只接受恰好 2 个参数这是文档反复强调的“Important”点-- Concatenation: || operator (preferred) or CONCAT (two args only) SELECT first_name || || last_name AS full_name SELECT CONCAT(CONCAT(first_name, ), last_name) -- Only 2 args!字符串函数一览注意SUBSTR与INSTR均为从 1 开始的索引INSTR未找到时返回 0SELECT LOWER(name), UPPER(name), INITCAP(name), TRIM(name), LTRIM(name), RTRIM(name), SUBSTR(name, 1, 3), -- 1-indexed LENGTH(name), REPLACE(name, old, new), INSTR(name, sub), -- Find position (1-indexed, 0 if not found) INSTR(name, sub, 1, 2), -- 2nd occurrence LPAD(str, 10, 0), RPAD(str, 10, ), REVERSE(name), -- 21c, or use custom function REGEXP_REPLACE(text, pattern, replacement), REGEXP_SUBSTR(text, pattern)模式匹配方面LIKE是大小写敏感的文档给出两种不敏感匹配方案对列应用UPPER()后再LIKE或使用REGEXP_LIKE的i标志SELECT * FROM t WHERE name LIKE A% -- Case-sensitive SELECT * FROM t WHERE UPPER(name) LIKE A% -- Case-insensitive workaround SELECT * FROM t WHERE REGEXP_LIKE(name, ^[A-Z]) SELECT * FROM t WHERE REGEXP_LIKE(name, ^[A-Z], i) -- i case-insensitive日期与时间Oracle 日期体系是方言差异的重灾区文档标注的“Critical”要点是Oracle 用TRUNC做日期截断而不是DATE_TRUNC格式化/解析用TO_CHAR/TO_DATE。当前日期时间函数注意四者的类型与语义差异SELECT SYSDATE, -- DATE (includes time) SYSTIMESTAMP, -- TIMESTAMP WITH TIME ZONE CURRENT_DATE, -- Session timezone CURRENT_TIMESTAMP -- Session timezone with precision FROM DUAL日期截断TRUNC的第二参数决定截断粒度注意IW与WW的周定义不同SELECT TRUNC(order_date) -- Truncate to day (removes time) SELECT TRUNC(order_date, MM) -- Truncate to month SELECT TRUNC(order_date, Q) -- Truncate to quarter SELECT TRUNC(order_date, YYYY) -- Truncate to year SELECT TRUNC(order_date, IW) -- Truncate to ISO week (Monday) SELECT TRUNC(order_date, WW) -- Truncate to week (same day as Jan 1) SELECT TRUNC(order_date, HH) -- Truncate to hour日期算术DATE加减数字即加减“天”更严谨的做法用INTERVAL月运算用ADD_MONTHS以正确处理月末SELECT order_date 7, -- Add 7 days order_date - 1, -- Subtract 1 day order_date INTERVAL 7 DAY, order_date INTERVAL 1 MONTH, order_date INTERVAL 2 HOUR, ADD_MONTHS(order_date, 3) -- Add months (handles month-end)日期差值与分量提取SELECT end_date - start_date, -- Days as number MONTHS_BETWEEN(end_date, start_date), -- Months (can be fractional) EXTRACT(DAY FROM (end_date - start_date)) -- Days from interval SELECT EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date), EXTRACT(DAY FROM order_date), EXTRACT(HOUR FROM ts), EXTRACT(MINUTE FROM ts), TO_CHAR(order_date, D), -- Day of week (1Sunday) TO_CHAR(order_date, DDD), -- Day of year TO_CHAR(order_date, Q), -- Quarter TO_CHAR(order_date, IW) -- ISO week number格式化与解析FM前缀即 fill mode去除Month等格式化产生的填充空格SELECT TO_CHAR(order_date, YYYY-MM-DD), TO_CHAR(order_date, Month DD, YYYY), -- January 15, 2024 TO_CHAR(order_date, FMMonth DD, YYYY), -- January 15, 2024 (FM fill mode) TO_CHAR(amount, 999,999.00), -- Number formatting TO_DATE(2024-01-15, YYYY-MM-DD), TO_TIMESTAMP(2024-01-15 10:30:00, YYYY-MM-DD HH24:MI:SS) FROM DUAL类型转换标准CAST之外Oracle 专属的TO_*转换函数提供格式掩码级的控制力-- CAST syntax SELECT CAST(123 AS NUMBER) SELECT CAST(123.45 AS NUMBER(10,2)) SELECT CAST(123 AS VARCHAR2(10)) SELECT CAST(order_date AS TIMESTAMP) SELECT CAST(ts AS DATE) -- TO_ conversion functions (Oracle-specific, more control) SELECT TO_NUMBER(123) SELECT TO_NUMBER(1,234.56, 9,999.99) -- With format mask SELECT TO_CHAR(123, 00000) -- 00123 SELECT TO_DATE(2024-01-15, YYYY-MM-DD) SELECT TO_TIMESTAMP(2024-01-15 10:30, YYYY-MM-DD HH24:MI)文档同时列出了 Oracle 常用类型名清单NUMBER、NUMBER(p,s)、VARCHAR2(n)、CHAR(n)、CLOB、DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、INTERVAL、RAW、BLOB、XMLTYPE。NULL 处理SELECT COALESCE(nullable_col, default), -- First non-null (standard) NVL(nullable_col, default), -- Oracle-specific, two args NVL2(col, not_null_val, null_val),-- If col IS NOT NULL then 2nd else 3rd NULLIF(col, ), -- Returns NULL if col LNNVL(condition), -- True if condition is FALSE or NULL DECODE(col, NULL, is null, col) -- NULL comparison in DECODE FROM DUAL文档特别指出NVL是 Oracle 专属仅两个参数而NVL2(x, a, b)是一个强力的三元式工具——x非空返回a为空返回b。JSON 处理Oracle 12c读取与校验-- Extract from JSON SELECT JSON_VALUE(json_col, $.key), -- Scalar value JSON_VALUE(json_col, $.key RETURNING NUMBER), JSON_QUERY(json_col, $.object), -- Object/array as JSON JSON_VALUE(json_col, $.items[0].name), -- Array access (0-indexed) JSON_EXISTS(json_col, $.key) -- Returns true/falseJSON_TABLE是把 JSON 数组解析为关系行的利器注意内层PATH的相对定位SELECT jt.* FROM t, JSON_TABLE(t.json_col, $.items[*] COLUMNS ( id NUMBER PATH $.id, name VARCHAR2(100) PATH $.name, nested_val VARCHAR2(50) PATH $.nested.field ) ) jt构造 JSON 与条件过滤ORDER BY子句可作用于JSON_ARRAYAGGSELECT JSON_OBJECT(id VALUE id, name VALUE name) FROM t SELECT JSON_ARRAY(1, 2, 3) FROM DUAL SELECT JSON_OBJECTAGG(key VALUE val) FROM t SELECT JSON_ARRAYAGG(val ORDER BY val) FROM t -- JSON condition in WHERE SELECT * FROM t WHERE JSON_EXISTS(json_col, $.status?( active))窗口函数与聚合函数窗口函数部分在标准函数之外突出了RATIO_TO_REPORTOracle 专属计算分区内占比SELECT ROW_NUMBER() OVER (PARTITION BY cat ORDER BY amt DESC), RANK() OVER (ORDER BY amt DESC), DENSE_RANK() OVER (ORDER BY amt DESC), SUM(amt) OVER (PARTITION BY cat), LAG(amt, 1, 0) OVER (ORDER BY dt), LEAD(amt) OVER (ORDER BY dt), FIRST_VALUE(amt) OVER (ORDER BY dt ROWS UNBOUNDED PRECEDING), LAST_VALUE(amt) OVER (ORDER BY dt ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), NTILE(4) OVER (ORDER BY amt), PERCENT_RANK() OVER (ORDER BY amt), RATIO_TO_REPORT(amt) OVER (PARTITION BY cat) -- Oracle-specific: percentage of total FROM t聚合部分的三个 Oracle 特性LISTAGG字符串聚合WITHIN GROUP指定排序DISTINCT变体需 19c、MEDIAN、STATS_MODE众数SELECT COUNT(*), COUNT(DISTINCT col), SUM(amount), AVG(amount), MIN(val), MAX(val), LISTAGG(col, , ) WITHIN GROUP (ORDER BY col), -- String aggregation LISTAGG(DISTINCT col, , ) WITHIN GROUP (ORDER BY col), -- 19c MEDIAN(amount), -- Oracle-specific STATS_MODE(col) -- Most frequent value FROM t GROUP BY category子查询因子CTE标准 CTE 之外递归 CTE11gR2用于树形展开例如按经理关系递归列出下属及其深度-- Standard CTE WITH active_users AS ( SELECT * FROM users WHERE status active ) SELECT * FROM active_users -- Recursive CTE (11gR2) WITH subordinates (id, name, manager_id, depth) AS ( SELECT id, name, manager_id, 1 FROM employees WHERE id 1 UNION ALL SELECT e.id, e.name, e.manager_id, s.depth 1 FROM employees e JOIN subordinates s ON e.manager_id s.id ) SELECT * FROM subordinates行限制FETCH FIRST、ROWNUM 与驱动层实现这是全文最“Critical”的一节。ROWNUM在ORDER BY之前生效因此带排序的行数限制必须把排序查询包进子查询-- FETCH FIRST (12c, preferred) SELECT * FROM orders ORDER BY order_date DESC FETCH FIRST 10 ROWS ONLY SELECT * FROM orders ORDER BY order_date DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY -- ROWNUM (older syntax, applied before ORDER BY!) SELECT * FROM ( SELECT * FROM orders ORDER BY order_date DESC ) WHERE ROWNUM 10 -- ROW_NUMBER for pagination SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY order_date DESC) rn FROM orders t ) WHERE rn BETWEEN 21 AND 30这条规则与 Metabase 自身的 Oracle 驱动实现直接呼应。oracle.clj 在处理LIMIT/OFFSET查询时明确注释;; Oracle doesnt support LIMIT n syntax. Instead we have to use WHERE ROWNUM n驱动通过两层子查询 一个虚拟的__rownum__列实现偏移分页ROWNUM offset items内层过滤、__rownum__ offset外层过滤执行完毕后再由 remove-rownum-column 把这个辅助列从结果集列结构中剔除。换言之提示词文档教给 LLM 的ROWNUM陷阱规避策略与驱动代码内部对同一限制的工程化绕法“先套子查询再过滤行号”是同构的——这正说明了该文档的规则并非泛泛的 Oracle 教材而是与 Metabase 查询管线行为对齐的生成守则。MERGEUpsertOracle 没有ON CONFLICT/ON DUPLICATE KEY UPDATEUpsert 用MERGE表达“匹配则更新、不匹配则插入”的完整逻辑MERGE INTO target_table t USING source_table s ON (t.id s.id) WHEN MATCHED THEN UPDATE SET t.name s.name, t.updated_at SYSDATE WHEN NOT MATCHED THEN INSERT (id, name, created_at) VALUES (s.id, s.name, SYSDATE)常见模式片段以下五个模式是 MetaBee 生成 Oracle SQL 时高频复用的“积木”安全除法NULLIF(denominator, 0)使除零产生NULL而非报错SELECT NULLIF(denominator, 0), numerator / NULLIF(denominator, 0), CASE WHEN denominator 0 THEN NULL ELSE numerator / denominator END FROM DUALDECODE函数——Oracle 专属的“表格式 CASE”其关键差异是对NULL的比较是原生支持的标准CASE用 NULL永远为假SELECT DECODE(status, A, Active, I, Inactive, P, Pending, Unknown -- Default ) FROM t -- DECODE handles NULL comparison (unlike CASE) SELECT DECODE(col, NULL, is null, not null) FROM t条件聚合SELECT COUNT(*) AS total, SUM(CASE WHEN status active THEN 1 ELSE 0 END) AS active_count, COUNT(CASE WHEN region US THEN 1 END) AS us_count FROM t分组取第一行两种等价思路ROW_NUMBER包一层过滤rn 1或 Oracle 专属的KEEP子句在聚合时保留排序首位的值SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY category ORDER BY updated_at DESC) rn FROM products t ) WHERE rn 1 -- Using KEEP FIRST (Oracle-specific) SELECT category, MAX(name) KEEP (DENSE_RANK FIRST ORDER BY updated_at DESC) AS latest_name, MAX(price) KEEP (DENSE_RANK FIRST ORDER BY updated_at DESC) AS latest_price FROM products GROUP BY category序列生成CONNECT BY LEVEL是经典的“数字表”技巧11gR2 可用递归 CTE 替代-- Using CONNECT BY SELECT LEVEL AS n FROM DUAL CONNECT BY LEVEL 100 SELECT DATE 2024-01-01 LEVEL - 1 AS dt FROM DUAL CONNECT BY LEVEL 365 -- Using recursive CTE (11gR2) WITH dates (dt) AS ( SELECT DATE 2024-01-01 FROM DUAL UNION ALL SELECT dt 1 FROM dates WHERE dt DATE 2024-12-31 ) SELECT * FROM dates层级查询CONNECT BY——用START WITH ... CONNECT BY PRIOR声明父子关系SYS_CONNECT_BY_PATH生成从根到当前节点的路径ORDER SIBLINGS BY控制兄弟节点顺序SELECT id, name, LEVEL, SYS_CONNECT_BY_PATH(name, /) AS path FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR id manager_id ORDER SIBLINGS BY name与其他方言的关键差异对照表文档末尾给出了一张四引擎对照表这是 MetaBee 在跨数据库场景下最容易“方言串味”的维度逐行覆盖引号、拼接、截断、加减日期、当前日期、NULL 函数、字符串聚合、分页、无表查询与 UpsertFeatureOraclePostgreSQLMySQLSQL ServerIdentifier quotesdoubledoublebacktick[brackets]Concat\|\|\|\|CONCATDate truncateTRUNC(d, MM)DATE_TRUNCDATE_FORMATDATETRUNCDate addd INTERVAL 7 DAYd INTERVAL 7 daysDATE_ADDDATEADDCurrent dateSYSDATECURRENT_DATENOW()GETDATE()NULL functionNVLCOALESCEIFNULLISNULLString aggLISTAGGSTRING_AGGGROUP_CONCATSTRING_AGGPaginationFETCH FIRST/ROWNUMLIMIT OFFSETLIMIT OFFSETTOP/OFFSET FETCHEmpty querySELECT x FROM DUALSELECT xSELECT xSELECT xUpsertMERGEON CONFLICTON DUPLICATE KEYMERGETernaryDECODE/NVL2CASEIFIIF与 Oracle 驱动实现的交叉印证提示词文档中的若干规则可以在 Oracle 驱动源码中找到对应的工程事实二者互为佐证无布尔类型的现实约束文档的类型名清单里没有BOOLEAN而驱动代码直接说明了原因——oracle.clj 注释Oracle doesnt really support boolean types so use bits instead并把布尔预编译参数替换为1/0。因此 MetaBee 生成 Oracle SQL 时不会出现TRUE/FALSE常量比较。日期字面量的 NLS 陷阱驱动在为LocalDateTime内联字面量时优先生成to_date(%s, YYYY-MM-DD HH24:MI:SS)而非date ...字面量oracle.clj L876-882注释解释后者依赖 Oracle 的NLS_DATE_FORMAT在多数安装上会报错。这正是文档强调“用TO_DATE/TO_CHAR显式给格式掩码”的深层原因。ROWNUM 分页如前所述驱动的LIMIT/OFFSET改写L613-668与文档“ROWNUM先于ORDER BY生效”的告诫完全一致。错误码级别的方言知识驱动还针对 Oracle 特化了几处判定——查询取消检测错误码 1013、表不存在检测错误码 942oracle.clj L889-894这类细节虽未写入提示词但说明方言文件与驱动实现共享同一套对 Oracle 行为的理解。小结resources/metabot/prompts/dialects/oracle.md 是一份“双重身份”的文档对用户而言它是一份覆盖标识符、字符串、日期、类型转换、NULL 语义、JSON、窗口/聚合函数、CTE、分页、Upsert 与经典模式的 Oracle SQL 实战速查表对 Metabase 而言它是 skill 注册机制 程序化加载、由 llm-sql-dialect-resource 声明、经 dialect-preload-parts 在 SQL 编辑器会话中合成预加载的隐藏方言技能。文档规则与 Oracle 驱动 中 ROWNUM 分页、布尔位化、to_date字面量等实现细节相互印证共同保证 MetaBee 为 Oracle 数据源生成的原生查询在语法与运行时行为上都是可执行的。如果你要为 Metabase 支持的新数据源补充 LLM 生成质量按同一目录约定新增一份方言文件并在对应驱动的llm-sql-dialect-resource方法中声明路径即被该机制自动纳管。【免费下载链接】metabaseThe easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:项目地址: https://gitcode.com/GitHub_Trending/me/metabase创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考