Variable manipulation in Oracle

Question

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?

score 3 · Answer 1 · answered Jan 20 '11 at 16:28

3

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?

answered Jan 20 '11 at 16:28

FrustratedWithFormsDesigner

26,726
31
139
202

SET is ANSI: http://stackoverflow.com/questions/3945361/tsql-set-vs-select-when-assigning-variables – OMG Ponies Jan 20 '11 at 16:32
Yes, I appreciate that the SQL I entered isn't applicable to Oracle as it's written for SQLServer. I've been attempting to understand the Oracle syntax with little success so far. The effect of the SET part updates the key variable as well as the id column. – AndyF Jan 20 '11 at 16:35
@AndyF: I'm not that familiar with the way you are using `SET` in your update. I am guessing it does 1) assign value of `id` to `@key` 2) assgins result of `@key+1` to `id`, is this correct? – FrustratedWithFormsDesigner Jan 20 '11 at 16:42
1

The value of @key + 1 is assigned to both id and @key. – AndyF Jan 21 '11 at 08:22

Allan · Accepted Answer · 2011-01-20T20:09:17.940

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.

Great, this is just what I want. The script is required for a simple migration, so a little cheat is just perfect. — AndyF, Jan 21 '11 at 08:31

Variable manipulation in Oracle

2 Answers2

Linked