HOME BLOG

Archive for the ‘Oracle’ Category

How to view what tables a user has privileges on in Oracle

Posted on: November 10th, 2022 by Olu No Comments

Hi folks,

In this post I talk about handy commands you can run to find out what tables a user has privileges on in an Oracle database.

To view privileges for all users, run the following query:

select * from dba_tab_privs;

 

To view privileges for the currently logged-in user, run the following query:

select * from role_tab_privs;

 

The result will show the role, owner, table name, column name, privilege, grantable and common fields for each privilege.

The above command could be very useful when debugging permissions issues.

That’s all for now. Till next time, happy software development.

How to create table literals in Oracle

Posted on: October 9th, 2020 by Olu No Comments

Hi folks,

In this post I talk about how to create literal tables in Oracle.

You may have a list of values and want to run an Oracle query against those values.

A convenient way to do this is to create a literal table in an Oracle SQL using those values.

 

Single-column

If you want to create a single-column literal table,

you can use either dbms_debug_vc2coll or sys.ODCIVarchar2List

e.g.

select * from table (sys.ODCIVarchar2List('AAA', 'BBB', 'CCC'));

This will return an inline table with a single column named COLUMN_VALUE and rows with values AAA, BBB and CCC.

i.e.

COLUMN_VALUE
==
AAA
BBB
CCC

Second example

select column_value from table(sys.dbms_debug_vc2coll(‘AAA’, ‘BBB’, ‘CCC’))

 

Multiple colums

If you want to generate a literal table with two columns, you can use a query like:

select objectschema m, objectname n
from
table(sys.ODCIObjectList(
sys.odciobject('APPLE', 'FRUIT'),
sys.odciobject('CARROT', 'VEGGIE')
));

For more tahan 2 columns, you can use a query like:

create type t as object (a varchar2(10), b varchar2(10), c number);
create type tt as table of t;

select * from table( tt (
    t('APPLE', 'FRUIT', 1),
    t('APPLE', 'FRUIT', 1122), 
    t('CARROT', 'VEGGIE', 3),
    t('PEACH', 'FRUIT', 104),
    t('CUCUMBER', 'VEGGIE', 5),
    t('ORANGE', 'FRUIT', 6) ) )

That’s all for now. Happy database querying.

 

Sources

Oracle SQL Tip: Using dbms_debug_vc2coll and sys.ODCIVarchar2List. https://training.fusionapplied.com/2017/08/23/oracle-sql-tip-using-dbms_debug_vc2coll-and-sys-odcivarchar2list/

Is there a non-ugly way to use a multi-column, multi-row table literal in an Oracle 11g query? https://stackoverflow.com/questions/29903397/is-there-a-non-ugly-way-to-use-a-multi-column-multi-row-table-literal-in-an-ora/29904128