APEX数据源加载实现Excel表数据导入及自定义存储过程

王小小鸭

已于 2023-09-18 16:49:05 修改

阅读量687

点赞数

分类专栏： PL/SQL oracle oracle apex 文章标签： java 数据库服务器

于 2023-09-18 16:45:38 首次发布

本文链接：https://blog.csdn.net/clover_oreo/article/details/132575970

版权

oracle apex 同时被 3 个专栏收录

43 篇文章 1 订阅

订阅专栏

PL/SQL

41 篇文章 0 订阅

订阅专栏

oracle

26 篇文章 1 订阅

订阅专栏

在APEX应用程序中会涉及到数据加载，说白了就是导入导出数据到数据库中，这里就以Excel导入数据到TEST_DATA_WXX表为例，来学习共享组件数据源数据加载定义

1 第一步先导出一个数据模板

进入《王小小鸭的学习demo》打开【用户管理】-【操作】-【下载】-【Excel】

下载后的Excel数据表是这样的↓

2.建表TEST_DATA_WXX，确定表结构

CREATE TABLE  "TEST_DATA_WXX"  --数据源加载的新表
(
--     主键自增generated by default as identity primary key
    USER_ID      NUMBER generated by default as identity primary key not null,
    NAME         NVARCHAR2(64)                                       not null,
    GENDER       NVARCHAR2(3),
    MOBILE       NVARCHAR2(32),
    JOB_NUMBER   NVARCHAR2(32),
    PASSWORD     NVARCHAR2(32),
    EMAIL        NVARCHAR2(32),
    DEVELOPER_ID NVARCHAR2(32), --开发者id
    IS_LEAVE     NUMBER,        --是否离职，0为否，1为是
    REMARK       NVARCHAR2(256),
    UPDATE_DATE  DATE
);

3 第三步创建数据加载定义

3.1在APEX开发环境以此找到【共享组件】-【数据源】-【数据加载定义】

3.2创建一个数据加载定义-【从头开始】-【下一步】

3.3定义名称、目标类型、表名等

3.4第一步导出的Excel文件选进来

3.5列和数据库表的列建立映射

映射之前

映射之后

不要这么做！！！【不足和改进建议】但是发现一个问题，从用户界面下载的Excel文件中因为界面不显示USER_ID，所以没有USER_ID，无法设置主键，还是得从数据库获取对应数据表，这样数据项才能完整（我是时间旅行者，我从时间海游回来了，我的建议是不要那么做，因为会返工，别问我是怎么知道的，就从界面导出就好）

然后重复第三步内容

啊？？？

打开f12查看问题

Unchecked runtime.lastError: A listener indicated an asynchronous response by returning true, but the message channel closed before a response was received

未选中runtime.lastError：侦听器通过返回true来指示异步响应，但消息通道在收到响应之前关闭

Error handling response: TypeError: Cannot read properties of undefined (reading 'isM3U8Video')

错误处理响应：TypeError:无法读取未定义的属性（读取“isM3U8Video”）

查到的资料：

这是一个错误信息，表明在异步响应处理中出现了问题。具体问题可以在代码中找到。以下是可能引起该错误的一些常见原因和解决方法：

尚未添加响应监听器：请确保代码中添加了相应的监听器，以便在异步操作完成后接收响应。

监听器可能已被移除：如果监听器被移除或失效，可能导致这个错误。请检查代码中是否正确添加了监听器，并在适当的时候移除监听器。

异步操作出现错误：请确保你的异步操作没有出现错误或异常，否则可能导致这个错误。检查异步操作的实现，以确认没有任何问题。

浏览器兼容性问题：不同的浏览器对于异步操作的处理方式可能有所不同，这可能是导致这个错误的原因之一。请检查代码是否具有跨浏览器兼容性，并尝试在不同的浏览器中运行以确认是否是浏览器特定的问题。

如果你能提供更多的上下文或相关的代码，我可以更具体地指导你解决这个问题。

错误处理响应：TypeError:无法读取未定义的属性（读取“isM3U8Video”）

【应对方法】

以下是几种可能的解决方案：

1. 检查你的网络连接。电脑或移动设备必须与互联网连接才能浏览网页或使用应用。如果你的网络连接不稳定或者速度过慢，那么可能会出现“加载时出错请点按以重试”的提示。你可以尝试重新连接Wi-Fi或移动数据，或者联系运营商检查网络问题。

2. 清除浏览器或应用缓存。浏览器或应用在访问网页或下载内容时会缓存一些数据，以便下一次访问速度更快。但是，过多的缓存可能会导致某些网页或应用出现错误。你可以尝试清除浏览器或应用缓存，然后重新访问网页或应用。

