Interesting articles and Dynamic SQL

I was websurfing and came across Steve Yegge website/blog from reddit.com.
I spent time last night and time day digesting the articles on the site. I would recommend you go and have a read of some of his articles. He has also put his top 7 most popular by traffic at the top.

I have played around a bit with various languages since starting with computers. Given my area is more technical and support than development or software engineering, I haven't had the need to learn any language more than being able to understand what the gist of the code and the overall logic.

The thing which makes me want to spend more time with Lisp and its macros as they remind me of dynamic SQL. Lisp macros are more powerful and expressive but the ability to generate code from code is a similar concept.

Good security suggests that any object permissions should be granted to roles and those roles granted to individual users/logins. Here is a good example of dynamic SQL in action.

-- Create the role read_only
-- Prompts for a schema name (owner)
-- Generates GRANT SELECT ON owner.table_name TO read_only

create role read_only;

set echo off
set pagesize 0
set verify off
spool /tmp/read_only_grants.sql
select 'GRANT SELECT ON '||owner||'.'||object_name||' TO read_only;'
from dba_objects
where owner = '&owner'
and object_name not like '%$%'
and object_type in ('TABLE','VIEW')
order by object_name;
spool off
@/tmp/read_only_grants.sql

You can extend this even further using DECODE or CASE to handle granting execute permissions on packages,procedures and functions.

select 'GRANT '||
CASE
when object_type in ('TABLE','VIEW') then 'SELECT'
when object_type in ('PACKAGE','PROCEDURE','FUNCTION') then 'EXECUTE'
else 'SELECT' end
||' ON '||owner||'.'||object_name||' TO read_only;'
from dba_objects
where owner = '&owner'
and object_name not like '%$%'
and object_type in ('TABLE','VIEW','PACKAGE','PROCEDURE','FUNCTION')
order by object_name;

I use dynamic SQL all the time, it saves time and allows the quick generation of repeating SQL.
It is like mail merge in Word or like Lisp macros.

Have Fun

Paul