title: Record type reflection / populating a record variable of an unknown type (PL/SQL)
body: <p>I have a package with a record type and a variable of that type in it:  </p>
  
  <pre><code>CREATE PACKAGE my_package AS
    TYPE t_rec IS RECORD(
      id           NUMBER,
      name         VARCHAR2(100),
      last_updated DATE
    );
    v_var t_rec;
  END;
  </code></pre>
  
  <p>I want to create an universal procedure for populating any record variable, so it can be called like this:  </p>
  
  <pre><code>BEGIN
    populate_record('my_package.v_var', 'MY_TABLE', CHARTOROWID(:VAR1));
  END;
  </code></pre>
  
  <p>The logic of that procedure may look like this:  </p>
  
  <pre><code>PROCEDURE populate_record(
    p_var_name   IN VARCHAR2,
    p_table_name IN VARCHAR2,
    p_rowid      IN ROWID
  ) IS
  BEGIN
    -- determining a type of a given record variable
    -- determining names and data types of given record variable's columns
    -- selecting a row from a given table by a given rowid
    -- filling out a given record variable by mapping table columns
    --   to record columns by their names (using dynamic PL/SQL)
  END;
  </code></pre>
  
  <p>But I have no idea how to obtain information about a given record. Are there any data dictionary views or built-in functions for querying record type columns?<br>
  Thanks.</p>
  
  <p>P.S.: 11.2</p>
  
  <p>UPDATED</p>
  
  <p>I found a way to obtain info about records using PL/Scope:</p>
  
  <pre><code>CREATE OR REPLACE VIEW user_record_types AS
  SELECT
    i.object_name package_name,
    i.name type_name
  FROM
    user_identifiers i
  WHERE
    i.object_type = 'PACKAGE'
    AND i.type = 'RECORD'
    AND i.usage = 'DECLARATION'
  /
  
  CREATE OR REPLACE VIEW user_record_types_columns AS
  SELECT
    i1.object_name package_name,
    i1.name type_name,
    ROW_NUMBER() OVER (
      PARTITION BY
        i1.object_name,
        i1.name
      ORDER BY
        i2.usage_id
    ) column_num,
    i2.name column_name,
    i3.name column_type,
    i3.type column_type_class
  FROM
    user_identifiers i1,
    user_identifiers i2,
    user_identifiers i3
  WHERE
    i1.object_type = 'PACKAGE'
    AND i1.type = 'RECORD'
    AND i1.usage = 'DECLARATION'
    AND i2.object_name = i1.object_name
    AND i2.object_type = i1.object_type
    AND i2.type = 'VARIABLE'
    AND i2.usage = 'DECLARATION'
    AND i2.usage_context_id = i1.usage_id
    AND i3.object_name = i2.object_name
    AND i3.object_type = i2.object_type
    AND i3.usage = 'REFERENCE'
    AND i3.usage_context_id = i2.usage_id
  /
  
  CREATE OR REPLACE VIEW user_record_variables AS
  SELECT
    i1.object_name package_name,
    i1.name variable_name,
    CASE i2.type
      WHEN 'RECORD' THEN
        i2.object_name
      WHEN 'PACKAGE' THEN
        i2.name
    END type_package_name,
    CASE i2.type
      WHEN 'RECORD' THEN
        i2.name
      WHEN 'PACKAGE' THEN
        i3.name
    END type_name
  FROM
    user_identifiers i1,
    user_identifiers i2,
    user_identifiers i3
  WHERE
    i1.object_type = 'PACKAGE'
    AND i1.type = 'VARIABLE'
    AND i1.usage = 'DECLARATION'
    AND i2.object_name = i1.object_name
    AND i2.object_type = i1.object_type
    AND i2.type IN ('RECORD', 'PACKAGE')
    AND i2.usage = 'REFERENCE'
    AND i2.usage_context_id = i1.usage_id
    AND i3.object_name (+) = i2.object_name
    AND i3.object_type (+) = i2.object_type
    AND i3.type (+) = 'RECORD'
    AND i3.usage (+) = 'REFERENCE'
    AND i3.usage_context_id (+) = i2.usage_id
  /
  </code></pre>
  
  <p>But there is a problem when I use %ROWTYPEs, because there is no info about "what of" that %ROWTYPE is. So I think that PL/Scope is not a complete solution...</p>