loading...

Dirty Dynamic SQL

booyaa profile image Mark Sta Ana Originally published at booyaa.wtf on ・2 min read

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';

Posted on by:

booyaa profile

Mark Sta Ana

@booyaa

He/Him/Dat Boi. SRE for an Agribiz SaaS Startup. Terrorizing Azure with Terraform. Rusticating Windows.

Discussion

pic
Editor guide