title: SQL Retrieving an object from VARRAY in Oracle 11g Database
body: <p>I have a schema RTRD_W  built in an Oracle 11g database that contains a table rtrd_pri that has a column NOMNL which is has a type defined as <strong>VARRAY(10) OF KPS_ADM.NUMBER_T</strong> (an object defined as a type in the KPS_ADM schema).  I am trying to run a query to <strong>retrieve the KPS_ADM.NUMBER_T object</strong> inside the varray but I keep getting an error returned in my SQL syntax.  The function I built is posted below</p>
  
  <pre><code>CREATE or replace function RETRIEVEPRIREF RETURN KPS_ADM.NUMBER_T AS
  REF1 KPS_ADM.NUMBER_T;
  BEGIN
  SELECT KPS_ADM.NUMBER_T INTO REF1 from table(NOMNL) WHERE (SELECT NOMNL FROM RTRD_W.rtrd_pri WHERE (syst_id like '%0516%'));
  RETURN REF1;
  END RETRIEVEPRIREF;
  </code></pre>
  
  <p>I know the query :<code>SELECT NOMNL FROM RTRD_W.rtrd_pri WHERE (syst_id like '%0516%')</code> works and does return a varray with a single KPS_ADM.NUMBER_T object inside it, but I can't seem to get the syntax right for searching inside the varray to retrieve the object.  </p>
  
  <p>Can anyone show the proper syntax to do this?</p>