文档首页/ 数据库和应用迁移 UGO/ 用户指南/ 语法转换指南/ 转换错误码/ Oracle To GaussDB 转换错误码/ U0100058: 目标数据库不支持dbms_sql.execute_and_fetch exact parameter value is true
更新时间：2026-03-19 GMT+08:00
查看PDF
分享

U0100058: 目标数据库不支持dbms_sql.execute_and_fetch exact parameter value is true

描述

GaussDB不支持dbms_sql.execute_and_fetch。

数据库类型与版本

  • 源库类型与版本：UGO 支持的 Oracle 版本。
  • 目标库类型与版本：UGO 支持的 GaussDB 版本。

语法示例

DECLARE

l_cursor_id INTEGER;

l_rows_fetched INTEGER;

l_empno NUMBER;

l_ename VARCHAR2(50);

l_salary NUMBER;

BEGIN

l_cursor_id := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(

c => l_cursor_id,

statement => 'SELECT empno, ename, sal FROM emp WHERE empno = :emp_id',

language_flag => DBMS_SQL.NATIVE

);

DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':emp_id', 7369);

DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_empno);

DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 2, l_ename, 50);

DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 3, l_salary);

l_rows_fetched := DBMS_SQL.EXECUTE_AND_FETCH(l_cursor_id, TRUE);

IF l_rows_fetched > 0 THEN

DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_empno);

DBMS_SQL.COLUMN_VALUE(l_cursor_id, 2, l_ename);

DBMS_SQL.COLUMN_VALUE(l_cursor_id, 3, l_salary);

END IF;

DBMS_SQL.CLOSE_CURSOR(l_cursor_id);

EXCEPTION

WHEN OTHERS THEN

IF DBMS_SQL.IS_OPEN(l_cursor_id) THEN

DBMS_SQL.CLOSE_CURSOR(l_cursor_id);

END IF;

RAISE;

END;

/

改造建议

dbms_sql.execute_and_fetch替换为DBE_SQL.NEXT_ROW，并且注释掉第二个参数。

GaussDB修改后：

DECLARE
    l_cursor_id   NUMBER(38);
    l_rows_fetched NUMBER(38);
    l_empno       NUMBER;
    l_ename       VARCHAR2(50);
    l_salary      NUMBER;
BEGIN
       l_cursor_id := DBE_SQL.REGISTER_CONTEXT();
    DBE_SQL.SQL_SET_SQL(
        CONTEXT_ID           => l_cursor_id,
        QUERY_STRING   => 'SELECT empno, ename, sal FROM emp WHERE empno = :emp_id',
        language_flag => 2
    );
    DBE_SQL.SQL_BIND_VARIABLE(l_cursor_id, ':emp_id', 7369);
    DBE_SQL.SET_RESULT_TYPE(l_cursor_id, 1, l_empno);
    DBE_SQL.SET_RESULT_TYPE(l_cursor_id, 2, l_ename, 50);
    DBE_SQL.SET_RESULT_TYPE(l_cursor_id, 3, l_salary);
    DBE_SQL.SQL_RUN(l_cursor_id);
    l_rows_fetched := DBE_SQL.NEXT_ROW(l_cursor_id/*, TRUE*/);
    IF l_rows_fetched > 0 THEN
        DBE_SQL.GET_RESULTS(l_cursor_id, 1, l_empno);
        DBE_SQL.GET_RESULTS(l_cursor_id, 2, l_ename);
        DBE_SQL.GET_RESULTS(l_cursor_id, 3, l_salary);
    END IF;
    DBE_SQL.SQL_UNREGISTER_CONTEXT(l_cursor_id);
    
EXCEPTION
    WHEN OTHERS THEN
        IF DBE_SQL.IS_ACTIVE(l_cursor_id) THEN
            DBE_SQL.SQL_UNREGISTER_CONTEXT(l_cursor_id);
        END IF;
        RAISE;
END;
/

相关文档