仅使用sqlplus上传二进制(BLOB)内容的最有效方法是什么

我的用例是将二进制文件交付并安装到Oracle表中。 这些文件在我的站点上本地可用,并且必须仅通过使用sqlplus才能安装在多个客户端站点上。 我目前通过在本地使用Python脚本加载文件来执行此操作,然后使用以下代码块生成sql脚本。 然后将脚本分发给客户端并使用sqlplus运行。

此方法有效,但存在以下缺点:

  • 如果必须上传大量的二进制文件,则sql脚本可能真的很大
  • 如果必须上传和/或二进制文件,则运行sql脚本的速度可能会很慢

我想知道,是有人想出更好的解决方案还是现在该如何改进呢?

example.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;
/
wgx19820526 回答:仅使用sqlplus上传二进制(BLOB)内容的最有效方法是什么

您的python脚本应建立与Oracle实例的连接。然后您的python脚本可以解析(即准备)SQL语句文本INSERT INTO BinaryResources (filename,mimeType,content) VALUES (?,?,?)

然后将占位符值绑定到光标(字符串,字符串,blob)。您可以使用提供的任何python-Oracle提供程序功能创建一个blob。

在填充大小至少为2-4k的数组之后,使用数组插入执行方法。

本文链接:https://www.f2er.com/2742644.html

大家都在问