title: Select value from table where the name is stored in another table
body: <p>I have below view which uses <code>get_rationalized_ip_adr</code> function to retrieve some value given as input the nodeid from NODE table.</p>
  
  <pre><code>    CREATE OR REPLACE VIEW A_DEVICE AS
      SELECT  NODE.NAME AS DEVICE
            , NODETYPE.TABLENAME AS TABLENAME
            , ext_a_device.get_rationalized_ip_adr(NODE.NODEID) AS IPADDRESS
            , COALESCE(LOC_SITE.OS_EASTING, LOC_RAILWAY.OS_EASTING, LOC_SIGNALBOX.EASTING) AS OS_EASTING
            , COALESCE(LOC_SITE.OS_NORTHING, LOC_RAILWAY.OS_NORTHING, LOC_SIGNALBOX.NORTHING) AS OS_NORTHING
      FROM NODE NODE
      JOIN NODETYPE NODETYPE
      ON NODE.NODE2NODETYPE = NODETYPE.NODETYPEID;
  </code></pre>
  
  <p>I had to add the function in the select stmt in order to get ipaddresses that are present in some other tables, tables which have the name stored in <code>NODETYPE</code> table.</p>
  
  <p>For example this is a screen shot form nodetype table. So the <code>get_rationalized_ip_adr</code> calls an API that does the fetching from <code>NR_NODE_DDF</code> for each nodeid passed to it.</p>
  
  <p><a href="https://i.stack.imgur.com/6KBbS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6KBbS.png" alt="enter image description here"></a></p>
  
  <p><code>get_rationalized_ip_adr</code> function just calls an API with the nodeid input and returns the ip_adr for the respective node.</p>
  
  <pre><code>FUNCTION get_rationalized_ip_adr( pin_node_id IN NUMBER)
  
  RETURN VARCHAR2 IS
  ln_errorcode NUMBER;
  ls_errortext VARCHAR2(200);
  ls_result    VARCHAR2(200);
  BEGIN
      pkggeneral.getobjectattribute( o_errorcode =&gt; ln_errorcode
                                          , o_errortext =&gt; ls_errortext
                                          , i_dimobject =&gt; pkgdimensionconstants.dimensionObject_Node
                                          , i_objectid  =&gt; pin_node_id
                                          , i_attribute =&gt; 'IP_ADDRESS'
                                          , o_result    =&gt; ls_result);
      RETURN ls_result;
  END get_rationalized_ip_adr;
  </code></pre>
  
  <p>Now the problem for example is that this select statement is taking around 90 seconds, which is too much. </p>
  
  <pre><code>SELECT * FROM A_DEVICE ad where ad.ipaddress = '10.16.52.152'
  </code></pre>
  
  <p>As I understood switching between sql and pl-sql context is the problem of this time to run.</p>
  
  <p>Can anyone think of another solution to decrease this time??</p>