Re: plsql

Ken Chesak wrote:

> Following SQL produces these results.
>
> select table_name,grantee,PRIVILEGE
> from USER_tab_privs a
> where a.table_name = 'BEER_CLIENT'
> order by table_name
>
> TABLE_NAME GRANTEE PRIVILEGE
> ------------------------------ ------------------------------ ------------------
> ----------------------
> BEER_CLIENT IWS_WEB_DEV INSERT
> BEER_CLIENT IWS_WEB_DEV SELECT
> BEER_CLIENT IWS_WEB_DEV UPDATE
> BEER_CLIENT IWS_WEB_DEV DELETE
>
> I would like SQL which produces one row as follows,
>
> BEER_CLIENT IWS_WEB_DEV INSERT SELECT UPDATE DELETE
>
> Oracle 8.1.7 thanks

Then you will need to use DECODE with the following

DECODE(privilege, 'SELECT', privilege) SEL
DECODE(privilege, 'INSERT', privilege) INS
DECODE(privilege, 'UPDATE', privilege) UPD
DECODE(privilege, 'DELETE', privilege) DEL

and then concatenate it all together again.

Daniel Morgan Received on Tue Jun 18 2002 - 15:46:58 CDT