3. 更换浏览器或应用。有些网页或应用可能不兼容你使用的浏览器或应用。你可以尝试更换浏览器或应用，然后重新访问网页或应用。如果你使用的是手机或平板电脑，还可以尝试使用其他浏览器应用。

4. 确认网页或应用是否可用。有时，网页或应用本身出现问题，导致无法正常加载。你可以尝试在其他设备上访问同一网页或应用，或者查看相关网站或社交媒体是否有关于该网页或应用的报告。

5. 重启设备。重启设备有时可以解决许多问题。尝试关闭电脑、手机或其他设备，然后再重新启动，然后重新访问网页或应用。

总之，当你遇到“加载时出错请点按以重试”的提示时，不要慌张。尝试采用以上几种解决方案，有助于解决问题。如果这些方法仍然无法解决问题，你可以向互联网服务提供商或相关技术支持寻求帮助。通过不断学习和实践，你可以更好地掌握如何快速解决网络问题。

好吧，换回页面生成的Excel表以后就也可以配置了，我的问题

创建页

创建完成的界面

试一下

【错误记录】 ORA-01722: 无效数字

但是点击加载数据会报错

技术信息（仅提供给开发人员使用）

is_internal_error: false
ora_sqlcode: -1722
ora_sqlerrm: ORA-01722: 无效数字 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1316 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1048 ORA-06512: 在 "APEX_220200.WWV_FLOW_DYNAMIC_EXEC", line 1337 ORA-06512: 在 "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: 在 "SYS.WWV_DBMS_SQL_APEX_220200", line 837 ORA-06512: 在 "APEX_220200.WWV_FLOW_DYNAMIC_EXEC", line 1332 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 767 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1022 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1256 ORA-06512: 在 "APEX_220200.WWV_FLOW_PROCESS_NATIVE", line 1343 ORA-06512: 在 "APEX_220200.WWV_FLOW_PLUGIN", line 3163
component.type: APEX_APPLICATION_PAGE_PROCESS
component.id: 219909827118416632
component.name: 加载数据
error_backtrace:
ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1316 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1048 ORA-06512: 在 "APEX_220200.WWV_FLOW_DYNAMIC_EXEC", line 1337 ORA-06512: 在 "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: 在 "SYS.WWV_DBMS_SQL_APEX_220200", line 837 ORA-06512: 在 "APEX_220200.WWV_FLOW_DYNAMIC_EXEC", line 1332 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 767 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1022 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1256 ORA-06512: 在 "APEX_220200.WWV_FLOW_PROCESS_NATIVE", line 1343 ORA-06512: 在 "APEX_220200.WWV_FLOW_PLUGIN", line 3163 ORA-06512: 在 "APEX_220200.WWV_FLOW_PROCESS", line 170

这是为什么？

查阅了造成这个错误的原因可能有以下几种情况：

字符串转换为数字失败：当尝试将一个无法被解析为数字的字符串转换为数字类型时，就会出现这个错误。例如将字符串"abc"转换为数字类型时就会报错。

解决方法：确保要转换的字符串是有效的数字格式，如去掉多余的字符或空格，并确认字符串不含有非数字字符。

2.字符串中包含特殊字符或非法字符：该错误也可能发生在字符串中包含特殊字符或非法字符时，导致无法成功转换为数字类型。

解决方法：检查字符串是否包含特殊字符，如字母、符号等，如果存在则需要进行处理或删除这些字符。

3.数据类型不匹配：在一些情况下，这个错误可能是由于将一个与目标列数据类型不匹配的值插入到数据库中引起的。

那我好像懂了，是密码，密码想被我隐藏了，所以无法被解析，呜呜呜QAQ

【解决的方法】那就将密码的非法字符改掉就好了

这样导出来就没有非法字符了

可还是会报错

技术信息（仅提供给开发人员使用）

