oracle - 仅使用 sqlplus 上传二进制(BLOB)内容的最有效方法是什么
问题描述
我的用例是将二进制文件交付并安装到 Oracle 表中。这些文件在我的站点上本地可用,并且必须仅使用 sqlplus 安装在多个客户端站点上。我目前通过在本地使用加载文件的 Python 脚本来执行此操作,然后生成带有如下块的 sql 脚本。然后将脚本分发给客户端并使用 sqlplus 运行。
此方法有效,但有以下缺点:
- 如果必须上传许多和/或大型二进制文件,则 sql 脚本可能非常大
- 如果必须上传和/或二进制文件,运行 sql 脚本可能会很慢
我想知道,如果有人想出了更好的解决方案,或者现在如何改进?
例子.sql
DECLARE
l BLOB;
PROCEDURE i(t IN VARCHAR2) IS BEGIN dbms_lob.append(l, utl_encode.base64_decode(utl_raw.cast_to_raw(t))); END i;
BEGIN
dbms_lob.createtemporary(lob_loc=>l, cache=>FALSE, dur=>dbms_lob.session);
i('/9j/4AAQSkZJRgABAQEASABIAAD/2wBDAAgGBgcGBQgHBwcJCQgKDBQNDAsLDBkSEw8UHRofHh0aHBwgJC4nICIsIxwcKDcpLDAxNDQ0Hyc5PTgyPC4zNDL/2wBDAQkJCQwLDBgNDRgyIRwhMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjL/wgARCAIXBAADASIAAhEBAxEB/8QAGwAAAwEBAQEBAAAAAAAAAAAAAQIDAAQFBgf/xAAZAQEBAQEBAQAAAAAAAAAAAAAAAQIDBAX/2gAMAwEAAhADEAAAAfmNji5hqsiVRWTFLctqpFXSAvCUlCNbnY64BBlGXbZDlICAEOhscDEGz2oc95F4dEDq4+kHPRiQOwA2ADhdgGkqiOvWRVyLH0+c5T1VOWfTzGX0OAzvU5j0zIZqgDqCeuctTim3OMSTJlOhKRHpzkSkCPkBVlRLCiak8DLaN52DqnkENppSgzXyZSVYpPWTnbBRjjA4UPoTPiebAByjEI7A1RHRDWN6TVzPTztPUHP28edDHSg4htDus4g6y7VBGpC0EmsCtopzkDtNiffwY6W5MXMCXhjV5zA4GACocCA7ABwKJQW/PUrJCdkoZOo8gOmC5VLE1Uwi1UQswJ3kCjTDgxMnARsISS3OSPGyA1cRN1Nz3CNz2xEXy15LiyXROkqHXTml6QXin3rLyjsZORfY4l4n6qHCvTQlD1fIMV6jnHXzAW1DkHYY4hVyeOq8qxTEYNM1mAUClpRsDEqbYHQsSa8CFkInbyESq4TUxIvqnn0IKYTOamWY2lipk4xWR0ZMOIUOibQKty9hyrSYz6osdURkYkaIVKMHDAwYm6VNJ0KzBHJA2kAxuhO2kOSoRbkKOsxqrMy1kFloZsllJF4l08/WTWeqlOS5NMM6LIx18gBiCOqmKRbUMwGpLQbQx07nU9GXGo+NaVLMnO3fbeIJbmsC35s6mLyzpdsXlfmGwwQVXZsiUPQcdCgR18x0c87VEdMRGoBbc7CjYIqCeNSHRDoGiWAAh2c6gpK0hevm6TmGAWXDZSPldAuykXpZzp3cxEdDnJQ6Wk2VEtlqQYyqlFACBnk4ylQDOGboURkS0LzFvLK86omWkV6Bq2SWiEOs1l4KKZWRpFWehxY+kQ5GsZ5UJ9HFc0Tc5WUQRgPNkLeh5/ZYEpPWe6HHrLJI506Yqt5BAXdX5L88Hr42O3g2OgQBciBaa9FX5cRKRvDTEq7JRcSkzAwNd3KqjUiC+ShDZgzdTbErMY1GvrKnl7bOePZzSl5ehZxM+IdEllQMM6Zdi1+Qaz0TTR1RiazBZUxMqsCUmSKHQ2UhToc5B0QB0QYyUpZz7oxzbp0c+6Cc9WqcVKIPOZX0OLs4ZV6OfJ2V8/K7Swb84O2fOxUTEWM5hGxhsAMDvj1QsOnrArFUzCMGUJYBeQrK7RHWxHWFS10hV7uWpamiZc1MlYGOoazWc+7OWBuqRNalY7p5zbY22DsQY42eRTo5DZZZsdG5iimiTVoWjYxrpY6jnNXOHnrI22ADgbYLAi44rz3xz9HP1kB0zFaJMi9JMjEQdGxwM5J6qqltkjtlxNCQdQbAOGG2BscBquc46FETthBh086rhkI2OjA2NOkxmU08rxjdXP31wIdAOANsFkAarbUyLe555+kDkaIl6EtKy8AR5NNbis7mdESa9XmEt4CwtjduLo54BJmhnUDLYjqIDYgNJmxJrSJRFB1cjVGkCa/MB+jjqIDgZlCpIuINgBkILiBBRAbbA6ebBAwXnchs8J0M1HkegLGVknoxw7qhNG8aQZVmRzKVmwGnQkayJbp5bHPnoc+pQTn6ViG6Ii7A6qSNhVlC6PTw64xOotXJraIawImpqLpRCLQ1G9vinvnXmhTOuas+vOu3nn09efnZpculIgS2m7mKxpsWlol+tPMn38689XU7eOtU4DUKYUYjqoPJlCVYOzE2');
i('FBccDHWKrmWecAZSMrKYM5Ne2ZzP2MeYSRcVDsAggGpIYoSk7MSFpkNViNSQUg9hGtEaIKq8InY3F1Ry70JrwZllbp5MYNijVmUlHqFImMeewmZYngTpJFmV0LV5DXUpQbo5eojiTagReLuZfNp0hFhSms8w6VmoXKIOnkB28LaWYuFjiRuvm9BOPm7eZY0S5ec6EKTQ2BGwwcMUnUkqKyT64jUDCudT1KHM9FSQoyzzqLqhJq11kvdyWBWnKRrpgoVUfEnNTnHTATdnKPGsi0LRMDUk4uQPQ5xanUcHTzXC3N02bc3URF8kzMTXWvNezctdK8OrnSuWk0gmpXSYKvM2JCEouCiQo6HYBSxAcDbG2wDsEjUuOgYgwwG64NrPRNRQn2cmaCtxE9HzTunucmejkW/X5rFEZQzckw4BsQYsL0KbF6Obpshd+VH5umU1qTYolOeGcULzrq5UbqXhzaFbEe/TyJIZ1guYneFjSvAotVJ7sUlDt5DBOkgrzPR4b8otodxKbTOyCyLwvE14EHXM2c9R1EZ9j2c5kstRLoOXozEa82zq1JABpMIMCvfwdCTjaSl07CHLfngpSZ0kGwvp1WYBSZclmwMdCuOmoL3chJeuQzsusBXjL0MeOqrLrl5+7hpK/bDnS3n/AEXCeWOvnUKwAQR0zCmsh687XK4PNNkx0c+dF2CnFwJrErqByhsFLwOjhrGA1QoFIkejnqSLoCk6hBxIdcSNAh0QAKrMlVljoktSOJJ2UjIWFnckd0XBxWpZz9U0LJClizZs6DutmaLjSSsotzaV2gCznqOGF4DvFwqyxRPR56549POdBwSjztUcQB0qS2xmLC3lkixKtMAtPq57noiWs50qmdjo57FmPGiBwuzAAYCqVDrxAwod3FbJx5gupOgl4sZXcWd4DOpFdcjlQr2j22curzROqqdEKyL8tSvOehRoFQkgX1fL+jTzOWNlm0cPkYAeZeRQcpQ586Dzo4s+5zzumZJvKpMMq9Y3KnUkKWIOnEOlWSCXyoDGXHGVdgdiRcjr2OSokXnNzsSbkWaJXYo9ZJV5PQ5+rpjXItKRlkUI2XDY21icyoRgFsoASKSRMSbHE32EdGLCOG7uX0086eAtyKbnJlu21nLeV5UUdCc27VOZO3lpCDLd+OtkhVJXfmx6Eo0sMMsFcVBogcuOvpTzksOvlIa/Ou2ARgX5uvkLdXNMeSk6ctBVh0ELQuQtHoIAhermvzoz1U59YxFqMcu6oC5vVPI9AC3km6xWPfyC9vNjoEwbn62Eh18hmzAYayk7TBrULjcdjsTLoVkAEGwqVWvMkhSalejnKI1iKPU56rEZp9hytPGeeDhUnSbpfo4OuuZ41SZVpazLUt+XrjnZCFlwMaK+blS3RxqU2QygqQQayyLS3STn0TFtydJzgg9DibrTg7+H1Dz4UVUFom2IofGxYiWJjvQOPU5yRogvTzWEpK6wXE7uZFTFWWrvJCEIqDSnt4cVlsWbOTaOOleXrIJbtIc7KSadB2Q2UV5hzEvDphSjpWI50MrKO7cwwVysrxDp4Ya5CV5DwYgeeOnc2Ogc+NaBC6dKTq97PN3dNZTa1iSrON08ulojzGyVG01OmKUFGssd0ISasxTSR0ziUV16F6IPx2VCzlK9UxPQ870E872uQnDfl615GZAq8xlOBiA353Gd8HmpM1awOmU6CdPn95522MQVGxBqNEdfENfENfETXGWmpM+hBSIzc5LNz0JuJHSrGxdsHYln5iOmAWMxgVS/PfnV783TTQeEgpOyxzxLBANSGNsAjA2zCnVJ4ODp5elOiWvqcLOCCs2dZbARtMs3OTpVFSmnjoWYBWKlhPFZFVNZA6m5ushPr5CnP6vHZzUlpeoczno8vITqHMQo6BYYJUjakh6cznTGhoctpQ0KdBx70OUjRLkXp1nFRLkN0uecp7TgPSF59S5ytWycNR0nInZl5G6ulPNcCKrNFtoY6OU4vibFOwdi');
INSERT INTO BinaryResources (filename, mimeType, content) VALUES ('DOC.JPG', 'image/jpeg', l);
dbms_lob.freetemporary(lob_loc=>l);
END;
/
解决方案
您的 python 脚本应该与 Oracle 实例建立连接。然后您的python 脚本可以解析(即准备)SQL 语句文本INSERT INTO BinaryResources (filename, mimeType, content) VALUES (?, ?, ?)
。
然后将占位符值绑定到光标(字符串、字符串、blob)。您可以使用提供的任何 python-Oracle 提供程序函数创建一个 blob。
在填充至少 2-4k 大小的数组后使用数组插入执行方法。
推荐阅读
- python - 我正在创建一个 Django API。出于身份验证的目的,我实现了 Djoser
- javascript - 如何使用数组变量将值从一个函数传递到另一个函数
- c# - 协程等待 3 秒后不会加载新场景
- typescript - 为什么“字符串和数字”在 Typescript 中没有被评估为“从不”?
- typescript - 父类 TypeScript 无法识别子方法
- html - 容器外的图像是否有可能,如果我缩小图像会粘在浏览器的左侧?
- .net-core - GraphQL .Net 订阅 EventStreamFieldType 未订阅 IObserverable
- node.js - 内容安全策略问题节点 js 反应
- nginx - CERTBOT 在 NGINX 服务器中找不到“SUBDOMAIN”的有效 IP 地址
- r - 如何根据 var1 (x) 的不同截止值绘制 %Sensitivity (y) 并为 R 上的每个观察值显示一个点?