{"id":3109,"date":"2014-01-17T02:06:00","date_gmt":"2014-01-17T01:06:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/"},"modified":"2014-01-17T02:06:00","modified_gmt":"2014-01-17T01:06:00","slug":"oracle-lobs-infer-the-file-type-with-dbmslob-package","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/","title":{"rendered":"Oracle LOBs: Infer the file type with dbms_lob package"},"content":{"rendered":"<p>LOBs (Large OBjects) first appeared in Oracle 7 and were created to store large amount of data such as document files, video, pictures, etc. Today, we can store up to 128 TB of data in a LOB, depending on the DB_BLOCK_SIZE parameter settings. In this posting, I will show you how to load LOB files into the Oracle database and will present a way to identify the file type of the LOB stored in the database, based on the LOB value column only.<\/p>\n<p>Each file type is associated to a &#8220;file signature&#8221;. This signature is composed of several bytes (i. e. 00 01), and allows to uniquely identify a file type (i.e. zip file, png file, etc). The signature corresponds to the first bytes of the file. To identify the file type of a LOB stored in a database, we just have to extract the first bytes of the LOB value and then compare them to a list of known bytes. I have created a PL\/SQL script for that purpose.<\/p>\n<p>The first step is to create a table containing LOB files. Here, I will create a simple table to store internal Binary objects (BLOB). The statement used is:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">CREATE TABLE app_doc (\u00a0 doc_id NUMBER,\u00a0 doc_value BLOB);\n\u00a0\nSQL&gt; desc app_doc;\nName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Null?\u00a0\u00a0\u00a0 Type\n----------------------------------------- -------- ----------------------------\nDOC_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER\nDOC_VALUE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BLOB<\/pre>\n<p>Now, I want to load a file from the operating file system to the table APP_DOC. The file to load is a picture saved as PNG format. The path is &#8220;\/oracle\/documents\/my_pic.png&#8221;.<\/p>\n<p>First, we must create an Oracle directory in order to access the file:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; CREATE OR REPLACE DIRECTORY DIR_DOCUMENTS AS '\/oracle\/documents';<\/pre>\n<p>We then use the DBMS_LOB package through a PL\/SQL block to load a file in the database:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">DECLARE\n\u00a0 src_lob\u00a0 BFILE := BFILENAME('DIR_DOCUMENTS', 'my_pic.png');\n\u00a0 dest_lob BLOB;\nBEGIN\n\u00a0 INSERT INTO app_doc VALUES(1, EMPTY_BLOB())\n\u00a0\u00a0\u00a0\u00a0 RETURNING doc_value INTO dest_lob;\n\u00a0 DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);\n\u00a0 DBMS_LOB.LoadFromFile( DEST_LOB =&gt; dest_lob,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SRC_LOB\u00a0 =&gt; src_lob,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AMOUNT\u00a0\u00a0 =&gt; DBMS_LOB.GETLENGTH(src_lob) );\n\u00a0 DBMS_LOB.CLOSE(src_lob);\n\u00a0 COMMIT;\nEND;\n\/<\/pre>\n<p>The table has now a new record:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select count (*) from app_doc;\n\u00a0 COUNT(*)\n----------\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/pre>\n<p>I have populated the table with 28 files of several types (png, pdf, bmp, etc.) and the table has 28 records. Here is the list of files loaded into the table:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">oracle@vmtest:\/oracle\/documents\/ [rdbms11203] ls -1\n001.png\n002.png\n003.png\n004.png\n005.png\n006.png\n007.png\n008.png\n009.png\n010.png\n011.png\n012.png\n013.png\n014.png\n015.png\n016.png\n017.png\n018.png\n100021667-logo-dbi-services-sa.jpg\ndbi_twitter_bigger.jpg\ne18294-SecureFiles_and_Large_Objects_Developers_Guide.pdf\ne25523-VLDB_and_Partitioning_Guide.pdf\nfile.zip\nMybmp2.bmp\nMybmp.bmp\nMydoc1.doc\nmypdf.pdf\ntext3.txt<\/pre>\n<p>Here is an extract of the PL\/SQL written to identify the LOB file type:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">-- Create a temporary table to store known document types\nCREATE GLOBAL TEMPORARY TABLE temp_doc_types (id NUMBER, type VARCHAR(5), sign VARCHAR(4)) ON COMMIT DELETE ROWS;<\/pre>\n<p>&nbsp;<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">-- Populate the temporary table\nINSERT INTO temp_doc_types VALUES (1,'jpeg','FFD8');\nINSERT INTO temp_doc_types VALUES (2,'gif','4749');\nINSERT INTO temp_doc_types VALUES (3,'png','8950');\nINSERT INTO temp_doc_types VALUES (4,'bmp','424D');\nINSERT INTO temp_doc_types VALUES (5,'pdf','2550');\nINSERT INTO temp_doc_types VALUES (6,'doc','D0CF');\nINSERT INTO temp_doc_types VALUES (7,'zip','504B');\nINSERT INTO temp_doc_types VALUES (8,'rar','5261');<\/pre>\n<p>&nbsp;<\/p>\n<p>In this example, I chose to compare only the four first bytes of the LOB value. The number of bytes composing a file signature can be larger, but to simplify the example, I only used file signatures that have four bytes.<\/p>\n<p>The number of files of each extension is returned by this statement:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select decode(type,NULL,'Unknown document type',type) as \"TYPE OF DOCUMENT\",\n\u00a0 2\u00a0 count(*) as \"NUMBER OF DOCUMENTS\"\n\u00a0 3\u00a0 from temp_doc_types a,\n\u00a0 4\u00a0 (select (dbms_lob.substr(doc_value,2,1)) as FILE_TYPE from app_doc) b\n\u00a0 5\u00a0 where a.sign(+)=b.FILE_TYPE group by a.type;\n\u00a0\nTYPE OF DOCUMENT\u00a0\u00a0\u00a0\u00a0\u00a0 NUMBER OF DOCUMENTS\n--------------------- -------------------\nUnknown document type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\ndoc\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\nbmp\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\npng\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18\npdf\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3\njpeg\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\nzip\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/pre>\n<p>&nbsp;<\/p>\n<p>The statement gets the first four bytes of each LOB and compares them to the temporary table, matching the LOB to a type.<\/p>\n<ul>\n<li>The decode function is used to display &#8216;Unknown document type&#8217; when NULL is returned and no extension matches between the LOB and the temporary table. This is the case for text files which have no file signature.<\/li>\n<li>The DBMS_LOB.SUBSTR function is used to retrieve only the first four bytes from the LOB value. Parameters are the source LOB value, the amount of bytes to read (4), and the starting position (1).<\/li>\n<li>An outer join is used to count LOBs not matching the temporary table. This will correspond to unidentified files types.<\/li>\n<\/ul>\n<p>To finish, I drop the temporary table before exiting the job:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">-- The temporary table is dropped before exit\nDROP TABLE temp_doc_types;\nexit;<\/pre>\n<p>And just for the eyes, this is the result in a HTML fashion, generated directly from sqlplus using the MARKUP option and an css style sheet. I let you read the Oracle documentation in order to know how to use the MARKUP option \ud83d\ude09<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/sql_report.png\" alt=\"sql_report\" width=\"725\" height=\"546\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>LOBs (Large OBjects) first appeared in Oracle 7 and were created to store large amount of data such as document files, video, pictures, etc. Today, we can store up to 128 TB of data in a LOB, depending on the DB_BLOCK_SIZE parameter settings. In this posting, I will show you how to load LOB files [&hellip;]<\/p>\n","protected":false},"author":27,"featured_media":3110,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[378,206,209,379],"type_dbi":[],"class_list":["post-3109","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-application-integration-middleware","tag-extension","tag-lob","tag-oracle-12c","tag-type"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Oracle LOBs: Infer the file type with dbms_lob package - dbi Blog<\/title>\n<meta name=\"description\" content=\"In this posting, I will show you how to load LOB files into the Oracle database and will present a way to identify the file type of the LOB stored in the database, based on the LOB value column only.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle LOBs: Infer the file type with dbms_lob package\" \/>\n<meta property=\"og:description\" content=\"In this posting, I will show you how to load LOB files into the Oracle database and will present a way to identify the file type of the LOB stored in the database, based on the LOB value column only.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-17T01:06:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/sql_report.png\" \/>\n\t<meta property=\"og:image:width\" content=\"725\" \/>\n\t<meta property=\"og:image:height\" content=\"546\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle LOBs: Infer the file type with dbms_lob package\",\"datePublished\":\"2014-01-17T01:06:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/\"},\"wordCount\":552,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/sql_report.png\",\"keywords\":[\"Extension\",\"LOB\",\"Oracle 12c\",\"Type\"],\"articleSection\":[\"Application integration &amp; Middleware\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/\",\"name\":\"Oracle LOBs: Infer the file type with dbms_lob package - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/sql_report.png\",\"datePublished\":\"2014-01-17T01:06:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"In this posting, I will show you how to load LOB files into the Oracle database and will present a way to identify the file type of the LOB stored in the database, based on the LOB value column only.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/sql_report.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/sql_report.png\",\"width\":725,\"height\":546},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle LOBs: Infer the file type with dbms_lob package\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle LOBs: Infer the file type with dbms_lob package - dbi Blog","description":"In this posting, I will show you how to load LOB files into the Oracle database and will present a way to identify the file type of the LOB stored in the database, based on the LOB value column only.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/","og_locale":"en_US","og_type":"article","og_title":"Oracle LOBs: Infer the file type with dbms_lob package","og_description":"In this posting, I will show you how to load LOB files into the Oracle database and will present a way to identify the file type of the LOB stored in the database, based on the LOB value column only.","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/","og_site_name":"dbi Blog","article_published_time":"2014-01-17T01:06:00+00:00","og_image":[{"width":725,"height":546,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/sql_report.png","type":"image\/png"}],"author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle LOBs: Infer the file type with dbms_lob package","datePublished":"2014-01-17T01:06:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/"},"wordCount":552,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/sql_report.png","keywords":["Extension","LOB","Oracle 12c","Type"],"articleSection":["Application integration &amp; Middleware"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/","name":"Oracle LOBs: Infer the file type with dbms_lob package - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/sql_report.png","datePublished":"2014-01-17T01:06:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"In this posting, I will show you how to load LOB files into the Oracle database and will present a way to identify the file type of the LOB stored in the database, based on the LOB value column only.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/sql_report.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/sql_report.png","width":725,"height":546},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-lobs-infer-the-file-type-with-dbmslob-package\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle LOBs: Infer the file type with dbms_lob package"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3109","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=3109"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3109\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/3110"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=3109"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=3109"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=3109"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=3109"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}