5 Replies Latest reply on Nov 29, 2019 3:58 PM by Sven W.

    How can I query several records in the same field of the same table? Oracle / ¿Cómo puedo consultar varios registros en un mismo campo de una misma tabla? Oracle

    John Cristian Cald

      Good day to all the Oracle developers,

           Buen día para todos los oracle developers,

       

      The matter is this, I hope to explain myself well.

           El asunto es el siguiente, espero explicarme bien.

       

      I have the following query in Oracle:

           Tengo el siguiente query en Oracle:

       

      SELECT GRANTEE

        FROM dba_tab_privs

      WHERE (    TABLE_NAME = 'FSD011'

              AND TABLE_NAME = 'FSD015'

              AND TABLE_NAME = 'FSD016'

              AND TABLE_NAME = 'FSD014'

              AND TABLE_NAME = 'FSD013');

       

      What I am trying to do is extract only the GRANTEE that has only all the tables that are indicated after the WHERE condition for the TABLE_NAME field. The output should be:

           Lo que estoy intentando es extraer sólo el GRANTEE que tenga únicamente todas las tablas que se indican después de la condición WHERE para el campo TABLE_NAME. La salida debería ser:

       

      GRANTEETABLE_NAME
      RABD_RAUMARTINEFSD011
      RABD_RAUMARTINEFSD015
      RABD_RAUMARTINEFSD016
      RABD_RAUMARTINEFSD014
      RABD_RAUMARTINEFSD013

       

      Unlike something like:

           A diferencia de si se usara algo como:

       

      SELECT GRANTEE

        FROM dba_tab_privs

      WHERE TABLE_NAME IN ('FSD011',

                            'FSD015',

                            'FSD016',

                            'FSD014',

                            'FSD013');

       

      Where it would be to extract all "GRANTEE" (other than "RABD_RAUMARTINE") that contain any of the indicated tables. (That is not what I want!)

           Donde lo que haría es extraer todos los GRANTEE (distintos a RABD_RAUMARTINE) que contengan cualquiera de las tablas indicadas pero no únicamente las 5.

       

      For example:

           Como por ejemplo:

       

       

      GRANTEETABLE_NAME
      RABD_RAUMARTINEFSD011
      RABD_RAUMARTINEFSD013
      RABD_RAUMARTINEFSD014
      RABD_RAUMARTINEFSD015
      RABD_RAUMARTINEFSD016
      RABD_IMPUESTOSFSD011
      RABD_IMPUESTOSFSD015
      RABD_IMPUESTOSFSD016

       

       

      How do i do it?

           De qué manera se podría conseguir esto?

       

      Thank you very much in advance.

           Muchas gracias de antemano.

       

      .

        • 1. Re: ¿Cómo puedo consultar varios registros en un mismo campo de una misma tabla? Oracle
          Glen Conway

          Su pregunta no es específica del SQL Developer IDE, que es el foco de este foro. Otros foros como SQL & PL/SQL son mejores lugares para preguntar.

           

          Sin embargo, parece que la primera instrucción SQL debe usar 'OR' en lugar de 'AND' para lograr el mismo resultado que la segunda instrucción SQL con una 'IN-list'.

           

          Cheers

          • 2. Re: ¿Cómo puedo consultar varios registros en un mismo campo de una misma tabla? Oracle
            GregV

            Hola,

             

            Necesitas escribir en inglés.

             

            If you want to make sure all your 5 tables are granted, you can write something like this:

             

            SELECT GRANTEE

            FROM dba_tab_privs

            WHERE TABLE_NAME IN ('FSD011',

                                 'FSD015',

                                 'FSD016',

                                 'FSD014',

                                 'FSD013')

            GROUP BY GRANTEE

            HAVING COUNT(*) = 5;

            • 3. Re: ¿Cómo puedo consultar varios registros en un mismo campo de una misma tabla? Oracle
              L. Fernigrini

              Podría ser algo asi / Could be something like this:

               

              [Edit] Just realized that GregV posted the same answer a few minutes ago

               

              SELECT GRANTEE

                FROM dba_tab_privs

              WHERE TABLE_NAME IN ('FSD011','FSD015','FSD016','FSD014','FSD013')

              GROUP BY GRANTEE

              HAVING COUNT(*) = 5;

               

               

              • 4. Re: ¿Cómo puedo consultar varios registros en un mismo campo de una misma tabla? Oracle
                mathguy

                Sorry, but on this site you will need to use English. (I believe there are sections on this same site for other languages; do your own research if needed).

                 

                Back to your problem.  WHICH privilege(s) are you looking for?

                 

                Even with the solutions proposed so far, you will pick up grantees who have grants to the five tables - but the privileges granted to them may all be different. Don't you need a more precise problem statement - such as, you need to find the grantees that have SELECT privilege (or whatever other privilege or privileges) on all five tables?

                • 5. Re: How can I query several records in the same field of the same table? Oracle / ¿Cómo puedo consultar varios registros en un mismo campo de una misma tabla? Oracle
                  Sven W.

                  Be careful with DBA_TAB_PRIVS. If one of the table exist in multiple schemas, then you will find several rows for this table in dba_tab_privs.
                  You probably want to add the schema name (OWNER) to the query. Just to be on the safe side.

                   

                  I would do a select like the following. If you comment in the having clause, it will only show the users that have all the tables granted.
                  If you run it without the having clause it should give a decent overview

                   

                  select table_owner,
                         count(*) table_count, 
                         count(granted_table) grant_count, 
                         listagg(granted_table,',') within group (order by granted_table) tables_granted
                  from ( select t.owner table_owner, t.table_name, p.grantee, p.table_name as granted_table
                          from dba_tables t
                          left join dba_tab_privs p on p.owner = t.owner and t.table_name = p.table_name
                          where t.table_name in ('FSD011',
                                        'FSD015',
                                        'FSD016',
                                        'FSD014',
                                        'FSD013')
                          group by t.owner, t.table_name, p.grantee, p.table_name
                        ) 
                  group by table_owner, grantee
                  --having count(*) = count(granted_table)
                  ;
                  

                   

                   

                  Compared to the previous suggestions, this query also avoids having to hardcode the number of tables to check. So if you put 20 tables in the list it will work too.

                   

                  -- EDIT: After reading Mathguys reply, I added a version that eliminates duplicates because of multiple privileges. However you should be aware that this could list users that have a select on one table and a DELETE on the other table.