Not all dynamic sql is strictly for immediate execution, nor is it dirty (I needed aninteresting title). I learnt this tricks from my friend at work, he's a big believer of using sql to code generate more sql.
Altering column length
You'll still get an error when running the generated sql if you try to shrink a column.
SELECT 'ALTER TABLE ' || table_name || ' MODIFY ' || column_name || ' VARCHAR2(' || CASE column_name WHEN 'FOO' THEN '10' ELSE '20' END || ');' AS sql FROM user_tab_columns WHERE column_name IN ( 'FOO','BAR') AND table_name IN (SELECT table_name FROM user_tables) AND NOT regexp_like(table_name, '_(NEW|BAK)$') -- exclude backups Inserting output from dynamic sql
Normally you'd expect to get away with using something like SELECT blah INTO v_foo, but with dynamic sql you need place the INTO in the EXECUTE IMMEDIATE statement.
SET SERVEROUTPUT ON DECLARE v_sql VARCHAR2(8000); v_count NUMBER; CURSOR c_tables IS SELECT table_name FROM user_tables; BEGIN FOR rec in c_tables LOOP v_sql := 'SELECT count(*) FROM ' || rec.table_name; EXECUTE IMMEDIATE v_sql INTO v_count; DBMS_OUTPUT.put_line(rec.table_name || ' has ' || v_count); END LOOP; END; / Using substitution variables
clear screen set serveroutput on declare cursor c_tables is select table_name from user_tables; v_data VARCHAR2(2000); v_sql VARCHAR2(2000); v_stuff VARCHAR2(50) := '&1'; -- try entering MAX(DATADATE) or COUNT(*) begin for rec in c_tables loop v_sql := 'SELECT TO_CHAR(' || v_stuff || ') FROM ' || rec.table_name; begin execute immediate v_sql into v_data; dbms_output.put_line(rec.table_name || ': ' || v_data); exception when others then dbms_output.put_line('failed to run: ' || v_sql); raise; end; end case; end loop; end; / One liners
Grants
SELECT 'GRANT ' || privilege || ' ON "' || GRANTOR ||'"."' || TABLE_NAME || '" TO "' || GRANTEE || '";' FROM dba_tab_privs WHERE grantor = 'FOO' AND grantee = 'BAR' ; Recreating synonyms as sys
select 'CREATE OR REPLACE SYNONYM "' || OWNER || '"."' || SYNONYM_NAME || '" FOR "' || TABLE_OWNER || '"."' || TABLE_NAME || '";' from dba_synonyms where table_owner = 'FOO'; 
Top comments (0)