Re: plsql

Christopher Merry wrote:

> Perhaps I am missing something, but I don't see how this solves the problem.
> Your DECODE simply returns the same value you are comparing against.
> Regardless of the outcome of the DECODE, it still produces each privilege in
> its own row. Short of writing a function called from within SQL, I can't
> think a way of accomplishing this. I'm not saying it can't be done without
> the function I just can't think of a way. If the DECODE works, please let
> me know.
>
> Here is one possible solution using a function:
>
> CREATE OR REPLACE FUNCTION priv_inline
> (p_table_name VARCHAR2
> ,p_grantee VARCHAR2)
> RETURN VARCHAR2 IS
> CURSOR privs_cur IS
> SELECT privilege
> FROM user_tab_privs
> WHERE table_name = p_table_name
> AND grantee = p_grantee;
>
> v_return_str VARCHAR2(255);
> BEGIN
> FOR privs_rec IN privs_cur LOOP
> IF privs_cur%ROWCOUNT > 1 THEN
> v_return_str := v_return_str || ', ';
> END IF;
> v_return_str := v_return_str || privs_rec.privilege;
> END LOOP;
>
> RETURN v_return_str;
> END priv_inline;
> /
>
> SELECT x.table_name, x.grantee, priv_inline(x.table_name, x.grantee) privs
> FROM (SELECT DISTINCT table_name, grantee FROM user_tab_privs) x
> WHERE x.table_name = 'BEER_CLIENT';
>
> I know this is not the most efficient solution but it works. Like anything
> else in Oracle, I'm sure there are a hundred different ways to do this.
>
> Here are the results of the above function:
>
> SQL> COLUMN PRIVS FORMAT A50
>
> SQL> SELECT x.table_name, x.grantee, priv_inline(x.table_name, x.grantee)
> privs
> 2 FROM (SELECT DISTINCT table_name, grantee FROM user_tab_privs) x
> 3 WHERE x.table_name = 'TEST_TBL';
>
> TABLE_NAME GRANTEE PRIVS
> ---------- ---------- --------------------------------------------------
> TEST_TBL TEST DELETE, INSERT, SELECT, UPDATE
>
> 1 row selected.
>
> Notice I used a subquery in the FROM clause to ensure no duplicates. Play
> around with this, it may be what you're looking for.
>
> cm
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3D0F9C32.271688C6_at_exesolutions.com...
> > 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
> >

You are missing something. You are missing the fact that it puts them into separate fields ... it is how a crosstab is done.

You can then sum over the result anyway you wish.

Daniel Morgan Received on Fri Jun 21 2002 - 11:47:44 CDT