is_internal_error: false
ora_sqlcode: -1722
ora_sqlerrm: ORA-01722: 无效数字 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1316 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1048 ORA-06512: 在 "APEX_220200.WWV_FLOW_DYNAMIC_EXEC", line 1337 ORA-06512: 在 "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: 在 "SYS.WWV_DBMS_SQL_APEX_220200", line 837 ORA-06512: 在 "APEX_220200.WWV_FLOW_DYNAMIC_EXEC", line 1332 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 767 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1022 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1256 ORA-06512: 在 "APEX_220200.WWV_FLOW_PROCESS_NATIVE", line 1343 ORA-06512: 在 "APEX_220200.WWV_FLOW_PLUGIN", line 3163
component.type: APEX_APPLICATION_PAGE_PROCESS
component.id: 219909827118416632
component.name: 加载数据
error_backtrace:
ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1316 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1048 ORA-06512: 在 "APEX_220200.WWV_FLOW_DYNAMIC_EXEC", line 1337 ORA-06512: 在 "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: 在 "SYS.WWV_DBMS_SQL_APEX_220200", line 837 ORA-06512: 在 "APEX_220200.WWV_FLOW_DYNAMIC_EXEC", line 1332 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 767 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1022 ORA-06512: 在 "APEX_220200.WWV_FLOW_DATA_LOADING", line 1256 ORA-06512: 在 "APEX_220200.WWV_FLOW_PROCESS_NATIVE", line 1343 ORA-06512: 在 "APEX_220200.WWV_FLOW_PLUGIN", line 3163 ORA-06512: 在 "APEX_220200.WWV_FLOW_PROCESS", line 170

部门表导入

搞了部门表新的数据加载

展示的页面

导入测试

导入成功

导入成功！

原先有10条数据，导入后有20条数据

算是完成了成功的一小步，后续还需继续努力，昨天蒋老师还给了一个数据源加载的范例，里面涉及了租户和权限等级，我这边暂时是以用户角色页面来控制权限的，相对比较简单，参考范例有点难度。

-- 数据源加载 范例
create procedure IMPORT_USER(p_work_sheet in varchar2, p_file_name in nvarchar2,
                             p_user_id in number, p_tenant_id in number,
                             p_message out nvarchar2, p_row_count out number,
                             p_update_row_count out number)
    -- 用户信息导入
as
--     pragma autonomous_transaction;
    v_count            number(10) := 0;
    v_user_id          number(20) := 0;
    v_user_name        nvarchar2(64);
    v_job_number       nvarchar2(64);
    v_mobile           nvarchar2(64);
    v_email            nvarchar2(64);
    v_union_id         nvarchar2(64);
    v_ding_user_id     nvarchar2(64);
    v_rule_id          number(10) := 0;
    v_rule_level       number(10) := 0;
    v_row_count        number(10) := 0;
    v_row_count_update number(10) := 0;
    v_flag             nvarchar2(256);
    v_err_msg          nvarchar2(2000);
    cursor c_job is select p.line_number as LINE_NUMBER,
                           TRIM(p.col001)      as JOB_NUMBER,
                           TRIM(p.col002)      as USER_NAME,
                           TRIM(p.col003)      as PASSWORD,
                           TRIM(p.col004)      as RULE_NAME,
                           DECODE(TRIM(p.col005),'是',1,'否',0,2)      as IS_ENABLE,
                           p.col006      as REMARK,
                           p.col007,
                           p.col008,
                           p.col009,
                           p.col010
                    from apex_application_temp_files f,
                         table ( apex_data_parser.parse(
                                 p_content => f.blob_content, p_file_name => f.filename,
                                 p_xlsx_sheet_name => case when p_work_sheet is not null then p_work_sheet end,
                                 p_add_headers_row => 'Y', p_file_charset => 'AL32UTF8', p_max_rows => 2000,
                                 P_SKIP_ROWS => 1) ) p
                    where f.name = p_file_name;

