CODEWITHSUNDEEP
sqloracle-databaseprimary-keyincrement
Kevin
Kevin

Reputation: 177

inserting into with an incrementing value

The best way I can think to explain this is: I'm trying to give each robot a unique id. Then, when I run the procedure, I only have to give the robot a name and id. The status are generated for me.

However, I am reaching a total roadblock because I can't find out the proper way to do this. I've tried counting the rows and putting it into tempid. That doesn't work. A new row is made every time I run it. So, I should always get a new id. I might have done it wrong though.

I get this error:

Error(23,44): PL/SQL: ORA-00984: column not allowed here

Here is the procedure:

CREATE OR replace PROCEDURE Checkbot
(nameinput IN VARCHAR2)
IS
  partfound NUMBER(4);
  foundall BOOLEAN;
  tempid NUMBER;
BEGIN

--Where I am having issues:

  SELECT Count(*)
  INTO tempid
  FROM robotinventory;
  INSERT INTO robotinventory VALUES (tempid, nameinput, NULL);

  foundall := TRUE;
  FOR i IN 1..8 LOOP
    partfound := 0;
    SELECT Max(prtserial)
    INTO partfound
    FROM partinventory
    WHERE parttypeid = i;

    IF partfound > 0 THEN
      DELETE FROM partinventory
      WHERE prtserial = partfound;
      INSERT INTO robotprt VALUES (tempid, idinput, i);
    ELSE
      foundall := FALSE;
    END IF;
  END LOOP;


    IF foundall THEN
      UPDATE robotinventory
      SET status = 'ready for assembly'
      WHERE robotid = tempid;
    ELSE
      UPDATE robotinventory
      SET status = 'waiting on parts'
      WHERE robotid = tempid;
    END IF;

  END;/

robot inventory table:

CREATE TABLE RobotInventory
(RobotID Number(4) PRIMARY KEY,
RobotName VARCHAR2(24),
Status VARCHAR2(64));

Previous version of my code, took out what I did wrong in mine, my goal is to replace idInput with an auto incrementing number:

create or replace procedure checkbot
(idInput in number, nameInput in varchar2)
is
  partFound number(4);
  foundAll boolean;
begin
  insert into robotInventory values (idInput, nameInput, null);
  foundAll := true;
  for i in 1..8 loop
    partFound := 0;
    select max(prtSerial)
    into partFound
    from partInventory
    where ParttypeID = i;

    if partFound > 0 then
      delete from partInventory
      where prtSerial = partFound;
      insert into robotPrt values (partFound, idInput, i);
    else
      foundAll := false;
    end if;
  end loop;


    if foundAll then
      update robotInventory
      set status = 'ready for assembly'
      where robotID = idInput;
    else
      update robotInventory
      set status = 'waiting on parts'
      where robotID = idInput;
    end if;

  END;
  /

Upvotes: 0

Views: 59

Answers (1)

tale852150
tale852150

Reputation: 1638

Create a sequence (called seq below) and use it in your INSERT SQL to populate robot_id.

INSERT INTO robotinventory VALUES (seq.nextval, nameinput, NULL);

Look at Oracle docs on how to create a sequence in more detail. Here is an example that you can change to fit your needs.

create sequence seq
start with 1
increment by 1
maxvalue 9999;

HTH

Upvotes: 1

Related Questions