[20230531]insert blob数据类型.txt
[20230531]insert blob数据类型.txt
--//链接https://connor-mcdonald.com/2023/05/29/why-i-blog/ 提供插入blob数据类型的简单方法,测试看看.
--//正常插入要先插入一个empty_blob(),然后获得一个定位指针,使用dbms_lob.loadfromfile插入.
1.环境:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
2.建立测试:
SCOTT@test01p> create table t ( b blob);
Table created.
CREATE OR REPLACE DIRECTORY TMP_EXPDP AS 'D:\tmp\expdp\';
GRANT EXECUTE, READ, WRITE ON DIRECTORY TMP_EXPDP TO SCOTT WITH GRANT OPTION;
--//直接测试作者提供的方法:
SCOTT@test01p> insert into t values (to_blob(bfilename('TMP_EXPDP','export.log')));
1 row created.
SCOTT@test01p> commit ;
Commit complete.
3.验证是否正确.
--//在toad下执行:
select * from t;
--//另存为a.log文件
D:\tmp\expdp>diff a.log export.log
--//说明OK。
SCOTT@test01p> select to_char(b) from t;
TO_CHAR(B)
-----------------------------------------------------------------------------------------------------------------------------------------------
;;;
Export: Release 12.2.0.1.0 - Production on Sat Jan 21 22:49:47 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/a******@test01p directory=TMP_EXPDP tables=t dumpfile=t1.dmp access_method=external_table
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T" 2.855 MB 100000 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
D:\TMP\EXPDP\T1.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jan 21 22:51:10 2023 elapsed 0 00:01:05
--//说明没有任何问题。尝试更大的文件8K,一样没有问题。
--//链接https://connor-mcdonald.com/2023/05/29/why-i-blog/ 提供插入blob数据类型的简单方法,测试看看.
--//正常插入要先插入一个empty_blob(),然后获得一个定位指针,使用dbms_lob.loadfromfile插入.
1.环境:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
2.建立测试:
SCOTT@test01p> create table t ( b blob);
Table created.
CREATE OR REPLACE DIRECTORY TMP_EXPDP AS 'D:\tmp\expdp\';
GRANT EXECUTE, READ, WRITE ON DIRECTORY TMP_EXPDP TO SCOTT WITH GRANT OPTION;
--//直接测试作者提供的方法:
SCOTT@test01p> insert into t values (to_blob(bfilename('TMP_EXPDP','export.log')));
1 row created.
SCOTT@test01p> commit ;
Commit complete.
3.验证是否正确.
--//在toad下执行:
select * from t;
--//另存为a.log文件
D:\tmp\expdp>diff a.log export.log
--//说明OK。
SCOTT@test01p> select to_char(b) from t;
TO_CHAR(B)
-----------------------------------------------------------------------------------------------------------------------------------------------
;;;
Export: Release 12.2.0.1.0 - Production on Sat Jan 21 22:49:47 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/a******@test01p directory=TMP_EXPDP tables=t dumpfile=t1.dmp access_method=external_table
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T" 2.855 MB 100000 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
D:\TMP\EXPDP\T1.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jan 21 22:51:10 2023 elapsed 0 00:01:05
--//说明没有任何问题。尝试更大的文件8K,一样没有问题。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
2021-06-02 [20210602]分析library cache转储 5.txt