Home » SQL & PL/SQL » SQL & PL/SQL » create sequence within pl/sql execute immediate.
create sequence within pl/sql execute immediate. [message #202536] Fri, 10 November 2006 04:17 Go to next message
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
Re: create sequence within pl/sql execute immediate. [message #202538 is a reply to message #202536] Fri, 10 November 2006 04:34 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: create sequence within pl/sql execute immediate. [message #616854 is a reply to message #202538] Sat, 21 June 2014 11:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3122
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.
Re: create sequence within pl/sql execute immediate. [message #616857 is a reply to message #616854] Sat, 21 June 2014 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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.
Re: create sequence within pl/sql execute immediate. [message #616860 is a reply to message #616857] Sat, 21 June 2014 12:23 Go to previous message
Lalit Kumar B
Messages: 3122
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.
Previous Topic: PARALLEL hint is not working in 11g.
Next Topic: Table Design Consideration
Goto Forum:
  


Current Time: Sat Dec 10 05:17:13 CST 2016

Total time taken to generate the page: 0.13616 seconds