Tuesday, February 25, 2014

How to Bind Variable using Query String

CREATE OR REPLACE FUNCTION XX_GET_VALUE_F (p_person_id      NUMBER,
                                           p_column_name    VARCHAR2)
   RETURN VARCHAR2
IS
   l_column_value   VARCHAR2 (2000) := NULL;
   l_query_string   VARCHAR2 (2000) := NULL;
BEGIN
   l_query_string :=
         'declare
   l_column_value varchar2(2000);
   begin
                select '
      || p_column_name
      || ' into :l_column_val from per_people_x where person_id =  '
      || p_person_id
      || ';'
      || ' end;';

   EXECUTE IMMEDIATE l_query_string USING OUT l_column_value;
   RETURN l_column_value;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;



select XX_GET_VALUE_F(70811,'LAST_NAME') from dual

No comments:

Post a Comment