{"id":9,"date":"2006-01-05T15:44:16","date_gmt":"2006-01-05T14:44:16","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/2006\/01\/05\/column-order\/"},"modified":"2006-01-05T15:44:16","modified_gmt":"2006-01-05T14:44:16","slug":"column-order","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=9","title":{"rendered":"Column order"},"content":{"rendered":"

Yesterday I came across a rather strange problem, which was luckily relatively easily solved. The problem is like this: I am using QDA (Qnxo Development Architecture) packages\u00a0to get to the data in my tables. In the Query Package there is a function to retrieve a row from the table. I had generated this code and it works correct for my situation and this will work on my development database. But, the order of the columns in the production database happened to be different. As different as just one column in another position.<\/p>\n

Consider the emp table on my development database:<\/p>\n

SQL<\/font>><\/font> <\/font>desc<\/font> emp
\n<\/font>Name<\/font> <\/font>Type<\/font>
\n<\/font>——– ————<\/font><\/em>
\nEMPNO <\/font>NUMBER<\/font>(4)<\/font>
\nENAME <\/font>VARCHAR2<\/font>(10)<\/font>
\nJOB <\/font>VARCHAR2<\/font>(9)<\/font>
\nMGR <\/font>NUMBER<\/font>(4)<\/font>
\nHIREDATE <\/font>DATE<\/font>
\nSAL <\/font>NUMBER<\/font>(7,2)<\/font>
\nCOMM <\/font>NUMBER<\/font>(7,2)<\/font>
\nDEPTNO <\/font>NUMBER<\/font>(2)<\/font><\/p>\n

The query code generated for this table is like this:<\/p>\n

FUNCTION<\/font> onerow <\/font>(<\/font>empno_in <\/font>IN<\/font> EMP_TP.EMPNO_t<\/font>)<\/font>
\n<\/font>RETURN<\/font> EMP_TP.EMP_rt
\n<\/font>IS<\/font>
\n\u00a0 onerow_rec EMP_TP.EMP_rt<\/font>;<\/font>
\n<\/font>BEGIN<\/font>
\n<\/font>\u00a0 SELECT <\/font>EMPNO<\/font>, <\/font>ENAME<\/font>, <\/font>JOB<\/font>, <\/font>MGR<\/font>, <\/font>HIREDATE<\/font>, <\/font>SAL<\/font>, <\/font>COMM<\/font>, <\/font>DEPTNO
\n<\/font>\u00a0\u00a0\u00a0 INTO<\/font> onerow_rec
\n<\/font>\u00a0 \u00a0 FROM<\/font> EMP
\n<\/font>\u00a0 WHERE <\/font>EMPNO <\/font>=<\/font> empno_in<\/font>;<\/font>
\n<\/font>\u00a0 RETURN<\/font> onerow_rec<\/font>;<\/font>
\n<\/font>EXCEPTION<\/font>
\n…
\n<\/font>END<\/font> onerow<\/font>;<\/font>
\n<\/font><\/p>\n

Now, on the production database, the table layout is a little bit different, for example, the hiredate was added to the table later, so the emp table has the following layout:<\/p>\n

SQL<\/font>><\/font> <\/font>desc<\/font> emp
\n<\/font>Name<\/font> <\/font>Type<\/font>
\n<\/font>——– ————<\/font><\/em>
\nEMPNO <\/font>NUMBER<\/font>(4)<\/font>
\nENAME <\/font>VARCHAR2<\/font>(10)<\/font>
\nJOB <\/font>VARCHAR2<\/font>(9)<\/font>
\nMGR <\/font>NUMBER<\/font>(4)<\/font>
\nSAL <\/font>NUMBER<\/font>(7,2)<\/font>
\nCOMM <\/font>NUMBER<\/font>(7,2)<\/font>
\nDEPTNO <\/font>NUMBER<\/font>(2)<\/font>
\nHIREDATE <\/font>DATE<\/font>
\n<\/font><\/p>\n

If we select the fields in the first order and then try to get them into a record, based on the %rowtype of the table, then this fails.<\/p>\n

The easy solution is, instead of using the column names in the select statement, use select *<\/strong> in the select statement. This results in a resultset where the columns have the same order as the order in the %rowtype based record.<\/p>\n

FUNCTION<\/font> onerow <\/font>(<\/font>empno_in <\/font>IN<\/font> EMP_TP.EMPNO_t<\/font>)<\/font>
\n<\/font>RETURN<\/font> EMP_TP.EMP_rt
\n<\/font>IS<\/font>
\n\u00a0 onerow_rec EMP_TP.EMP_rt<\/font>;<\/font>
\n<\/font>BEGIN<\/font>
\n<\/font>\u00a0 SELECT\u00a0<\/font>*<\/font>
\n<\/font>\u00a0\u00a0\u00a0 INTO<\/font> onerow_rec
\n<\/font>\u00a0 \u00a0 FROM<\/font> EMP
\n<\/font>\u00a0 WHERE <\/font>EMPNO <\/font>=<\/font> empno_in<\/font>;<\/font>
\n<\/font>\u00a0 RETURN<\/font> onerow_rec<\/font>;<\/font>
\n<\/font>EXCEPTION<\/font>
\n…
\n<\/font>END<\/font> onerow<\/font>;<\/font><\/p>\n

Luckily this is a parameter in Qnxo<\/a> which can easily be set to a different default value.<\/p>\n","protected":false},"excerpt":{"rendered":"

Yesterday I came across a rather strange problem, which was luckily relatively easily solved. The problem is like this: I am using QDA (Qnxo Development Architecture) packages\u00a0to get to the data in my tables. In the Query Package there is a function to retrieve a row from the table. I had generated this code and […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5,8],"tags":[],"class_list":["post-9","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","category-qnxo"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/9","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=9"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/9\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=9"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=9"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=9"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}