{"id":412,"date":"2019-05-15T06:02:30","date_gmt":"2019-05-15T10:02:30","guid":{"rendered":"http:\/\/dbtricks.com\/?p=412"},"modified":"2019-05-15T06:02:30","modified_gmt":"2019-05-15T10:02:30","slug":"weird-oracle-bug-ora-02019-when-creating-materialized-view","status":"publish","type":"post","link":"https:\/\/dbtricks.com\/?p=412","title":{"rendered":"Weird oracle bug &#8211; ORA-02019 when creating MATERIALIZED VIEW"},"content":{"rendered":"<p>ORA-02019: connection description for remote database not found<\/p>\n<p>TL;DR<br \/>\nWhen you try to create a materialized view on a different schema and the query selects from more than one remote object you get a misleading error  ORA-02019: connection description for remote database not found.<br \/>\nYour options:<br \/>\nchange the query to use one objects.<br \/>\nConnect as the other user and create the materialized view<br \/>\nUgly workaround \u2013 create a materialized view for one of the remote objects and use it in the query.<\/p>\n<p>Long version:<\/p>\n<p>Lately, I was trying to create a materialized view. The problem was that the query behind the view was using a sub query. As a result, when I tried to run the CREATE MATERIALIZED VIEW command, I got the famous <strong>ORA-22818: subquery expressions not allowed here.<\/strong><\/p>\n<pre lang=\"SQL\" line=\"1\">\r\ncreate MATERIALIZED VIEW u1.JOIN_EMP_DEPT_MV as \r\nselect a.employee_id,a.department_id ,(select department_name from hr.departments@orcl d where \r\n                                       d.department_id = a.department_id ) department_name\r\nfrom hr.employees@orcl a  ;<\/pre>\n<p>According to Oracle error codes, creating a materialized view containing a sub query is not supported<\/p>\n<pre lang=\"SQL\" line=\"1\">\r\nORA-22818: subquery expressions not allowed here\r\n22818. 00000 -  \"subquery expressions not allowed here\"\r\n*Cause:    An attempt was made to use a subquery expression where these\r\n           are not supported.\r\n*Action:   Rewrite the statement without the subquery expression.\r\n<\/pre>\n<p>In order to Rewrite the statement without the subquery expression I used the plsql guru Steven Feuerstein advice and <a href=\"http:\/\/stevenfeuersteinonplsql.blogspot.com\/2017\/08\/no-subqueries-allowed-in-materialized.html\">created a regular view with the complex query and tried to create a materialized on top of the view<\/a>. <\/p>\n<p>As a side note, this way of building a simple materialized view on top of a regular view is a good practice since it will allow you to change the materialized view without having to drop and recreate it. <\/p>\n<p>The first part of the process went well and the view was created and worked as expected<\/p>\n<pre lang=\"SQL\" line=\"1\">\r\ncreate  VIEW u1.JOIN_EMP_DEPT_V as \r\nselect a.employee_id,a.department_id ,\r\n                                     (select department_name from hr.departments@orcl d \r\n                                      where d.department_id = a.department_id ) department_name\r\nfrom hr.employees@orcl a  ;<\/pre>\n<p>However, once I tried to create the materialized view on the application schema using my private schema, I got the following message <\/p>\n<pre lang=\"SQL\" line=\"1\">\r\nconn U2\/U2\r\n\r\ncreate MATERIALIZED VIEW u1.JOIN_EMP_DEPT_MV as \r\nselect * from u1.JOIN_EMP_DEPT_V;\r\n\r\nORA-02019: connection description for remote database not found<\/pre>\n<p>This message is completely misleading. We know that the db link exists and we know it works. After all, the view was created using this db link.  <\/p>\n<pre lang=\"SQL\" line=\"1\">create or replace VIEW u1.JOIN_EMP_DEPT_V as \r\nselect a.employee_id,a.department_id from hr.employees@orcl a  ;\r\n<\/pre>\n<p>One remote object \u2013 Works!<\/p>\n<p>In addition, user U1 can create the the materialized view without a problem<\/p>\n<pre lang=\"SQL\" line=\"1\">\r\nconn U1\/U1\r\ncreate MATERIALIZED VIEW u1.JOIN_EMP_DEPT_MV as \r\nselect * from JOIN_EMP_DEPT_V;\r\n<\/pre>\n<p>also works!<\/p>\n<p>Apparently,  I am not the only one encountering the <strong>ORA-02019: connection description for remote database not found<\/strong> issue.<\/p>\n<p><a href=\"https:\/\/support.oracle.com\/knowledge\/Oracle%20Database%20Products\/2305974_1.html\">Creating materialized view of another user via private db link succeeded by selecting single table while failed by selecting more than one table (Doc ID 2305974.1)<\/a><\/p>\n<p>Sadly, according to Oracle, this is not bug. Basically, they claim that this is the way it works and you can ask for an ENHANCEMENT REQUEST.<\/p>\n<p>If creating the view with only one remote object or connecting as the other user are not an option , one ugly workaround would be to create  a materialized view for one of the tables and create the join it with one remote object<\/p>\n<pre lang=\"SQL\" line=\"1\">\r\ncreate MATERIALIZED VIEW u1.JUST_DEPT_MV as \r\nselect * from hr.departments@orcl;\r\n\r\n\r\ncreate or replace VIEW u1.JOIN_EMP_DEPT_V as \r\nselect a.employee_id,a.department_id ,(select department_name from u1.JUST_DEPT_MV d where d.department_id = a.department_id ) department_name\r\nfrom hr.employees@orcl a  ;\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>ORA-02019: connection description for remote database not found TL;DR When you try to create a materialized view on a different schema and the query selects from more than one remote object you get a misleading error ORA-02019: connection description for remote database not found. Your options: change the query to use one objects. Connect as [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[110,111],"class_list":["post-412","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-ora-02019","tag-ora-22818"],"_links":{"self":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/412","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=412"}],"version-history":[{"count":10,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/412\/revisions"}],"predecessor-version":[{"id":446,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/412\/revisions\/446"}],"wp:attachment":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=412"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=412"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=412"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}