[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,一样没有问题。