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:

  • Programmatically Insert Rows Into Table
  • Conditional sort order in SQL Server windowed function clauses
  • Query XML creating field names whithout knowing node names
  • How to query for columns in sql database
  • Converting a number to datetime sql
  • Obtain column names from query using wildcard
  • 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 TABLE ldap_domains

    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?

  • Multiple Connection String
  • SQL Server 2008 INSERT Optimization
  • Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
  • Using bcp utility to export SQL queries to a text file
  • sql-server: how do i know who is in my database?
  • How to return empty groups in SQL GROUP BY clause
  • 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

      v_key integer := 0;
      update ldap_domains 
      set ldap_domains.key = v_key+1;

    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?

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.