{"id":9295,"date":"2019-02-10T11:43:38","date_gmt":"2019-02-10T11:43:38","guid":{"rendered":"https:\/\/dbtut.com\/?p=9295"},"modified":"2019-02-10T11:43:38","modified_gmt":"2019-02-10T11:43:38","slug":"query-the-table-with-the-clob-column-from-the-remote-database","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/","title":{"rendered":"Query the table with the Clob column from the remote database"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p>When a table containing a CLOB column is queried with a dblink from a remote database, it returns an error as ORA-22992. A table with a clob column can be queried from the remote database with two different method without this error.<\/p>\n<p>When you query with dblink, the error will be taken as below.<\/p>\n<p><span style=\"color: #ff0000;\"><em>ORA-22992: cannot use LOB locators selected from remote tables<\/em><\/span><\/p>\n<pre class=\"lang:default decode:true\">SQL&gt; select msg from aduruoz.message@remote_db;\r\nERROR:\r\nORA-22992: cannot use LOB locators selected from remote tables\r\n\r\nno rows selected<\/pre>\n<h3>First Method:<\/h3>\n<p>We create a view that queries the clob area with dbms_lob.substr in the database. Then we can query through this view without error from the remote database.<\/p>\n<p><strong>We create the view as below:<\/strong><\/p>\n<pre class=\"lang:default decode:true \">SQL&gt; CREATE OR REPLACE FORCE VIEW MESSAGE_VIEW (MSG)AS SELECT dbms_lob.substr(msg,4000,1) FROM ADURUOZ.MESSAGE;\r\n\r\nView created.<\/pre>\n<p><strong>You can query this view from other database with dblink without error:<\/strong><\/p>\n<pre class=\"lang:default decode:true \">SQL&gt; select * from aduruoz.message_view@remote_db;\r\n\r\nMSG\r\n--------------------------------------------------------------------------------\r\nThis is a message<\/pre>\n<h3>Second Method:<\/h3>\n<p>Create a global temp table in the remote database and insert the data in the database that we want to query into this global temp table. We can then query this global temp table.<\/p>\n<p>We are creating a Temp table in the Remote database:<\/p>\n<pre class=\"lang:default decode:true \">SQL&gt; create global temporary table message_global ( msg clob );\r\n\r\nTable created.<\/pre>\n<p><strong>Insert data into the temp table:<\/strong><\/p>\n<pre class=\"lang:default decode:true\">SQL&gt; insert into message_global select msg from aduruoz.message@remote_db;\r\n\r\n1 row created.<\/pre>\n<p><strong>Query the Temp table:<\/strong><\/p>\n<pre class=\"lang:default decode:true \">SQL&gt; select * from message_global;\r\n\r\nMSG\r\n--------------------------------------------------------------------------------\r\nThis is a message<\/pre>\n<p>As can be seen, it is possible to query tables that contain a clob column from a remote database.<\/p>\n\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_9295\" class=\"pvc_stats all  \" data-element-id=\"9295\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; When a table containing a CLOB column is queried with a dblink from a remote database, it returns an error as ORA-22992. A table with a clob column can be queried from the remote database with two different method without this error. When you query with dblink, the error will be taken as below. &hellip;<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_9295\" class=\"pvc_stats all  \" data-element-id=\"9295\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[4],"tags":[1983,1982,1981,1984],"class_list":["post-9295","post","type-post","status-publish","format-standard","","category-oracle","tag-cannot-use-lob-locators-selected-from-remote-tables","tag-ora-22992","tag-ora-22992-cannot-use-lob-locators-selected-from-remote-tables","tag-query-lob-column-from-the-remote-database"],"aioseo_notices":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.9 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Query the table with the Clob column from the remote database - Database Tutorials<\/title>\n<meta name=\"description\" content=\"Query the table with the Clob column from the remote database\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query the table with the Clob column from the remote database - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"Query the table with the Clob column from the remote database\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-02-10T11:43:38+00:00\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"Query the table with the Clob column from the remote database\",\"datePublished\":\"2019-02-10T11:43:38+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/\"},\"wordCount\":202,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"keywords\":[\"cannot use LOB locators selected from remote tables\",\"ORA-22992\",\"ORA-22992: cannot use LOB locators selected from remote tables\",\"query lob column from the remote database\"],\"articleSection\":[\"ORACLE\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/\",\"name\":\"Query the table with the Clob column from the remote database - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"datePublished\":\"2019-02-10T11:43:38+00:00\",\"description\":\"Query the table with the Clob column from the remote database\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query the table with the Clob column from the remote database\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Query the table with the Clob column from the remote database - Database Tutorials","description":"Query the table with the Clob column from the remote database","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:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/","og_locale":"en_US","og_type":"article","og_title":"Query the table with the Clob column from the remote database - Database Tutorials","og_description":"Query the table with the Clob column from the remote database","og_url":"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/","og_site_name":"Database Tutorials","article_published_time":"2019-02-10T11:43:38+00:00","author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"Query the table with the Clob column from the remote database","datePublished":"2019-02-10T11:43:38+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/"},"wordCount":202,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"keywords":["cannot use LOB locators selected from remote tables","ORA-22992","ORA-22992: cannot use LOB locators selected from remote tables","query lob column from the remote database"],"articleSection":["ORACLE"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/","url":"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/","name":"Query the table with the Clob column from the remote database - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"datePublished":"2019-02-10T11:43:38+00:00","description":"Query the table with the Clob column from the remote database","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/10\/query-the-table-with-the-clob-column-from-the-remote-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Query the table with the Clob column from the remote database"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/9295","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=9295"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/9295\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=9295"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=9295"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=9295"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}