ORA-00933: SQL command not properly ended when deleting two tables at the same time

Question

I am trying to delete data from two related tables - primary key in the table users and foreign key in the table login - but I'm getting error PL/SQL: ORA-00933: SQL command not properly ended.

Create table users and primary key:

/* table user*/
create table users (id_user number(10) not null, 
                    name_user varchar(30) not null);
/* primary key */
alter table users add constraint user_pk primary key (id_user);

Create table login and primary key and foreign key:

/* table login*/
create table login (id_login number(10) not null, 
                    id_user_login number(10) not null, 
                    email_login varchar(20) not null,
                    password_login varchar(20) not null);
/* primary key */
alter table login add constraint login_pk primary key (id_login);

/* foreign key reference to user*/
alter table login add constraint login_fk_user foreign key (id_user_login)
references users(id_user) on delete cascade;

Procedure to create session with table users/login, which works:

PROCEDURE create_user_session( p_name   IN VARCHAR2,
                               p_email  IN VARCHAR2,
                               p_pass   IN VARCHAR2,
                               p_error  OUT NUMBER,
                               p_msg_error OUT VARCHAR2)
  IS
  BEGIN
    p_error := 0;
    INSERT ALL

    INTO users (id_user, name_user) VALUES(seq_user.NEXTVAL,p_name)

    INTO login(id_login, id_user_login, email_login, pass_login)
    VALUES(seq_login.NEXTVAL, seq_user.CURRVAL, p_email, p_pass)

    SELECT * FROM DUAL COMMIT;
  EXCEPTION
  WHEN OTHERS THEN
    p_error     := 1;
    p_msg_error := 'Error!'||SQLERRM;
  END create_user_session;

Now I want to delete this session, but I have error PL/SQL: ORA-00933: SQL command not properly ended from this procedure:

PROCEDURE delete_user_session(
    p_id_user IN NUMBER,
    p_error OUT NUMBER,
    p_msg_error OUT VARCHAR2)
IS
BEGIN
  p_error := 0;
  DELETE FROM users, login USING users
  INNER JOIN login WHERE users.id_user = p_id_user
  AND login.id_user_login  = p_id_user;
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  p_error     := 1;
  p_msg_error := 'Error!'||SQLERRM;
END delete_user_session;

I have this image from SQL developer to show the error (red squiggle underline on the s, in DELETE FROM users, login ... if you can't see the image):

What am I doing wrong?

Answer 1

You can't delete from two tables in one statement - there is no delete equivalent of insert all. (Unless you have constraints that cascade the delete, or a trigger that does that manually). The documentation shows that your syntax is not valid, as there is no path to specify more than one table.

You will need to have two delete statements, removing the records from the child table first:

  DELETE FROM login 
  WHERE login.id_user_login = p_id_user;
  DELETE FROM users
  WHERE users.id_user = p_id_user;

You could change your foreign key constraint to delete cascade:

alter table login add constraint login_fk_user foreign key (id_user_login)
references users(id_user) on delete cascade;

... which would mean you would only have to explicitly delete from the users table; but that may not actually be what you want, as it removes one level of validation - you may want to prevent a parent key being accidentally removed if it has children. Issuing two deletes doesn't really hurt here.

Incidentally, your first procedure is not committing, which you might be expecting. In this line:

    ...
    SELECT * FROM DUAL COMMIT;

... the COMMIT is interpreted as an alias for the DUAL table, not a separate command. You would need a semicolon after DUAL, and preferably a new line for the COMMIT;. But it's generally considered better not to commit in a procedure, and let the top-level caller decide whether to commit or roll back to preserve data integrity.