Home » SQL & PL/SQL » SQL & PL/SQL » Alter in PL/SQL
Alter in PL/SQL [message #220985] Fri, 23 February 2007 00:32 Go to next message
ultimate123
Messages: 2
Registered: January 2007
Location: US
Junior Member
I was trying to Alter a sequence within PL/SQL. (Execute Immediate 'Alter sequence s increment by 10')

It didnot give any error.. But the sequence was not getting altered.

I read somewhere that we cant use DDL within PL/SQL. But I was using Create and Truncate as shown below, and worked.

If that is the case, is there anything special with ALTER?? Is there any way to use ALTER inside PL/SQL??

Thanks
VR
Re: Alter in PL/SQL [message #220986 is a reply to message #220985] Fri, 23 February 2007 00:53 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

does this work for you.

create sequence s start with 1
/
select sequence_name,
min_value,
max_value,
increment_by from user_sequences where sequence_name='S'
/
declare
sql_stat varchar2(100);
begin
sql_stat :='alter sequence s increment by 10';
execute immediate sql_stat;
end;
/
select sequence_name,
min_value,
max_value,
increment_by from user_sequences where sequence_name='S'
/


regards,
Re: Alter in PL/SQL [message #221440 is a reply to message #220986] Mon, 26 February 2007 14:53 Go to previous message
ultimate123
Messages: 2
Registered: January 2007
Location: US
Junior Member
I got it... I was using

EXECUTE IMMEDIATE 'SELECT s.NEXTVAL FROM dual';

and hence the sequence was not getting incremented. I took select out of Execute Immediate and it worked.

Thanks for your help
Previous Topic: Convert Number to Word (currency conversion)
Next Topic: Query rewrite failing to use an obvious MView candidate
Goto Forum:
  


Current Time: Sat Dec 03 08:22:25 CST 2016

Total time taken to generate the page: 0.04722 seconds