Oracle Dynamic SQL
Select value with dynamic SQL
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!');