Home » SQL & PL/SQL » SQL & PL/SQL » Getting same sequence number in select query (Oracle 19c)
Getting same sequence number in select query [message #689660] Tue, 12 March 2024 04:43 Go to next message
sss111ind
Messages: 636
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
Re: Getting same sequence number in select query [message #689661 is a reply to message #689660] Tue, 12 March 2024 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
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.
Re: Getting same sequence number in select query [message #689662 is a reply to message #689660] Tue, 12 March 2024 05:21 Go to previous messageGo to next message
cookiemonster
Messages: 13923
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;
Re: Getting same sequence number in select query [message #689663 is a reply to message #689661] Tue, 12 March 2024 05:21 Go to previous messageGo to next message
sss111ind
Messages: 636
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
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.
Re: Getting same sequence number in select query [message #689689 is a reply to message #689664] Wed, 20 March 2024 09:04 Go to previous messageGo to next message
mathguy
Messages: 108
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.
Re: Getting same sequence number in select query [message #689706 is a reply to message #689689] Wed, 27 March 2024 12:02 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: how to sum the amounts
Next Topic: RETUNR FUNCTION USING "TYPE"
Goto Forum:
  


Current Time: Thu May 23 10:08:06 CDT 2024