OraFAQ Forum: SQL & PL/SQL » Getting same sequence number in select query

Home » SQL & PL/SQL » SQL & PL/SQL » Getting same sequence number in select query (Oracle 19c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Getting same sequence number in select query [message #689660]

Tue, 12 March 2024 04:43

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Hi All,

I want to get same sequence number in both columns of select query, please help me to get this.

create sequence test_seq;
 
create or replace FUNCTION get_seq_val RETURN VARCHAR2 IS
    L_SEQ_VAL VARCHAR(100);
  BEGIN
      L_SEQ_VAL :=  test_seq.nextval ;
    RETURN L_SEQ_VAL;
  END get_seq_val;
/
 
 
with temp as( select 
       get_seq_val  from dual)
       select seqval,seqval from temp; (e.g - 1,1)

Thank you

Report message to a moderator

Re: Getting same sequence number in select query [message #689661 is a reply to message #689660]

Tue, 12 March 2024 05:13

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> with temp as( select
  2         get_seq_val seqval  from dual)
  3         select seqval,seqval from temp;
SEQVAL
---------------------------------------------------
SEQVAL
---------------------------------------------------
1
2

1 row selected.

SQL>
SQL> with temp as( select /*+ materialize */
  2         get_seq_val seqval  from dual)
  3         select seqval,seqval from temp;
SEQVAL
---------------------------------------------------
SEQVAL
---------------------------------------------------
3
3

1 row selected.

Report message to a moderator

Re: Getting same sequence number in select query [message #689662 is a reply to message #689660]

Tue, 12 March 2024 05:21

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You can use the materialize hint:

with temp as( select /*+ materialize */
       get_seq_val as seqval from dual)
       select seqval,seqval from temp;

Report message to a moderator

Re: Getting same sequence number in select query [message #689663 is a reply to message #689661]

Tue, 12 March 2024 05:21

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Thank you very much for your help Michel and Cookiemonster. Without this forum I would not have survived in this job for so long.

[Updated on: Tue, 12 March 2024 05:22]

Report message to a moderator

Re: Getting same sequence number in select query [message #689664 is a reply to message #689663]

Tue, 12 March 2024 09:35

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Note that Oracle automatically does it if you directly call the sequence in the query instead of in a function:

SQL> select test_seq.nextval , test_seq.nextval from dual;
NEXTVAL NEXTVAL
------- -------
      4       4

1 row selected.

Report message to a moderator

Re: Getting same sequence number in select query [message #689689 is a reply to message #689664]

Wed, 20 March 2024 09:04

mathguy
Messages: 107
Registered: January 2023

Senior Member

The problem is that Oracle merges the subquery (defined in the WITH clause) into the main query, and it does so carelessly. It's an optimizer behavior that should be fixed by Oracle, but it won't be, so you do need to use hints to work around it.

MATERIALIZE is not the best hint to use though. For one thing, it's undocumented. For another, it does too much. It not only corrects the behavior, it also causes Oracle to write the output of the subquery to disk (or other form of permanent storage); this we don't need. To prevent the optimizer from merging the subquery into the main query, you should use the documented NO_MERGE hint instead. This can be done in two ways - either include the hint in the SELECT clause of the subquery, or include it with a reference to the subquery name in the SELECT clause of the main query. Either one of the following two queries will behave as needed:

 
with temp as( select /*+ no_merge */
       get_seq_val as seqval  from dual)
       select seqval,seqval from temp; 

with temp as( select 
       get_seq_val as seqval  from dual)
       select /*+ no_merge(temp) */ seqval,seqval from temp;

In the second query it is necessary to reference the subquery in the hint - adding NO_MERGE (with no qualification) in the top-level SELECT will not suffice, since that has a different meaning.

Report message to a moderator

Re: Getting same sequence number in select query [message #689706 is a reply to message #689689]

Wed, 27 March 2024 12:02

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Issue is no_merge calls function twice:

drop sequence test_seq; 
create sequence test_seq;
create or replace
  function get_seq_val
     return varchar2
     is
         v_seq_val varchar2(100);
     begin
         v_seq_val :=  test_seq.nextval;
         dbms_output.put_line('get_seq_val:' || v_seq_val);
         return v_seq_val;
end get_seq_val;
/
set serveroutput on
column seqval format a6
SQL> with temp as( select /*+ no_merge */
  2         get_seq_val as seqval  from dual)
  3         select seqval,seqval from temp;

SEQVAL SEQVAL
------ ------
1      1

get_seq_val:1
get_seq_val:2
SQL> with temp as( select
  2         get_seq_val as seqval  from dual)
  3         select /*+ no_merge(temp) */ seqval,seqval from temp;

SEQVAL SEQVAL
------ ------
3      3

get_seq_val:3
get_seq_val:4
SQL> with temp as( select /*+ materialize */
  2         get_seq_val as seqval from dual)
  3         select seqval,seqval from temp;

SEQVAL SEQVAL
------ ------
5      5

get_seq_val:5
SQL>

SY.

[Updated on: Wed, 27 March 2024 12:03]

Report message to a moderator

Re: Getting same sequence number in select query [message #689707 is a reply to message #689706]

Wed, 27 March 2024 12:12

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

We would need to declare function deterministic even though it isn't which could cause unexpected results in other statements:

drop sequence test_seq; 
create sequence test_seq;
create or replace
  function get_seq_val
     return varchar2
     deterministic
     is
         v_seq_val varchar2(100);
     begin
         v_seq_val :=  test_seq.nextval;
         dbms_output.put_line('get_seq_val:' || v_seq_val);
         return v_seq_val;
end get_seq_val;
/
set serveroutput on
column seqval format a6
SQL> with temp as( select /*+ no_merge */
  2         get_seq_val as seqval  from dual)
  3         select seqval,seqval from temp;

SEQVAL SEQVAL
------ ------
1      1

get_seq_val:1
SQL> with temp as( select
  2         get_seq_val as seqval  from dual)
  3         select /*+ no_merge(temp) */ seqval,seqval from temp;

SEQVAL SEQVAL
------ ------
2      2

get_seq_val:2
SQL> with temp as( select /*+ materialize */
  2         get_seq_val as seqval from dual)
  3         select seqval,seqval from temp;

SEQVAL SEQVAL
------ ------
3      3

get_seq_val:3
SQL>

But we would get unexpected results in, for example, multi-row select:

SQL> with temp as( select /*+ no_merge */
  2         get_seq_val as seqval  from dual)
  3         select deptno,seqval,seqval from temp,dept;

    DEPTNO SEQVAL SEQVAL
---------- ------ ------
        10 4      4
        20 4      4
        30 4      4
        40 4      4

get_seq_val:4
SQL>

while most likely we expexted:

SQL> with temp as( select /*+ no_merge */
  2         get_seq_val as seqval  from dual)
  3         select deptno,seqval,seqval from temp,dept;

    DEPTNO SEQVAL SEQVAL
---------- ------ ------
        10 4      4
        20 5      5
        30 6      6
        40 7      7

get_seq_val:4
get_seq_val:5
get_seq_val:6
get_seq_val:7
SQL>

SY.

Report message to a moderator

Previous Topic:	how to sum the amounts
Next Topic:	RETUNR FUNCTION USING "TYPE"

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 00:31:06 CDT 2024