0

I have a simple Oracle query:

SELECT SEQ_01.NEXTVAL FROM DUAL

I want write this query in SQL standard for run it in both DB

1
  • 1
    That's nice. Good luck figuring it out. Did you have a question? Commented Apr 20, 2015 at 16:13

1 Answer 1

5

The equivalent of Oracle's select seq_01.nextval from dual is:

select nextval('seq_01');

More details in the manual: http://www.postgresql.org/docs/current/static/functions-sequence.html

There is no way you can write a SQL statement that works in both DBMS equally. Neither of them implements the syntax defined by SQL standard for sequences


Using your own functions you can actually make the same SQL work in both DBMS.

For Oracle

create or replace function seq_nextval(p_sequence_name varchar)
  return integer
as
  l_statement varchar(4000);
  l_value     integer;
begin 
  l_statement := 'select '||upper(p_sequence_name)||'.nextval from dual';
  execute immediate l_statement
    into l_value;
  return l_value;
end;
/  

select seq_nextval('seq_01')
from dual;

For Postgres

create table dual (dummy varchar(1));
insert into dual values ('X');

create function seq_nextval(p_sequence_name text)
  returns bigint
as
$$
   select nextval(p_sequence_name);
$$
language sql;

select seq_nextval('seq_01')
from dual;

Note that you pay the usual price for DBMS independent SQL: it runs equally slow on all platforms. Especially the Oracle workaround with PL/SQL and dynamic SQL is going to be massively slower than a plain seq_01.nextval call.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.