Home » SQL & PL/SQL » SQL & PL/SQL » Create sequence.
Create sequence. [message #224570] Wed, 14 March 2007 12:34 Go to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Hi all.

Can I create a sequence in my pl/sql program? Thanks.
Re: Create sequence. [message #224572 is a reply to message #224570] Wed, 14 March 2007 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>Can I create a sequence in my pl/sql program?
I give up can you.
FWIW - this is BAD programming technique/form. You should avoid doing DDL from PL/SQL.
if you must do so, you'll have to use EXECUTE IMMEDIATE.
Re: Create sequence. [message #224574 is a reply to message #224572] Wed, 14 March 2007 12:45 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Hi.

If I create sequence in SQL, Can I use it in my pl/sql program?
Thank you.
Re: Create sequence. [message #224586 is a reply to message #224574] Wed, 14 March 2007 13:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ok, why do you think you need to create a sequence from PL/SQL?
Re: Create sequence. [message #224607 is a reply to message #224586] Wed, 14 March 2007 14:06 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
I have to create a unique sequence for a variable. I don't know if I create it in sql and can use it in my program or I have to create it in my program. Because this is my first time of using pl/sql language.
Re: Create sequence. [message #224618 is a reply to message #224607] Wed, 14 March 2007 15:23 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You create it in SQL. You can then use it in your PL/SQL programs. If it is in another schema, you will need the proper permissions to use it.
Re: Create sequence. [message #224756 is a reply to message #224618] Thu, 15 March 2007 06:10 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thank you.
Re: Create sequence. [message #224799 is a reply to message #224618] Thu, 15 March 2007 09:23 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
joy_division wrote on Wed, 14 March 2007 15:23
You create it in SQL. You can then use it in your PL/SQL programs. If it is in another schema, you will need the proper permissions to use it.


However, if the sequence doesn't exist and you use it in code, the sql code will not compile and the code will never run. For example

begin
execute immediate 'create sequence my_seq';
insert into my_table(col1) values(my_seq.nextval);
end;

will fail because my_seq doesn't exist when the block is parsed.
Re: Create sequence. [message #224845 is a reply to message #224799] Thu, 15 March 2007 12:47 Go to previous message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But one can still abuse EXECUTE IMMEDIATE and make it work.
SQL> begin
  2  execute immediate 'create sequence my_seq';
  3  execute immediate 'create table test (a number)';
  4  execute immediate 'insert into test values (my_seq.nextval)';
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * From test;

         A
----------
         1

SQL>
Previous Topic: Column Headings
Next Topic: PLS-00905 -- Why am I getting this
Goto Forum:
  


Current Time: Mon Dec 05 12:40:57 CST 2016

Total time taken to generate the page: 0.08689 seconds