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: 25531
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: 4758
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: 1650
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: 21127
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: Sat Jul 22 22:23:49 CDT 2017

Total time taken to generate the page: 0.11871 seconds