title: How do I combine mixed data types? Is Varray with Varchar2 the right choice?
body: <p>I’m trying to combine records from several queries where some of the fields have different data types.  These different queries are used not only to select the desired records but also to make sure that each query has the exact same field names in the exact same order.  Each of these queries will return ten or fewer records each.  Also, it is possible that one or more of these queries will return no records at all.</p>
  
  <p>The difficulty is that the data types are different in these various data sources.  For example a column named <code>fld_Shirt_Size</code> may have numeric values such as <code>(15.5, 17, 20)</code> from one data source but another data source for this same field has text values <code>('Small', 'Medium', 'Large')</code>.  Both data sources can contain nulls mixed in with other values.</p>
  
  <p>I tried to use a UNION or a UNION ALL method of joining these queries and the different data types prevented this from working.  I then tried creating a VARRAY using VARCHAR2 as the data type and then populating that array with the records from each query but am again getting errors about data types.</p>
  
  <p>I need to keep the numbers as numbers and the text as text so using a conversion method to make every value a text string is not an option.  The simple example above is to illustrate the situation I am working with but the real data is financial and tax data where some fields don’t exist for some years and need a text value to note this (i.e. <code>‘N/A’</code> or <code>‘Did Not Exist’</code>) and others fields have NULL values that are not the same as a zero currency value which is why the numbers need to stay numbers and the text needs to stay text even though they are all in the same field.  These results are going to be displayed and will later be used in Excel.  Each of the queries have been used to ‘groomed’ the underlying data to make sure all of them all have the same field names in the same order.</p>
  
  <p>I am using PL/SQL Oracle 9i in a read only environment (no make table privileges, etc.) and also I am very new to sql, but have many years of experience with MS Access.  No ODBC connection to this data is available.</p>
  
  <p>My beta code (included below) produces the following error messages:</p>
  
  <pre><code>ORA-06550: line 87, column 54:  
  PLS-00382: expression is of the wrong type  
  ORA-06550: line 87, column 9:  
  PL/SQL: Statement ignored  
  ORA-06550: line 90, column 54:  
  PLS-00201: identifier ‘D_INTERANL_ID’ must be declared  
  ORA-06550: line 90, column 9:  
  PL/SQL: Statement ignored…  
  </code></pre>
  
  <p>The error message then repeats for each of the other similar lines.</p>
  
  <p>This beta code has just a few fields from the standard layout for each query and is currently just being used to finding a workable method.  The real standard layout queries have about 350 fields each and there are 15 of these queries that need to have their results combined with additional set of quires being added each year.</p>
  
  <p>Any help is greatly appreciated.  I’ve been searching this and other sites for days trying to find the right code and to get the syntax to work. </p>
  
  <pre><code>DECLARE
    MyTID varchar2(10);
    MyRecordCountOfVarray number;
    MyCursorRowCounter number;
    MyRowPositionInVarray number;  
  </code></pre>
  
  <blockquote>
  <pre><code>CURSOR Cursor_rst1 IS
         SELECT d_owner_internal_id,
                d_internal_id,
                d_tid,
                d_entity_name,
                d_form_seq
           FROM rtns.itas_rtn_ct_1120_cor tblRecords
          WHERE d_form_seq = '2710' --Tax Year =2003
            AND D_TID = MyTID;  
  </code></pre>
  </blockquote>
  
  <pre><code>CURSOR Cursor_rst2 IS
         SELECT d_owner_internal_id,
                d_internal_id,
                d_tid,
                d_entity_name,
                d_form_seq
           FROM rtns.itas_rtn_ct_1120_cor tblRecords 
          WHERE d_form_seq = '11625' --Tax Year =2004
            AND D_TID = MyTID;  
  </code></pre>
  
  <blockquote>
  <pre><code>  TYPE MyVarray IS VARRAY(500) OF VARCHAR2(100);
    v_owner_internal_id             MyVarray :=MyVarray();
    v_internal_id                   MyVarray :=MyVarray();
    v_tid                           MyVarray :=MyVarray();
    v_entity_name                   MyVarray :=MyVarray();
    v_form_seq                      MyVarray :=MyVarray();  
  </code></pre>
  </blockquote>
  
  <pre><code>  BEGIN  
    MyTID := '0000083';  
  </code></pre>
  
  <blockquote>
  <pre><code> MyRecordCountOfVarray:=v_owner_internal_id.COUNT;  
  </code></pre>
  </blockquote>
  
  <pre><code>  FOR d_owner_internal_id IN Cursor_rst1
    LOOP
          MyCursorRowCounter :=Cursor_rst1%ROWCOUNT;
  
          MyRowPositionInVarray := MyRecordCountOfVarray + MyCursorRowCounter;  
  </code></pre>
  
  <blockquote>
    <p></p>
  </blockquote>
  
  <pre><code>    v_owner_internal_id.extend;  
      --**************************************  
      --* First ERROR shows up on next line  
      --ORA-06550: line 87, column 54:  
      --PLS-00382: expression is of the wrong type  
      --ORA-06550: line 87, column 9:  
      --PL/SQL: Statement ignored  
      --**************************************          
      v_owner_internal_id(MyRowPositionInVarray) :=d_owner_internal_id;    
  </code></pre>
  
  <blockquote>
  <pre><code>        v_internal_id.extend;  
      --****************************************
      --* Second ERROR shows up on next line
      --ORA-06550: line 90, column 54:
      --PLS-00201: identifier ‘D_INTERANL_ID’ must be declared
      --ORA-06550: line 90, column 9:
      --PL/SQL: Statement ignored…
      --****************************************        
      v_internal_id(MyRowPositionInVarray)       :=d_internal_id;
  </code></pre>
  </blockquote>
  
  <pre><code>        v_tid.extend;  
          v_tid(MyRowPositionInVarray)               :=d_tid;
  
          v_entity_name.extend;  
          v_entity_name(MyRowPositionInVarray)       :=d_entity_name;
  
          v_form_seq.extend;
          v_form_seq(MyRowPositionInVarray)          :=d_FORM_SEQ;        
  
  
    END LOOP;  
  </code></pre>
  
  <blockquote>
  <pre><code>-- Review output  
    FOR i IN v_owner_internal_id.first .. v_owner_internal_id.last  
    LOOP dbms_output.put_line(v_owner_internal_id(i) || ',  ' ||  
                              v_internal_id(i) || ',  ' ||  
                              v_tid(i) || ',  ' ||  
                              v_entity_name(i) || ',  ' ||  
                              v_form_seq(i));  
    END LOOP;  
  </code></pre>
  </blockquote>
  
  <pre><code>END;
  </code></pre>