Oracle 12c has introduced the useful SQL standard
IDENTITY
feature, which is essentially just syntax sugar for binding a sequence to a column default. We can use it like this:
create table t1 (col1 number generated always as identity);
create table t2 (col2 number generated always as identity);
insert into t1 values (default);
insert into t1 values (default);
insert into t1 values (default);
insert into t2 values (default);
select * from t1;
select * from t2;
Which produces
COL1
----
1
2
3
COL2
----
1
For unit testing against our database, we might want to know what “state” our identities are in. For each table, we would like to know the next value such an identity would produce. If we knew all the backing sequence names, we could query their
seq.currval
, but we don’t know those sequence names as they are generated.
However, we can query the dictionary views to get this information as follows:
select data_default
from user_tab_cols
where data_default is not null
and identity_column = 'YES'
and table_name in ('T1', 'T2');
An alternative is to query
user_tab_identity_cols
This would produce:
"TEST"."ISEQ$$_116601".nextval
"TEST"."ISEQ$$_116603".nextval
Now, if we’re lazy, we could just run
EXECUTE IMMEDIATE
on each of those expressions and we’re done:
set serveroutput on
declare
v_current number;
begin
for rec in (
select table_name, data_default
from user_tab_cols
where data_default is not null
and identity_column = 'YES'
and table_name in ('T1', 'T2')
) loop
execute immediate replace(
'select ' || rec.data_default || ' from dual',
'.nextval',
'.currval'
) into v_current;
dbms_output.put_line(
'Table : ' || rec.table_name ||
', currval : ' || v_current
);
end loop;
end;
/
This would produce:
Table : T1, currval : 3
Table : T2, currval : 1
Alternatively, if you want this result to be a SQL result instead of
DBMS_OUTPUT
content, you could run this:
with
function current_value(p_table_name varchar2) return number is
v_current number;
begin
for rec in (
select data_default
from user_tab_cols
where table_name = p_table_name
and data_default is not null
and identity_column = 'YES'
)
loop
execute immediate replace(
'select ' || rec.data_default || ' from dual',
'.nextval',
'.currval'
) into v_current;
return v_current;
end loop;
return null;
end;
select *
from (
select table_name, current_value(table_name) current_value
from user_tables
where table_name in ('T1', 'T2')
)
where current_value is not null
order by table_name;
/
The alternative using
user_tab_identity_cols
would look like this:
with
function current_value(p_table_name varchar2) return number is
v_current number;
begin
for rec in (
select sequence_name
from user_tab_identity_cols
where table_name = p_table_name
)
loop
execute immediate
'select ' || rec.sequence_name || '.currval from dual'
into v_current;
return v_current;
end loop;
return null;
end;
select *
from (
select table_name, current_value(table_name) current_value
from user_tables
)
where current_value is not null
order by table_name;
/
The result is now a nice SQL result set:
TABLE_NAME CURRENT_VALUE
--------------------------
T1 3
T2 1
Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder
result for me is no so nice:
SQL> create table t1 (col1 number generated always as identity);
Table created.
SQL> create table t2 (col2 number generated always as identity);
Table created.
SQL> with
2 function current_value(p_table_name varchar2) return number is
3 v_current number;
4 begin
5 for rec in (
6 select data_default
7 from user_tab_cols
8 where table_name = p_table_name
9 and data_default is not null
10 and identity_column = 'YES'
11 )
12 loop
13 execute immediate replace(
14 'select ' || rec.data_default || ' from dual',
15 '.nextval',
16 '.currval'
17 ) into v_current;
18 return v_current;
19 end loop;
20
21 return null;
22 end;
23 select *
24 from (
25 select table_name, current_value(table_name) current_value
26 from user_tables
27 where table_name in ('T1', 'T2')
28 )
29 where current_value is not null
30 order by table_name;
31 /
order by table_name
*
ERROR at line 30:
ORA-08002: sequence ISEQ$$_1954315.CURRVAL is not yet defined in this session
ORA-06512: at line 22
Yeah, I know. Run once with NEXTVAL and then re-run with CURRVAL as a workaround…
Not related, but did you ever considered making a JOOQ for javascript?
Having the same quality as JOOQ has, it can easily dominate the SQL market in javascript.
I can be wrong, but JOOQ looks to me far more complete than solutions like sequelize.
But I guess the lack of a JDBC like API in javascript would make this quite a challenge…
Thank you very much for your comment. Yes, of course, we’re constantly thinking about how to bring jOOQ to other languages, but that’s a lot of effort and needs a lot of preparation. Right now, we’re looking into how we can improve the Java version in a way that we can reuse our API as much as possible in other languages (e.g. by generating the API and a big chunk of the implementation from a meta language).
JavaScript (or rather: TypeScript) will be a very interesting candidate. But I think it’ll still take until 2020/2021 until we can ship something.