CODEWITHSUNDEEP
sqlsql-serveroracle-databaseplsql
AndyF
AndyF

Reputation: 1094

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?

Upvotes: 1

Views: 1091

Answers (2)

Allan
Allan

Reputation: 17429

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.

Upvotes: 3

FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27516

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?

Upvotes: 3

Related Questions