Skip to Main Content
  • Questions
  • Dynamically passing sequence name to get currval

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Aryan.

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

Answered by: Chris Saxon - Last updated: August 11, 2020 - 4:39 pm UTC

Category: PL/SQL - 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 we 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 ...;


and you rated our response

  (10 ratings)

Reviews

August 11, 2020 - 2:21 pm UTC

Reviewer: Aryan from Minneapolis,MN

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

Followup  

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

August 11, 2020 - 3:07 pm UTC

Reviewer: Aryan from Minneapolis,MN

Thanks Chris for the solution. It worked
Chris Saxon

Followup  

August 11, 2020 - 4:39 pm UTC

Great :)

August 11, 2020 - 4:03 pm UTC

Reviewer: A reader


August 11, 2020 - 4:04 pm UTC

Reviewer: A reader


August 11, 2020 - 4:04 pm UTC

Reviewer: A reader


August 11, 2020 - 4:04 pm UTC

Reviewer: A reader


August 11, 2020 - 4:04 pm UTC

Reviewer: A reader


August 11, 2020 - 4:04 pm UTC

Reviewer: A reader


August 11, 2020 - 4:05 pm UTC

Reviewer: A reader


August 11, 2020 - 4:05 pm UTC

Reviewer: A reader


More to Explore

SQL

The Oracle documentation contains a complete SQL reference.