Oracle Database Dynamic SQL Select value with dynamic SQL

30% OFF - 9th Anniversary discount on Entity Framework Extensions until December 15 with code: ZZZANNIVERSARY9

Example

Let's say a user wants to select data from different tables. A table is specified by the user.

 function get_value(p_table_name varchar2, p_id number) return varchar2 is
    value varchar2(100);
  begin
    execute immediate 'select column_value from ' || p_table_name || 
                      ' where id = :P' into value using p_id;
    return value;
  end;

Call this function as usual:

declare
  table_name varchar2(30) := 'my_table';
  id number := 1;
begin
  dbms_output.put_line(get_value(table_name, id));
end;

Table to test:

create table my_table (id number, column_value varchar2(100));
insert into my_table values (1, 'Hello, world!');


Got any Oracle Database Question?