title: Sorting Data at the runtime in Oracle PL/SQL using Associative array
body: <p>I am trying to sort a data from runtime query and no way I can do that without use of a collection. Essentially, I have 2 columns in Service table - SERVICE_ID, SERVICE_NAME. I have created an Associative array in a package so that I can use it in my procedure.</p>
  <pre><code>TYPE g_vc_arr IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;
  </code></pre>
  <p>In my pl/sql block I am creating a variable like below:</p>
  <pre><code>service_list my_pkg.g_vc_arr;
  </code></pre>
  <p>I am assigning the SERVICE_ID and SERVICE_NAME to this variable like below:</p>
  <pre><code>LOOP
    service_list(services.SERVICE_ID) := services.SERVICE_NAME;
  END LOOP;
  </code></pre>
  <p>Now, I am using this to sort the name column like below snippet. I am able to print the name but in this process, I am losing the ID.</p>
  <pre><code>for query_result_row in (SELECT * from table(service_list) order by 1) loop
          dbms_output.put_line(query_result_row.COLUMN_VALUE);
  end loop;
  </code></pre>
  <p>I need both ID and NAME to process further. How can I get both?</p>