Re: plsql

Charlie Edwards wrote:

> <snip>
>
> Blimey Christopher, that's all a bit long-winded. Why use all that
> PL/SQL, when a bit of SQL will suffice?
>
> As Daniel suggested, you can do it with decodes, but include a GROUP
> BY to get down to a single row per table:
>
> SELECT table_name,
> grantee,
> rtrim(max(decode(PRIVILEGE,'INSERT','INSERT, '))||
> max(decode(PRIVILEGE,'SELECT','SELECT, '))||
> max(decode(PRIVILEGE,'UPDATE','UPDATE, '))||
> max(decode(PRIVILEGE,'DELETE','DELETE, ')),', ') privs
> FROM user_tab_privs
> GROUP BY table_name,grantee;
>
> Regards
>
> CE

Nicely done, Charlie. I'll admit that I was a bit perplexed by Daniel's initial answer, but it's clear to me now. I like exchanges like this because they help to get one thinking about the various ways of doing things in SQL, and that can be invaluable. I used to turn almost instinctively towards stored procedures and functions to get rather basic operations done, but no longer.

If anyone's interested in pursuing the power of DML and SQL queries, check out the various books by Joe Celko. My favorite is "SQL Puzzles & Answers."

Bye,
TG Received on Fri Jun 21 2002 - 16:17:16 CDT