Dynamic SQL with PL/SQL example
Sample dynamic SQL using PL/SQL. I had some problems constructing dynamic SQL but I finnaly got it working. In case somebody has a use of it I put it here. The code finds all NUMBER columns from the current user tables.
Things worth a notice:
- when using into keyword in the dynamic SQL we have to use begin and end keywords since into is PL/SQL, not SQL
- EXECUTE IMMEDIATE can use IN and OUT parameters
The code:
DECLARE
CURSOR all_my_tables IS
SELECT TABLE_NAME,
column_name,
data_precision
FROM user_tab_columns
WHERE data_type LIKE 'NUMBER%';
max_col_val NUMBER(20);
sql_stmt VARCHAR(200);
BEGIN
FOR t IN all_my_tables
LOOP
sql_stmt := 'begin select max(' || t.column_name || ') into :a from '
|| t.TABLE_NAME || '; end;';
EXECUTE IMMEDIATE sql_stmt USING OUT max_col_val;
DBMS_OUTPUT.PUT_LINE('Table: ' || t.TABLE_NAME || ' Column: '
|| t.column_name || ' type: NUMBER(' || t.data_precision || ') max: '
|| max_col_val);
END LOOP;
END;