1 Reply Latest reply: Nov 7, 2012 6:08 AM by David Last-Oracle RSS

    Call PL/SQL function inside a sql statement problem

    972962
      Hello, this is my first post here :D

      I started working in Apex a few weeks ago and now I am struggling with a problem.


      I have a function: get_blob(query varchar2);

      So i need to call this function and give a query as a parameter in it ... this works just fine, but when i want to call an other function inside the query i don't get it working


      get_blob('
      SELECT
      i.naam AS zh,

      get_age_category(r.poging_dt,p.geboorte_dt) AS lft_cat


      FROM registraties r
      LEFT JOIN instanties i ON r.instantie_naam = i.instantie_id
      WHERE r.registratie_id = 12
      ');


      FUNCTION get_age_category(date1 IN DATE,date2 IN DATE) RETURN NUMBER AS
      BEGIN
      RETURN 5;
      END get_age_category;


      Now is my question, how can i make this work correct ? I get an error because the id of get_age_category is not correct it said ... and when i put quotes around it it's working but the output is just text and the function is not called.

      Anybody who can give me an explanation how i in this case can call the function get_age_category ? Thanks in advance