OraFAQ Forum: SQL & PL/SQL » create sequence within pl/sql execute immediate.

Home » SQL & PL/SQL » SQL & PL/SQL » create sequence within pl/sql execute immediate.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

create sequence within pl/sql execute immediate. [message #202536]

Fri, 10 November 2006 04:17

laksha
Messages: 42
Registered: June 2006

Member

I am creating a sequence using execute immediate in PL/SQL
and I am getting the following error. I am not able to use seq.nextval in the statements that follow.

set serveroutput on
declare
a number;
i number;
begin
EXECUTE IMMEDIATE 'CREATE SEQUENCE TEST_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 1000000 NOCYCLE NOCACHE ORDER';
for i in 1..2 loop
select test_seq.nextval into a from dual;
dbms_output.put_line(a);
end loop;
end;
ORA-06550: line 7, column 11:
PL/SQL: ORA-02289: sequence does not exist
ORA-06550: line 7, column 4:
PL/SQL: SQL Statement ignored

Then I create the sequence using using SQL

SQL>CREATE SEQUENCE TEST_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 1000000 NOCYCLE NOCACHE ORDER;

and I use the sequence in the PL/SQL

set serveroutput on
declare
a number;
i number;
begin
for i in 1..2 loop
select test_seq.nextval into a from dual;
dbms_output.put_line('A');
end loop;
end;
3
4
PL/SQL procedure successfully completed

Please help.
Thanks

Report message to a moderator

Re: create sequence within pl/sql execute immediate. [message #202538 is a reply to message #202536]

Fri, 10 November 2006 04:34

Maaher
Messages: 7065
Registered: December 2001

Senior Member

laksha wrote on Fri, 10 November 2006 11:17

Please help.

There's nothing we can do: at compile time, the sequence does not exist. You could, however, use EXECUTE IMMEDIATE again:

set serveroutput on
declare
  a number;
begin
  EXECUTE IMMEDIATE 'CREATE SEQUENCE TEST_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 1000000 NOCYCLE NOCACHE ORDER';
  for i in 1..2 loop
    EXECUTE IMMEDIATE 'select test_seq.nextval v from dual' INTO a;
    dbms_output.put_line(a);
  end loop;
end; 
/

drop sequence test_seq
/

Note: the declaration of i is not necessary. It is never used. Loop variables are declared implicitly.

MHE

Report message to a moderator

Re: create sequence within pl/sql execute immediate. [message #616854 is a reply to message #202538]

Sat, 21 June 2014 11:31

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I know it is a zombie thread, however, I am compelled to post.

So this is the reason, one of my reporting colleague came across this thread and implemented it in a new business logic, and I simply asked him to change the design and asked him not to create sequence in such fashion. I explained him that Maarten replied OP for his specific requirement and I hope it isn't a good practice to continue with. After a long argument, I could finally change the design and created the required sequence and discussed it in our daily standup meeting per agile methodology.

P.S. Per my colleague's request, I am posting in a zombie thread. If anybody has any other suggestions or if someone defer me, please do reply. It's the first time I came to know that people believe so much in the answers provided in this forum. This has made me so much concerned that from next time, before posting a solution I need to make sure if it's completely relevant to use.

Lest I should include a disclaimer that please test before implementing any of the forum suggestions in your production database.

Report message to a moderator

Re: create sequence within pl/sql execute immediate. [message #616857 is a reply to message #616854]

Sat, 21 June 2014 12:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

not everything posted on the Internet is considered to be "Best Practices"
IMO, no object should ever be created as part of executable PL/SQL code.
Every required object should only ever be created by static DDL SQL statements executed during software version upgrades.

Report message to a moderator

Re: create sequence within pl/sql execute immediate. [message #616860 is a reply to message #616857]

Sat, 21 June 2014 12:23

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

For a reason, this happened to be just one thread which wasn't conplete enough with respect to folks looking for resolution on similar subject.

@BS, I completely agree with you, that not everything is ought to be considered helpful through google search in internet. But, when juniors tend to do such things, some or the other responsible person needs to take care of it. In this case its me.

P.S. I believe Michel does a good job asking OPs to post their solutions to complete the thread. Else the entire thread is actually of no use.

Report message to a moderator

Previous Topic:	PARALLEL hint is not working in 11g.
Next Topic:	Table Design Consideration

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 21:28:23 CDT 2024