Oracle PL-SQL sequence

Question

Good Day,

I am trying to use a pl/sql variable in my query (I'm a sql server guy) and here is my code:

DECLARE
    nextvalue     integer;
BEGIN

    SELECT submission_seq.currval INTO nextvalue FROM DUAL;

    dbms_output.put_line('test');    
    dbms_output.put_line(nextvalue);

    /* ERROR OCCURS HERE */
    SELECT nextvalue, id FROM TABLE_STATUS;

END;

I'm getting the PLS-00428: an INTO clause is expected in this SELECT statement.

I'm not understanding why I'm getting this as I used one to populate nextvalue which does contain what I want.

Can anyone help me?

TIA,

coson

dimitrisli · Accepted Answer · 2012-10-18 22:48:18Z

1

It's because, as the message describes, the select statement is expecting you to allocate the result into a variable.

Try:

DECLARE
    nextvalue     integer;
    result        table_status%type;
BEGIN

    SELECT submission_seq.nextval INTO nextvalue FROM DUAL;

    dbms_output.put_line('test');    
    dbms_output.put_line(nextvalue);


    SELECT id INTO result FROM TABLE_STATUS;

END;

edited Oct 18, 2012 at 22:48

answered Oct 18, 2012 at 22:32

dimitrisli

21.4k12 gold badges64 silver badges65 bronze badges

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

2 Comments

coson Over a year ago

Well this certainly makes more sense. I take it that "table_status%type" refers to a column in TABLE_STATUS.

dimitrisli Over a year ago

yes you want your variable to be of the same type as the type of the column whose data you are trying to save in your id.

manuerumx · Accepted Answer · 2012-10-18 22:36:21Z

0

The secuence has started?

Change this

SELECT submission_seq.currval INTO nextvalue FROM DUAL;

for this

SELECT submission_seq.nextval INTO nextvalue FROM DUAL;

answered Oct 18, 2012 at 22:36

manuerumx

1,2601 gold badge14 silver badges30 bronze badges

Comments

Swapna Mohan · Accepted Answer · 2013-01-25 22:54:22Z

0

If you are using Oracle 11g then you don't have to select from dual anymore. Below snippet will work.

/* Formatted on 1/25/2013 4:35:00 PM */
DECLARE
   nextvalue      NUMBER := submission_seq.currval;
BEGIN
   DBMS_OUTPUT.put_line ('test');
   DBMS_OUTPUT.put_line (nextvalue);
END;

answered Jan 25, 2013 at 22:54

Swapna Mohan

1751 silver badge13 bronze badges

Comments

kleopatra · Accepted Answer · 2013-01-26 10:55:50Z

0

Try this:

DECLARE
    nextvalue     integer;
BEGIN
nextvalue := submission_seq.currval;

dbms_output.put_line('test');    
dbms_output.put_line(nextvalue);

SELECT nextvalue, id FROM TABLE_STATUS;

END;

edited Jan 26, 2013 at 10:55

kleopatra

51.6k28 gold badges103 silver badges220 bronze badges

answered Jan 26, 2013 at 10:50

user2001117

3,7871 gold badge20 silver badges18 bronze badges

Collectives™ on Stack Overflow

Oracle PL-SQL sequence

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related