ORACLE 经验两则：Sys_Refcursor 与外部表 SKIP 的配置骨架

1. 为什么这两个 ORACLE 老问题总在项目里反复出现做 ORACLE 存储过程开发或者数据加载的 DBA大概率都遇到过两个绕不开的场景一个是要在存储过程里把一段查询结果直接吐给上层应用另一个是把外部文本文件挂进数据库当表查。前者绕不开Sys_Refcursor后者绕不开外部表的SKIP子句。Sys_Refcursor是 ORACLE 9i 之后引入的弱类型游标好处是不用再像 9i 之前那样先TYPE ... IS REF CURSOR定义一个自定义类型直接拿它当OUT参数就能把结果集返回给调用方。听起来简单但实际写的时候参数顺序、OPEN ... FOR的写法、调用端怎么接每一步都有坑。外部表这边ORGANIZATION EXTERNAL配合oracle_loader能把服务器上的文本文件当普通表查。真正让人头疼的是文件头很多批处理文件第一行是标题或者总控行直接加载会把脏数据带进来这时候就得靠SKIP 1跳过去。再加上 DOS 和 UNIX 换行符不一样records delimited by newline和records delimited by 0x0A用错了整张表可能一行都读不出来。这篇就把这两块的可复制骨架拆开讲清楚配置直接拿去改字段名就能用。排查阶段如果想让 AI 工具帮你快速定位语法或者参数问题可以用 TaoToken 统一一个 Key 走 API 通道省得在多个工具之间来回切。2. TaoToken 前置把 AI 排查通道先接上在写存储过程和外部表的过程中报错信息往往比较短比如ORA-00942、ORA-29913这种光看编号不好判断是权限、路径还是语法问题。这时候让 AI 帮你把报错和上下文一起分析会快很多。TaoToken 在这里的作用是提供一个统一的 API 入口你不用为每个 AI 工具单独配一套 Key。官网入口是 https://taotoken.net/?utm_sourcetaotoken_aicg_blog_endutm_mediumcsdnutm_campaignrewriteutm_content API 地址是 https://taotoken.net/api 注意 API 这个地址不带 UTM 参数。具体操作上先去控制台创建一个 Key地址是 https://taotoken.net/console?utm_sourcetaotoken_aicg_blog_endutm_contentconsoleutm_campaignrewrite 然后在 API Keys 页面拿到密钥地址是 https://taotoken.net/api-keys?utm_sourcetaotoken_aicg_blog_endutm_contentapi-keysutm_campaignrewrite 。拿到之后你可以在本地脚本或者 AI 编码工具里把 base_url 指向https://taotoken.net/api模型名按文档里给的填。如果你只是想快速验证某个 ORACLE 语法或者让模型解释一段报错直接用模型对话页面就行https://taotoken.net/model-chat?utm_sourcetaotoken_aicg_blog_endutm_contentmodel-chatutm_campaignrewrite 。要是你长期在写 PL/SQL、做数据加载脚本想让 AI 持续参与编码可以看 Coding Planhttps://taotoken.net/coding-plan?utm_sourcetaotoken_aicg_blog_endutm_contentcoding-planutm_campaignrewrite 。接入文档在 https://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_contentdocutm_campaignrewrite 里面写了不同语言怎么调。注意TaoToken 只是帮你统一 AI 工具的调用通道不替代数据库客户端也不碰你的生产库连接。所有 SQL 还是在你自己的 ORACLE 环境里执行。3. Sys_Refcursor 存储过程可复制配置3.1 最小可跑的存储过程骨架先看一个完整的存储过程入参是业务日期范围和两个业务代码出参就是一个Sys_Refcursor。字段名我用了中文别名方便你对照结果。CREATE OR REPLACE PROCEDURE Zkquery ( p_Jyjh IN VARCHAR2, p_Wtdm IN VARCHAR2, p_Begindate IN DATE, p_Enddate IN DATE, Cur OUT SYS_REFCURSOR ) AS BEGIN OPEN Cur FOR SELECT Fpclh AS 批处理号, Fyyf AS 费用月份, Zhs AS 托收户数, Zje / 100 AS 托收金额, Cghs AS 成功户数, Cgje AS 成功金额, CASE WHEN Bz 0 THEN 未作返回 ELSE 已做返回 END AS 返回标志, Scph AS 上传批号, Schs AS 上传户数, Scje / 100 AS 上传金额, Zxrq AS 执行日期, Ctpc AS 出托批次, Sntfile AS 扣费文件, Rtnfile AS 返回盘文件 FROM t_Zkzl WHERE Wtdm p_Wtdm AND Jyjh p_Jyjh AND Zxrq BETWEEN p_Begindate AND p_Enddate; END Zkquery; /这里有几个点值得说。第一SYS_REFCURSOR是 ORACLE 预定义的弱类型游标不需要你自己TYPE。第二OPEN Cur FOR后面直接跟SELECT游标和查询是绑定的。第三OUT参数不需要初始化过程内部OPEN之后调用方就能取到结果。3.2 调用端怎么接这个游标在 SQL*Plus 或者 SQL Developer 里可以这样调VARIABLE rc REFCURSOR; EXEC Zkquery(JY001, WD001, TO_DATE(2024-01-01,YYYY-MM-DD), TO_DATE(2024-01-31,YYYY-MM-DD), :rc); PRINT rc;在 Java 里用 JDBC 调的话注册Types.REF_CURSOR或者OracleTypes.CURSOR然后callableStatement.registerOutParameter(5, OracleTypes.CURSOR)执行完getObject(5)拿到ResultSet再遍历。在另一个存储过程里调用就声明一个SYS_REFCURSOR变量传进去DECLARE v_cur SYS_REFCURSOR; v_row t_Zkzl%ROWTYPE; BEGIN Zkquery(JY001, WD001, SYSDATE - 30, SYSDATE, v_cur); LOOP FETCH v_cur INTO v_row; EXIT WHEN v_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_row.Fpclh); END LOOP; CLOSE v_cur; END; /3.3 参数对照表参数名方向类型说明p_JyjhINVARCHAR2交易计划号过滤条件p_WtdmINVARCHAR2委托代码过滤条件p_BegindateINDATE执行日期起始p_EnddateINDATE执行日期结束CurOUTSYS_REFCURSOR返回结果集游标提示SYS_REFCURSOR是弱类型字段结构由OPEN FOR的查询决定调用方不需要提前知道列定义但这也意味着编译期不会帮你检查列名拼写写的时候要仔细。4. 外部表 SKIP 跳行配置骨架4.1 DOS 格式文件的外部表DOS 格式换行是\r\n用records delimited by newline让 ORACLE 自己识别。SKIP 1跳过第一行标题。CREATE TABLE DXPK ( ZSXH VARCHAR2(20), HM VARCHAR2(20), A VARCHAR2(10), JFHM VARCHAR2(30), ZH VARCHAR2(20), RQ VARCHAR2(15), FYJE NUMBER, B VARCHAR2(10), C VARCHAR2(1), D VARCHAR2(1), PNXH VARCHAR2(10) ) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY pkdata ACCESS PARAMETERS ( records delimited by newline skip 1 nologfile nobadfile nodiscardfile fields terminated by | missing field values are null reject rows with all null fields ) LOCATION (Dxpk) ) PARALLEL 4 REJECT LIMIT UNLIMITED;4.2 UNIX 格式文件的外部表UNIX 换行是\n对应十六进制0x0A。这里skip 1的位置和 DOS 版本略有不同写在records delimited by 0x0A之后、fields terminated by之前。CREATE TABLE RTN ( XH VARCHAR2(8), YDM VARCHAR2(2), ZKBZ VARCHAR2(1), ZH VARCHAR2(19), KHJDM VARCHAR2(20), HM VARCHAR2(30), CKYE NUMBER, KYYE NUMBER, JYJE NUMBER, ZJSXF NUMBER, YWSXF NUMBER, YLSXF NUMBER ) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY pkdata ACCESS PARAMETERS ( records delimited by 0x0A nologfile nobadfile nodiscardfile skip 1 fields terminated by | missing field values are null reject rows with all null fields ) LOCATION (RTN) ) PARALLEL 4 REJECT LIMIT UNLIMITED;4.3 关键子句对照子句作用常见取值records delimited by指定行分隔符newline / 0x0Askip跳过文件开头行数1跳标题fields terminated by字段分隔符| / , / 0x09missing field values are null缺失字段置空固定写法reject rows with all null fields全空行丢弃固定写法nologfile / nobadfile / nodiscardfile不生成日志和坏文件按需REJECT LIMIT允许拒绝行数UNLIMITED / 数字注意DEFAULT DIRECTORY pkdata里的目录必须是数据库里已经创建的 DIRECTORY 对象而且 ORACLE 进程用户要有读权限。文件本身放在服务器文件系统上不是客户端。5. 验证请求与成功结果5.1 验证游标返回先确认存储过程编译通过SELECT object_name, status FROM user_objects WHERE object_type PROCEDURE AND object_name ZKQUERY;STATUS是VALID就说明编译没问题。然后在 SQL*Plus 里执行前面那段VARIABLE rc REFCURSOR的调用PRINT rc应该能看到查询结果集。如果是在应用端重点看ResultSet有没有数据、列名是不是和OPEN FOR里的别名一致。5.2 验证外部表加载外部表建好之后直接查SELECT COUNT(*) FROM DXPK; SELECT * FROM RTN WHERE ROWNUM 5;如果COUNT(*)返回 0先检查文件路径和文件名大小写。LOCATION (Dxpk)里的文件名要和服务器上实际文件名完全一致ORACLE 在部分平台上对大小写敏感。再查一下有没有被拒绝的行SELECT * FROM DXPK WHERE ROWNUM 10;如果字段错位多半是fields terminated by写错了或者文件里实际用的分隔符和配置不一致。可以用hexdump或者文本编辑器看下真实分隔符。5.3 用 AI 辅助排查把报错编号和你的建表语句一起丢给模型让它帮你比对参数。比如ORA-29913通常和外部表访问参数有关ORA-00942可能是表或视图不存在。通过 TaoToken 的模型对话页面可以直接问https://taotoken.net/model-chat?utm_sourcetaotoken_aicg_blog_endutm_contentmodel-chatutm_campaignrewrite 。如果你在写一个批量加载脚本想让 AI 帮你生成多个外部表 DDL可以用 Coding Plan 持续对话https://taotoken.net/coding-plan?utm_sourcetaotoken_aicg_blog_endutm_contentcoding-planutm_campaignrewrite 。6. 本篇常见错排查6.1 Sys_Refcursor 相关报错 ORA-01001无效的游标。多半是游标没OPEN就FETCH或者已经CLOSE了还在用。检查OPEN Cur FOR是否执行到以及CLOSE的位置。调用端拿不到数据。先确认OPEN FOR里的WHERE条件是不是把数据全过滤掉了。可以先把条件去掉直接SELECT COUNT(*)看基表有没有数据。在存储过程里嵌套调用时游标被覆盖。如果外层过程也用了同名游标变量注意作用域。建议每个过程用独立的变量名。Java 端报SQLException: Invalid column type。检查registerOutParameter用的类型是不是OracleTypes.CURSOR不同 JDBC 驱动版本常量名可能不一样。6.2 外部表 SKIP 相关SKIP 没生效标题行还是进来了。确认skip的位置在ACCESS PARAMETERS括号内且拼写正确。DOS 和 UNIX 版本里skip和records delimited by的相对顺序可以调整但必须在fields terminated by之前。UNIX 文件用 newline 读出来是乱码或者只有一行。换成records delimited by 0x0A。反过来DOS 文件用0x0A可能每行末尾多一个\r导致最后一个字段带不可见字符。ORA-29913执行 ODCIEXTTABLEOPEN 调用时出错。通常是 DIRECTORY 对象不存在或者权限不够。用SELECT * FROM all_directories WHERE directory_name PKDATA;确认然后GRANT READ ON DIRECTORY pkdata TO 你的用户;。字段数对不上。外部表的列数要和文件里fields terminated by分隔后的字段数一致。少列会补 null多列会报错或者截断。建表前先用文本编辑器数一下每行有几个分隔符。REJECT LIMIT 设太小。如果文件里有少量脏数据REJECT LIMIT 0会导致查询直接失败。调试阶段先用UNLIMITED确认数据没问题再收紧。6.3 环境与权限外部表依赖服务器端文件系统客户端工具连过去只能看到表结构看不到文件。如果你在本地 SQL Developer 里建外部表文件必须放在数据库服务器上不是你的本机。DIRECTORY 对象的路径也是服务器路径。另外PARALLEL 4在数据量小的时候不一定更快反而可能因为并行度设置不当导致资源争用。单文件加载可以先去掉PARALLEL子句等确认功能正常再加。排查过程中如果报错信息太短可以把完整的 DDL、报错编号、文件前几行内容一起整理好通过 TaoToken 的 API 通道发给模型分析。接入方式参考文档https://taotoken.net/doc?utm_sourcetaotoken_aicg_blog_endutm_contentdocutm_campaignrewrite Key 在 https://taotoken.net/api-keys?utm_sourcetaotoken_aicg_blog_endutm_contentapi-keysutm_campaignrewrite 创建。这样一套流程走下来游标返回和外部表加载基本能一次跑通。