Oracle11g variable declaration

Question

I'm trying to increment a sequence by the grater value from an id column (numeric type but not int). For this I've tried to declare an int variable and selecting into it the max value from the table like this:

declare
        last_id int;
begin
        select max(id) into last_id from my_table;
        alter sequence my_table_seq increment by last_id;    
end;

However, I'm getting this error

ORA-06550: line 2, column 19:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
  := . ( @ % ; not null range default character

I'm using Oracle 11g. Here's a screenshot

If you want to put DDL in a PL/SQL block, you need to use dynamic SQL and you can't use variables. You'd need to build an appropriate SQL statement in a string and EXECUTE IMMEDIATE that. My guess is that you're trying to do something like the reset_sequences procedure here does asktom.oracle.com/pls/asktom/… — Justin Cave
– Justin Cave, Commented Aug 11, 2015 at 14:36
You want the sequence to increment by the value of max(id) ? — David Aldridge
– David Aldridge, Commented Aug 11, 2015 at 14:50
@JustinCave did not know that. However I'm getting the error in the variable declaration. I've tried using execute immediate but I'm getting the same error — Nicolas Schejtman
– Nicolas Schejtman, Commented Aug 11, 2015 at 14:50

Praveen · Accepted Answer · 2015-08-11 15:02:43Z

1

declare
     last_id number;
begin
     select max(id) into last_id from my_table;
     execute immediate 'alter sequence my_table_seq increment by ' || to_char(last_id);    
end;

Use any of oracle Numeric Data Types instead of int

Use execute immediate for any DDN in anonymous block

edited Aug 11, 2015 at 15:02

answered Aug 11, 2015 at 14:43

Praveen

9,4634 gold badges38 silver badges51 bronze badges

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

6 Comments

Justin Cave Over a year ago

You can't use bind variables in DDL.

Nicolas Schejtman Over a year ago

I'm still getting the error. The error occurs in the declare statement. The error code is PLS-00103

Nicolas Schejtman Over a year ago

@Praveen same thing.Here is a screenshot

Praveen Over a year ago

@Nicolas Schejtman I tested it locally by creating table, sequence and execute the above query, worked fine for me.

Nicolas Schejtman Over a year ago

@Praveen must be a problem with the db management software then?

|

mahi_0707 · Accepted Answer · 2015-08-11 22:20:08Z

Created similar objects and it works as expected.

Suggest you to try the below:

1.Change your column to from ID to something_id.

2.Remove double quotes from schema and table name (just give schema.table_name)

3.Ensure you do not miss a semi colon at the end of statement and all variables declared properly.

If still doesn't work then go for trial and error method.

Just take simple block and try printing the ID value alone.

Then keep adding your logic (assign value to last_id and try print last_id).

Then finally add your execute immediate statement and see if it works.

Create table general_discount_type
(id number);
    -- Table created.

Begin
insert into general_discount_type values(101);
insert into general_discount_type values(102);
insert into general_discount_type values(103);
insert into general_discount_type values(104);
End;
-- Statement processed.

Create sequence general_discount_type_seq
start with 1
increment by 1
NOCACHE
NOCYCLE;
-- Sequence created.

 Declare 
 last_id number;
 Begin
 select max(id) into last_id from MAHI_SCHEMA.general_discount_type;
 execute immediate 'alter sequence MAHI_SCHEMA.general_discount_type_seq increment by ' 
 || to_char(last_id);
 dbms_output.put_line('Value in last_id is : ' || last_id);
 End;
  -- Value in last_id is : 104

Output

Collectives™ on Stack Overflow

Oracle11g variable declaration

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related