Skip to Main Content
  • Questions

  • Dynamically passing sequence name to get currval

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Aryan.

Asked: August 10, 2020 - 10:49 pm UTC

Last updated: August 11, 2020 - 4:39 pm UTC

Version: Oracle 12c

Viewed 1000+ times

You Asked

I am trying to get the currval of all the user sequences in the schema. When I run the below sql it gives me invalid sql statement.
I am not sure if the below is the right way to achieve it. Please advise.
Assumption: The current value of the sequences are already set in the session.

set serveroutput on;
declare
sq number;
sqnm varchar2(50); 
stmt varchar2(1000);

cursor sqnc is (select sequence_name from user_sequences);
begin
    for row in sqnc loop
    sqnm := row.sequence_name;
    stmt := 'SELECT' || sqnm ||'.currval into' || sq || 'from dual';
    execute immediate stmt;
   dbms_output_put_line(sqnm || ' ' ||sq);
    end loop;
end;


and Chris said...

select ... into ... is a PL/SQL construct.

With execute immediate you're running SQL The into clause is part of this statement:

execute immediate stmt
  into ...;


Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Aryan, August 11, 2020 - 2:21 pm UTC

I changed the line to
stmt := 'SELECT' || sqnm ||'.currval from dual';
execute immediate stmt into sq;

I am still getting the invalid SQL statement error.
Also, when I tried to pass a sequence name using sqnm it gives the same error.
Kindly advise .
Chris Saxon
August 11, 2020 - 2:36 pm UTC

Look very carefully at the SQL statement you're generating. Log it somewhere and I'm sure the answer will reveal itself.

Found the issue with my stmt

Aryan, August 11, 2020 - 3:07 pm UTC

Thanks Chris for the solution. It worked
Chris Saxon
August 11, 2020 - 4:39 pm UTC

Great :)

A reader, August 11, 2020 - 4:03 pm UTC


A reader, August 11, 2020 - 4:04 pm UTC


A reader, August 11, 2020 - 4:04 pm UTC


A reader, August 11, 2020 - 4:04 pm UTC


A reader, August 11, 2020 - 4:04 pm UTC


A reader, August 11, 2020 - 4:04 pm UTC


A reader, August 11, 2020 - 4:05 pm UTC


A reader, August 11, 2020 - 4:05 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.