CODEWITHSUNDEEP
sqloracle-databasepostgresql
Mauro Midolo
Mauro Midolo

Reputation: 1958

Migrate oracle sql query to PostgreSql

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

Upvotes: 0

Views: 1923

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 5

Related Questions