{"id":336,"date":"2009-10-02T16:33:16","date_gmt":"2009-10-02T14:33:16","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=336"},"modified":"2009-10-02T16:33:16","modified_gmt":"2009-10-02T14:33:16","slug":"reading-values-from-a-varchar2-indexed-associative-array","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=336","title":{"rendered":"Reading values from a varchar2 indexed Associative Array"},"content":{"rendered":"

\"logo-oracle\"<\/a>Oracle<\/a> has provided us with collections since Oracle 7. It is one of the most renamed features in the Oracle database. In Oracle 7 they were called \u2018PL\/SQL Tables\u2019. In Oracle 8 they were renamed to \u2018Index By Tables\u2019 and since Oracle 9 they are called \u2018Associative Arrays\u2019. If you index by an integer value then you can read the values by using an integer index variable. If the array is dense, that means all the indexes, from the first to the last, have a value associated to them you can even use a numeric for loop to get to the data. But what if you index by a string value, which is a possibility since Oracle 9i. <\/p>\n

<\/div>\n

<\/p>\n

\"9i\"<\/a>The solution is actually rather simple. Using a simple loop. First of all create an index variable of the same type you use to index the collection. It would be kind of silly to declare the collection using a varchar2(10) and then declare an index variable as a varchar2(5). If an index value would be \u2018Patrick\u2019 then this wouldn\u2019t fit in the index variable resulting in a error.<\/p>\n

\n
ORA<\/span>-<\/span>06502<\/span>: PL<\/span>\/<\/span>SQL: numeric <\/span>or<\/span> value error: <\/span>character<\/span> string buffer too small<\/span><\/pre>\n
<\/div>\n
Then assign the index variable the value of the first index used. Now start the loop and do the stuff you want with the collection. At the end of the loop assign the next index value to the variable. If you try to read past the end of the collection, then the value of the index will be NULL, which is a great marker to exit the loop.<\/p>\n
If you put the exit clause as the first statement in the loop, then there is no reason to check for any items present in the collection. If you want to check how many items are present, then use <collection>.count.<\/p>\n
I have created a small script to demonstrate how it can be done:<\/p>\n
\n
declare<\/span>\n  subtype index_t <\/span>is<\/span> <\/span>varchar2<\/span>(<\/span>10<\/span>);\n  type  birthdates_tt <\/span>is<\/span> <\/span>table<\/span> <\/span>of<\/span> date <\/span>index<\/span> <\/span>by<\/span> index_t;\n  l_birthdates birthdates_tt;\n  l_indx index_t;\n<\/span>begin<\/span>\n  <\/span>--<\/span> Fill the table with the birthdates<\/span>\n<\/span>  l_birthdates(<\/span>'<\/span>Patrick<\/span>'<\/span>) :<\/span>=<\/span> to_date(<\/span>'<\/span>29-12-1972<\/span>'<\/span>,<\/span>'<\/span>DD-MM-YYYY<\/span>'<\/span>);\n  l_birthdates(<\/span>'<\/span>Dana<\/span>'<\/span>) :<\/span>=<\/span> to_date(<\/span>'<\/span>06-03-1976<\/span>'<\/span>,<\/span>'<\/span>DD-MM-YYYY<\/span>'<\/span>);\n  l_birthdates(<\/span>'<\/span>Quinty<\/span>'<\/span>) :<\/span>=<\/span> to_date(<\/span>'<\/span>18-12-1998<\/span>'<\/span>,<\/span>'<\/span>DD-MM-YYYY<\/span>'<\/span>);\n  l_birthdates(<\/span>'<\/span>Kayleigh<\/span>'<\/span>) :<\/span>=<\/span> to_date(<\/span>'<\/span>19-11-2000<\/span>'<\/span>,<\/span>'<\/span>DD-MM-YYYY<\/span>'<\/span>);\n  l_birthdates(<\/span>'<\/span>Mitchell<\/span>'<\/span>) :<\/span>=<\/span> to_date(<\/span>'<\/span>23-06-2003<\/span>'<\/span>,<\/span>'<\/span>DD-MM-YYYY<\/span>'<\/span>);\n\n  <\/span>--<\/span> make index variable the same as the first from the list<\/span>\n<\/span>  l_indx :<\/span>=<\/span> l_birthdates.first;\n  <\/span>--<\/span> start a simple loop<\/span>\n<\/span>  loop\n    <\/span>--<\/span> exit the loop when we passed the end<\/span>\n<\/span>    <\/span>exit<\/span> <\/span>when<\/span> l_indx <\/span>is<\/span> <\/span>null<\/span>;\n    <\/span>--<\/span> write the value of the index and the value at index in the AA to screen<\/span>\n<\/span>    dbms_output.put_line(l_indx <\/span>||<\/span>'<\/span> => <\/span>'<\/span>||<\/span>l_birthdates(l_indx));\n    <\/span>--<\/span> select the next index value from the list<\/span>\n<\/span>    l_indx :<\/span>=<\/span> l_birthdates.<\/span>next<\/span>(l_indx);\n  <\/span>end<\/span> loop;\n<\/span>end<\/span>;\n<\/span>\/<\/span><\/pre>\n
<\/div>\n
\"WebButton\"<\/a>I hope this information helps in leveraging the power of collections. That is no need to know the index values upfront, because you can traverse the collection at runtime where you don\u2019t know the values of the indexes used. Some of this behavior and more about collections will be part of my presentation about Optimizing SQL with Collections<\/a> at OPP2009<\/a>. <\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle has provided us with collections since Oracle 7. It is one of the most renamed features in the Oracle database. In Oracle 7 they were called \u2018PL\/SQL Tables\u2019. In Oracle 8 they were renamed to \u2018Index By Tables\u2019 and since Oracle 9 they are called \u2018Associative Arrays\u2019. If you index by an integer value […]<\/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-336","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\/336","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=336"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/336\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=336"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=336"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=336"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}