Re: plsql

From: Christopher Merry <merryct_at_constructingbits.com>
Date: Thu, 20 Jun 2002 22:09:10 -0700
Message-ID: <uh5d7ig9jtjaaa@corp.supernews.com>

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
>
Received on Fri Jun 21 2002 - 00:09:10 CDT