begin
    p_message := 'fail';
    for c in c_job
        loop

            --判断操作者权限级别，是否满足条件
            select max(p.RULE_LEVEL)
            into v_rule_level
            from PAS_USER_RULE r
                     left join PAS_RULE p on r.RULE_ID = p.RULE_ID and p.TENANT_ID = p_tenant_id
            where r.USER_ID = p_user_id
              and r.DEL_FLAG = 0
              and r.TENANT_ID = p_tenant_id;
            -- 判断操作者权限级别，是否满足条件 最低要求超级管理员

            if v_rule_level > 20 then
                p_message := '权限不足';
                return;
            else
                -- 判断中台用户有效性
                select count(1)
                into v_count
                from PAS_BASIC_USER_V
                where TENANT_ID = p_tenant_id
                  and IS_LEAVE = 0
                  and JOB_NUMBER = trim(upper(c.JOB_NUMBER))
                  and ROWNUM = 1;

                if v_count = 0 then
                    p_message := '第' || c.LINE_NUMBER || '行,工号 ' || c.JOB_NUMBER || ' 不存在';
                    return;
                else

                    if c.RULE_NAME is null then
                        p_message := '第' || c.LINE_NUMBER || '行,角色 ' || c.RULE_NAME || ' 为空';
                        return;
                    ELSif c.PASSWORD is null then
                        p_message := '第' || c.LINE_NUMBER || '行,密码 ' || c.PASSWORD || ' 为空';
                        return;
                    ELSif c.IS_ENABLE = 2 then
                        p_message := '第' || c.LINE_NUMBER || '行,是否生效 ' || c.IS_ENABLE || ' 填写错误或为空';
                        return;
                    else
                        -- 判断角色有效性
                        select count(1), max(RULE_ID)
                        into v_count,v_rule_id
                        from PAS_RULE
                        where TENANT_ID = p_tenant_id
                          and DEL_FLAG = 0
                          and RULE_NAME = trim(c.RULE_NAME)
                          and RULE_LEVEL >= 20;

                        if v_count = 0 then
                            p_message := '第' || c.LINE_NUMBER || '行,角色 ' || c.RULE_NAME || ' 不存在';
                            return;
                        else
                            -- 查询中台用户信息
                            select max(MOBILE),
                                   max(NAME),
                                   max(USER_ID),
                                   max(EMAIL),
                                   max(UNION_ID),
                                   max(EXTRA_USER_ID),
                                   max(JOB_NUMBER)
                            into v_mobile,v_user_name,v_user_id,v_email,v_union_id,v_ding_user_id,v_job_number
                            from PAS_BASIC_USER_V
                            where TENANT_ID = p_tenant_id
                              and IS_LEAVE = 0
                              and JOB_NUMBER = trim(upper(c.JOB_NUMBER))
                              and ROWNUM = 1;


                            select count(1)
                            into v_count
                            from PAS_USERS
                            where USER_ID = v_user_id
                              and TENANT_ID = p_tenant_id
                              and DEL_FLAG = 0
                              and ROWNUM = 1;

                            -- 用户是否存在
                            if v_count = 0 then
                                insert into PAS_USERS (USER_ID, USER_NAME, JOB_NUMBER, MOBILE, EMAIL, PASSWORD,
                                                       UNION_ID,IS_ENABLE,
                                                       DING_USER_ID, REMARK, CREATED_BY, CREATED_DATE, TENANT_ID)
                                values (v_user_id, v_user_name, v_job_number, v_mobile, v_email, c.PASSWORD, v_union_id,
                                        C.IS_ENABLE,v_ding_user_id, c.REMARK, p_user_id, sysdate, p_tenant_id);
                                v_row_count := v_row_count + SQL%ROWCOUNT;


                                insert into PAS_USER_RULE (USER_ID, RULE_ID, CREATED_BY, DEL_FLAG, CREATED_DATE, TENANT_ID,IS_ENABLE)
                                values (v_user_id, v_rule_id, p_user_id, 0, sysdate, p_tenant_id,C.IS_ENABLE);

                            else
                                update PAS_USERS
                                set USER_NAME=v_user_name,
                                    MOBILE=v_mobile,
                                    EMAIL=v_email,
                                    PASSWORD=c.PASSWORD,
                                    UNION_ID=v_union_id,
                                    DING_USER_ID=v_ding_user_id,
                                    REMARK=c.REMARK,
                                    IS_ENABLE=C.IS_ENABLE,
                                    UPDATED_BY=p_user_id,
                                    UPDATED_DATE=sysdate
                                where JOB_NUMBER = trim(upper(c.JOB_NUMBER))
                                  and TENANT_ID = p_tenant_id;
                                v_row_count_update := v_row_count_update + SQL%ROWCOUNT;

                                update PAS_USER_RULE
                                set RULE_ID=v_rule_id,
                                    UPDATED_BY=p_user_id,
                                    IS_ENABLE=C.IS_ENABLE,
                                    UPDATED_DATE=sysdate
                                where TENANT_ID = p_tenant_id
                                  and USER_ID = v_user_id;

                            end if;


                        end if;
                    end if;


                end if;
            end if;

            p_message := 'success';
        end loop;
    WRITE_LOG(GET_FN_NAME(), 'debug', 'p_message: ' || p_message, p_user_id, p_tenant_id);
    p_row_count := v_row_count;
    p_update_row_count := v_row_count_update;
    commit;

exception

    when no_data_found then
        v_err_msg := sqlerrm || chr(13) || dbms_utility.format_error_backtrace;
        WRITE_LOG
            (GET_FN_NAME(), 'error', v_err_msg, p_user_id, p_tenant_id);
        v_flag := 'error: 数据没找到';
        rollback;

    when too_many_rows then
        v_err_msg := sqlerrm || chr(13) || dbms_utility.format_error_backtrace;
        WRITE_LOG
            (GET_FN_NAME(), 'error', v_err_msg, p_user_id, p_tenant_id);
        v_flag := 'error: 返回值多于1行';
        rollback;

    when others then
        v_err_msg := sqlerrm || chr(13) || dbms_utility.format_error_backtrace;
        WRITE_LOG
            (GET_FN_NAME(), 'error', v_err_msg, p_user_id, p_tenant_id);
        v_flag := 'error: 出现异常';
        rollback;


