{"id":606,"date":"2014-02-28T06:18:49","date_gmt":"2014-02-28T06:18:49","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=606"},"modified":"2014-02-28T06:18:49","modified_gmt":"2014-02-28T05:18:49","slug":"","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=606","title":{"rendered":"Typical"},"content":{"rendered":"<p>After a presentation by Lucas Jellema I decided to try something with types in <a title=\"Oracle\" href=\"http:\/\/www.oracle.com\/\" target=\"_blank\">Oracle<\/a><img loading=\"lazy\" decoding=\"async\" title=\"table_EMP\" style=\"border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; float: right; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px\" border=\"0\" alt=\"table_EMP\" src=\"http:\/\/bar-solutions.com\/weblog\/img\/Types\/table_EMP.png\" width=\"240\" align=\"right\" height=\"169\" \/><\/a>. One of the issues posed in this presentation was that the type cannot include self-reference. Neither direct nor indirect. <\/p>\n<p><!--more--><\/p>\n<p>A table like the emp table cannot be expressed as an object type. The table has a column mgr which is a reference to another employee. <\/p>\n<p>So I tried something like this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"type_EMP_T\" style=\"border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; float: left; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px\" border=\"0\" alt=\"type_EMP_T\" src=\"http:\/\/bar-solutions.com\/weblog\/img\/Types\/type_EMP_T.png\" width=\"240\" align=\"left\" height=\"226\" \/> <\/p>\n<style type=\"text\/css\">\n<p>.code, .code pre\n{\n  font-size: small;\n  color: black;\n  font-family: consolas, \"Courier New\", courier, monospace;\n  background-color: #ffffff;\n  \/*white-space: pre;*\/\n}\n.code pre { margin: 0em; }\n.code .rem { color: #ff0000; }\n.code .kwrd { color: #008080; }\n.code .str { color: #0000ff; }\n.code .op { color: #0000c0; }\n.code .preproc { color: #cc6633; }\n.code .asp { background-color: #ffff00; }\n.code .html { color: #800000; }\n.code .attr { color: #ff0000; }\n.code .alt\n{\n  background-color: #f4f4f4;\n  width: 100%;\n  margin: 0em;\n}\n.code .lnum { color: #606060; }<\/style>\n<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">TYPE<\/span> emp_t <span class=\"kwrd\">AS<\/span> <span class=\"kwrd\">OBJECT<\/span>\r\n( empno <span class=\"kwrd\">number<\/span>(<span class=\"str\">4<\/span>)\r\n, ename <span class=\"kwrd\">varchar2<\/span>(<span class=\"str\">10<\/span>)\r\n, job <span class=\"kwrd\">varchar2<\/span>(<span class=\"str\">9<\/span>)\r\n, mgr emp_t\r\n, hiredate <span class=\"kwrd\">DATE<\/span>\r\n, sal <span class=\"kwrd\">number<\/span>(<span class=\"str\">7<\/span>,<span class=\"str\">2<\/span>)\r\n, comm <span class=\"kwrd\">number<\/span>(<span class=\"str\">7<\/span>,<span class=\"str\">2<\/span>)\r\n, deptno <span class=\"kwrd\">number(<span class=\"str\">2<\/span>)\r\n)<\/pre>\n<\/div>\n<p>&#160;<\/p>\n<p>This results in the following error:<\/p>\n<pre><div class=\"code\" align=\"left\">\r\n<span class=\"preproc\">Warning: Type created with compilation errors<\/span>\r\nErrors for TYPE DEMO.EMP_T:\r\nLINE\/COL ERROR\r\n-------- ------------------------------------------------------------------------------------\r\n1\/1      PLS-00318: type &quot;EMP_T&quot; is malformed because it is a non-REF mutually recursive type\r\n0\/0      PL\/SQL: Compilation unit analysis terminated\r\n<\/div><\/pre>\n<p>So, self referencing is not possible. But you can create hierarchical sets of types, where you extend one type in a child type. But the type could not include a self-reference, neither direct nor indirect.<\/p>\n<p>Time to try it out. Don&#8217;t ever take for granted what anyone says, including me, always try it out.<\/p>\n<p>My script was the following. I have a person type with all the information needed for this person and then, &#8216;below&#8217; that I have an employee type:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\"><img loading=\"lazy\" decoding=\"async\" title=\"type_PERS_T_EMP_T\" style=\"border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; float: left; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px\" border=\"0\" alt=\"type_PERS_T_EMP_T\" src=\"http:\/\/bar-solutions.com\/weblog\/img\/Types\/type_PERS_T_EMP_T.png\" width=\"175\" align=\"left\" height=\"246\" \/>TYPE<\/span> pers_t <span class=\"kwrd\">AS<\/span> <span class=\"kwrd\">OBJECT<\/span>\r\n( empno <span class=\"kwrd\">number<\/span>(<span class=\"str\">4<\/span>)\r\n, ename <span class=\"kwrd\">varchar2<\/span>(<span class=\"str\">10<\/span>)\r\n, job <span class=\"kwrd\">varchar2<\/span>(<span class=\"str\">9<\/span>)\r\n, hiredate <span class=\"kwrd\">DATE<\/span>\r\n, sal <span class=\"kwrd\">number<\/span>(<span class=\"str\">7<\/span>,<span class=\"str\">2<\/span>)\r\n, comm <span class=\"kwrd\">number<\/span>(<span class=\"str\">7<\/span>,<span class=\"str\">2<\/span>)\r\n, deptno <span class=\"kwrd\">number<\/span>(<span class=\"str\">2<\/span>)\r\n) <span class=\"kwrd\">NOT<\/span> <span class=\"kwrd\">FINAL<\/span><\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">TYPE<\/span> emp_t <span class=\"kwrd\">FORCE<\/span> <span class=\"kwrd\">UNDER<\/span> pers_t\r\n(\r\n  mgr pers_t\r\n)<\/pre>\n<\/div>\n<p>&#160;<\/p>\n<p>And the output for these statements is:<\/p>\n<pre><div class=\"code\" align=\"left\"><p>Type created<br \/>No errors for TYPE DEMO.PERS_T<br \/>Type created<br \/>No errors for TYPE DEMO.EMP_T<br \/><\/p><\/div><\/pre>\n<p>Now, I know you can create objects in Oracle which cannot be called easily. Think about a package, with an overloaded function that has ambiguity in its parameters. <a title=\"Steven Feuerstein\" href=\"http:\/\/www.stevenfeuerstein.com\/\" rel=\"nofollow\" target=\"_blank\">Steven Feuerstein<\/a> has a <a title=\"Learn PL\/SQL\" href=\"http:\/\/www.stevenfeuerstein.com\/learn\/building-code-analysis\" target=\"_blank\">great article<\/a> on this. Oracle will let you compile this package, but you can never call these programs. So, it&#8217;s time to test if it works. I created an anonymous block to try this:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">DECLARE<\/span>\r\n  <span class=\"kwrd\">TYPE<\/span> emps_tt <span class=\"kwrd\">IS<\/span> <span class=\"kwrd\">TABLE<\/span> <span class=\"kwrd\">OF<\/span> emp_t <span class=\"kwrd\">INDEX<\/span> <span class=\"kwrd\">BY<\/span> <span class=\"kwrd\">PLS_INTEGER<\/span>;\r\n  l_emps emps_tt;\r\n  l_mgr emp_t;\r\n  l_emp emp_t;\r\n  l_indx pls_integer;\r\n<span class=\"kwrd\">BEGIN<\/span>\r\n  <span class=\"kwrd\">FOR<\/span> rec <span class=\"kwrd\">IN<\/span> (<span class=\"kwrd\">SELECT<\/span> e.*\r\n    <span class=\"kwrd\">FROM<\/span> emp e\r\n  <span class=\"kwrd\">CONNECT<\/span> <span class=\"kwrd\">BY<\/span> <span class=\"kwrd\">PRIOR<\/span> e.empno = e.mgr\r\n  <span class=\"kwrd\">STAR<\/span>t <span class=\"kwrd\">WITH<\/span> e.mgr <span class=\"kwrd\">IS<\/span> <span class=\"kwrd\">NULL<\/span>) loop\r\n    <span class=\"rem\">-- Check if it's the PRESIDENT<\/span>\r\n    <span class=\"kwrd\">IF<\/span> rec.mgr <span class=\"kwrd\">IS<\/span> <span class=\"kwrd\">NULL<\/span> <span class=\"kwrd\">THEN<\/span>\r\n      l_mgr := emp_t(<span class=\"kwrd\">NULL<\/span>, <span class=\"kwrd\">NULL<\/span>, <span class=\"kwrd\">NULL<\/span>, <span class=\"kwrd\">NULL<\/span>, <span class=\"kwrd\">NULL<\/span>, <span class=\"kwrd\">NULL<\/span>, <span class=\"kwrd\">NULL<\/span>, <span class=\"kwrd\">NULL<\/span>);\r\n    <span class=\"kwrd\">ELSE<\/span>\r\n      l_mgr := l_emps(rec.mgr);\r\n    <span class=\"kwrd\">END<\/span> <span class=\"kwrd\">IF<\/span>;\r\n    l_emp := emp_t( rec.empno\r\n                  , rec.ename\r\n                  , rec.job\r\n                  , rec.hiredate\r\n                  , rec.sal\r\n                  , rec.comm\r\n                  , rec.deptno\r\n                  , l_mgr);\r\n    l_emps(rec.empno) := l_emp;\r\n  <span class=\"kwrd\">END<\/span> loop;\r\n  l_indx := l_emps.first;\r\n  <span class=\"kwrd\">LOOP<\/span>\r\n    <span class=\"kwrd\">EXIT<\/span> <span class=\"kwrd\">WHEN<\/span> l_indx <span class=\"kwrd\">IS<\/span> <span class=\"kwrd\">NULL<\/span>;\r\n    dbms_output.put_line(l_emps(l_indx).mgr.empno || '<span class=\"str\"> <\/span>' ||\r\n                         l_emps(l_indx).mgr.ename || '<span class=\"str\"> <span class=\"kwrd\">=&gt;<\/span> <\/span>' ||\r\n                         l_emps(l_indx).empno || '<span class=\"str\"> <\/span>' ||\r\n                         l_emps(l_indx).ename\r\n                        );\r\n    l_indx := l_emps.next(l_indx);\r\n  <span class=\"kwrd\">END<\/span> loop;\r\n<span class=\"kwrd\">END<\/span>;<\/pre>\n<\/div>\n<p>And, to my surprise, it worked.<\/p>\n<p>So, you cannot self-reference objects (which makes sense because the object being referenced doesn&#8217;t exist when you reference it), but you can reference parent objects. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>After a presentation by Lucas Jellema I decided to try something with types in Oracle. One of the issues posed in this presentation was that the type cannot include self-reference. Neither direct nor indirect.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5],"tags":[],"class_list":["post-606","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/606","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=606"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/606\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}