PL / SQL macro, as in C programming

Question

So, I studied programming in C macros and used them, but in my work I often use PL / SQL, and I was wondering if there is a way to do the same in PL / SQL. Right now I have a function call with three different by values ​​and then returning the value, but the function is so simple that I think I can do it from within the original stored procedure. In C a, Macro is a line (or lines) of code that is simply completely replaced by a compilation call, but it is more efficient than calling a function many times.

Example from C:

#define query(fieldValue, Attribute, Table) (select fieldValue from Table where record = Attribute)

and when called in the body of the code, the request (value, value, value) will completely replace the select statement.

just an example of how it might appear in C, because I'm really not sure how it will be in PL / SQL.

Is this possible in SQL? It should be for 2-3 lines of code, here it is.

Thanks a lot, SMKS

Answer 1

Just following OldProgrammer's comments, I think you will need one function that uses dynamic SQL to return individual values ​​from very similar SQL statements.

The following is an example of how this can be achieved:

declare 

  function get_field_val(
      p_field          varchar2,
      p_table          varchar2,
      p_where_clause   varchar2
  )   return varchar2 is
      v_query     clob;
      v_result    varchar2(4000);
  begin

      v_query := 'select to_char(' || p_field ||')' ||
                 'from ' || p_table || ' ' || p_where_clause;                 

      execute immediate v_query into v_result;

      return v_result;

  end;

begin

  dbms_output.put_line(
      get_field_val(
          p_field        => 'COLUMN_NAME',
          p_table        => 'ALL_TAB_COLUMNS',
          p_where_clause => 'where owner = ''SYS'' and table_name = ''ACCESS$''
              and column_id = 1'));

  dbms_output.put_line(
      get_field_val(
          p_field        => 'max(table_name)',
          p_table        => 'all_tables',
          p_where_clause => 'where owner = ''SYS'''));

end;

A few notes about this:

• This function can return only one varchar value. If you need different types or arrays of values, you will need to approach this using the built-in or custom plsql collections.
• , , , ( AUTHID CURRENT_USER)