end ;
/

【代码记录】

存储过程IMPORT_USER_WXX

create procedure IMPORT_USER_WXX(p_work_sheet in varchar2, p_file_name in nvarchar2,
                                 p_user_id in number, p_message out nvarchar2, p_row_count out number,
                                 p_update_row_count out number)


    -- 用户信息导入
as
--     pragma autonomous_transaction;  自治性事务杂注

    v_row_count        number(10) := 0;
    v_row_count_update number(10) := 0;
    v_flag             nvarchar2(256);
    v_err_msg          nvarchar2(2000);
    cursor c_job is select p.line_number                             as LINE_NUMBER,
                           TRIM(p.col001)                            as NAME,
                           TRIM(p.col002)                            as GENDER,
                           TRIM(p.col003)                            as MOBILE,
                           TRIM(p.col004)                            as JOB_NUMBER,
                           TRIM(p.col005)                            as PASSWORD,
                           TRIM(p.col006)                            as EMAIL,
                           TRIM(p.col007)                            as DEVELOPER_ID,
                           DECODE(TRIM(p.col008), '是', 1, '否', 0, 2) as IS_LEAVE,
                           p.col009                                  as REMARK,
                           p.col010
                    from apex_application_temp_files f,
                         table ( apex_data_parser.parse(
                                 p_content => f.blob_content, p_file_name => f.filename,
                                 p_xlsx_sheet_name => p_work_sheet,
                                 p_add_headers_row => 'Y', p_file_charset => 'AL32UTF8', p_max_rows => 2000,
                                 P_SKIP_ROWS => 1) ) p
                    where f.name = p_file_name;

begin
    --p_message := 'fail';
    for c in c_job
        loop

            INSERT INTO USER_TEST_WXX2(NAME, GENDER, MOBILE, JOB_NUMBER, PASSWORD, EMAIL, DEVELOPER_ID, IS_LEAVE,
                                       REMARK,UPDATE_DATE)
            VALUES (c.NAME, c.GENDER, c.MOBILE, c.JOB_NUMBER, c.PASSWORD, c.EMAIL, c.DEVELOPER_ID, c.IS_LEAVE, c.REMARK,sysdate);
                 v_row_count := v_row_count + SQL%ROWCOUNT;

    p_message := v_row_count;
        end loop;
    WRITE_LOG(GET_FN_NAME(), 'debug', 'p_message: ' || p_message, p_user_id);
    p_row_count := v_row_count;
    p_update_row_count := v_row_count_update;
    commit;
    p_message := 'success';
exception

    when no_data_found then
        v_err_msg := sqlerrm || chr(13) || dbms_utility.format_error_backtrace;
        WRITE_LOG
            (GET_FN_NAME(), 'error', v_err_msg, p_user_id);
        v_flag := 'error: 数据没找到';
        rollback;

    when too_many_rows then
        v_err_msg := sqlerrm || chr(13) || dbms_utility.format_error_backtrace;
        WRITE_LOG
            (GET_FN_NAME(), 'error', v_err_msg, p_user_id);
        v_flag := 'error: 返回值多于1行';
        rollback;

    when others then
        v_err_msg := sqlerrm || chr(13) || dbms_utility.format_error_backtrace;
        WRITE_LOG
            (GET_FN_NAME(), 'error', v_err_msg, p_user_id);
        v_flag := 'error: 出现异常';
        rollback;

end ;
/

紧急插播一起事故

新增失败，查看会话

看似没问题，但是id无法自增是个大问题

技术信息（仅提供给开发人员使用）

is_internal_error: false
ora_sqlcode: -1400
ora_sqlerrm: ORA-01400: 无法将 NULL 插入 ("YWJA"."USER_TEST_WXX2"."USER_ID")
component.type: APEX_APPLICATION_PAGE_PROCESS
component.id: 212815993941508467
component.name: 处理表单用户信息-表单
error_statement:
(select USER_ID, NAME, GENDER, MOBILE, JOB_NUMBER, PASSWORD, EMAIL, DEVELOPER_ID, IS_LEAVE, REMARK, UPDATE_DATE from USER_TEST_WXX2 with check option)

破案了，原来是对应的表单上的sql语句未设置id主键自增，但是这是查询语句，好像也不符合鸭？

绊住了我半天，真的是太烦了究竟是哪一步出了问题？