{"id":5,"date":"2014-03-20T16:12:00","date_gmt":"2014-03-20T16:12:00","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/2014\/03\/20\/impossible-to-change-virtual-column\/"},"modified":"2015-11-17T20:27:23","modified_gmt":"2015-11-17T19:27:23","slug":"impossible-to-change-virtual-column","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2014\/03\/20\/impossible-to-change-virtual-column\/","title":{"rendered":"Impossible to change virtual column"},"content":{"rendered":"<p>On my series of using datapump with remap_schema and virtual columns, I end up yesterday with a new case:<\/p>\n<p>&#8211; impossible to change a virtual column after the referenced function does not exist anymore.<\/p>\n<p>This bug is present on 11.2.0.3, corrected on 11.2.0.4.<\/p>\n<p>Example:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSQL&gt; grant connect,resource to u1 identified by u1;\r\nGrant succeeded.\r\n\r\nSQL&gt; connect u1\/u1\r\nConnected.\r\n\r\nSQL&gt; create or replace function f1 (v1 number)\r\n  return number DETERMINISTIC as\r\nbegin\r\n  return v1;\r\nend;\r\n\/  \r\n\r\nFunction created.\r\n\r\n-- Create table with virtual column referencing the function\r\n\r\nSQL&gt; create table t1 (c1 number, c2 number as (f1(c1)));\r\nTable created.\r\n\r\nSQL&gt; drop function f1;\r\nFunction dropped.\r\n\r\n-- Try to change the virtual column. Impossible!\r\nSQL&gt; alter table t1 drop column c2;\r\n\r\nalter table t1 drop column c2\r\n*\r\nERROR at line 1:\r\nORA-00904: &quot;U1&quot;.&quot;F1&quot;: invalid identifier\r\n\r\n-- Workaround is to recreate the function...\r\n\r\n-- clean up\r\nSQL&gt; connect \/ as sysdba\r\nConnected.\r\n\r\nSQL&gt; drop user u1 cascade;\r\nUser dropped.\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>On my series of using datapump with remap_schema and virtual columns, I end up yesterday with a new case: &#8211; impossible to change a virtual column after the referenced function does not exist anymore. This bug is present on 11.2.0.3, corrected on 11.2.0.4. Example: SQL&gt; grant connect,resource to u1 identified by u1; Grant succeeded. SQL&gt; [&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,6,2,4],"tags":[],"class_list":{"0":"post-5","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-bug","7":"category-oracle","8":"category-oracle-11g","9":"category-virtual-column","10":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/5","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=5"}],"version-history":[{"count":2,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/5\/revisions"}],"predecessor-version":[{"id":88,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/5\/revisions\/88"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=5"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=5"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=5"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}