Variable manipulation in Oracle
So I’ve been digging around on the net all afternoon to find the answer to this. I have a table that looks like so:
CREATE TABLE ldap_domains ( domain varchar(128) NOT NULL, name varchar(16) NOT NULL, description varchar(32) NOT NULL ) ALTER TABLE ldap_domains ADD CONSTRAINT pk_domain PRIMARY KEY (domain); INSERT INTO ldap_domains VALUES ('test', 'test', 'test'); INSERT INTO ldap_domains VALUES ('test1', 'test', 'test'); INSERT INTO ldap_domains VALUES ('test2', 'test', 'test'); INSERT INTO ldap_domains VALUES ('test3', 'test', 'test');
I need to modify this table so that a new column becomes the primary key. As part of this I need to update the new column with unique values before the constraint is applied. This looks like so:
ALTER TABLE ldap_domains DROP CONSTRAINT pk_domain; ALTER TABLE ldap_domains ADD id int; DECLARE @key int SET @key = 0 UPDATE ldap_domains SET @key = id = @key + 1; ALTER TABLE ldap_domains ALTER COLUMN id int NOT NULL; ALTER TABLE ldap_domains ADD CONSTRAINT pk_id PRIMARY KEY (id);
This works as expected with SQLServer, however, I need this to work for Oracle as well. Essentially the section Oracle doesn’t like is:
DECLARE @key int SET @key = 0 UPDATE ldap_domains SET @key = id = @key + 1;
Can anyone enlighten me?
2 Solutions collect form web for “Variable manipulation in Oracle”
It appears that what you’re trying to do is initially set each row to have a different ID. The preferred way to do this in Oracle is to use a sequence:
CREATE SEQUENCE seq_ldap_domains START WITH 0; UPDATE ldap_domains SET id = seq_ldap_domains.nextval;
This will not guarantee that the inserted values are sequential (though, in this case, they likely will be), if that’s relevant.
Since it is not likely that you will have any other processes updating the field at the same time, it’s probably safe to cheat a little and use something like this, if you really don’t want to create a sequence:
UPDATE ldap_domains SET id = rownum-1;
As far as I know, you can’t simultaneously read and set an environment variable on a per-row basis in Oracle.
The sytnax is not correct for Oracle.
You could try something like
declare v_key integer := 0; begin update ldap_domains set ldap_domains.key = v_key+1; end;
I suggest you brush up on PL/SQL syntax, it’s a bit different. This code sample above is just a starting point, you will probably have a bit more work to do, and it would look differnent if you wanted to do it in a sqlplus script but not use PL/SQL blocks.
I also admit I’m confused about what
SET @key = id = @key + 1; will do, so I’m not 100% sure how to translate it. Is this a SQL-Server feature? What will be the result of this statement?