Hi folks,
In this article I talk about how to invoke the next value an Oracle sequence specific number of times. Sequences are used to generate values for columns to produce auto-increment functionality.
We use the nextval to fetch the next value of a sequence.
To get the next value a number of times, say 10 times, run the following query
select level, mysequence.nextval from dual connect by level<=10;
That’s all for now. Till next time, happy software development.
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.
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