Declaring variable in PL/SQL when Commit transaction

Question

I try to write this transaction in Oracle Developer:

DECLARE @UserID int:
SET @UserID = 2323;

BEGIN TRANSACTION;

DELETE FROM OWNER.TABLE
WHERE USER_ID = @UserID;

COMMIT TRANSACTION

But int is red - error say about there is no in or out

Answer 1

Since this is tagged Oracle and you say you are using Oracle Developer...

The syntax you are using is entirely wrong for an Oracle database - you appear to be trying to use code intended for SQL SERVER.

Re-writing it for oracle can be as simple as:

DELETE FROM OWNER.TABLE
WHERE USER_ID = 2323;

COMMIT;

However, if you particularly need a bind variable then:

VARIABLE UserID NUMBER;

BEGIN
  :UserID := 2323;
END;
/

DELETE FROM OWNER.TABLE
WHERE USER_ID = :UserID;

COMMIT;

or (using a pl/sql variable):

DECLARE
  UserID OWNER.TABLE.USER_ID%TYPE := 2323;
BEGIN
  DELETE FROM OWNER.TABLE
  WHERE USER_ID = UserID;

  COMMIT;
END;
/

However, if it is SQL Server then this works:

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE table_name ( User_ID INT );

INSERT INTO table_name VALUES ( 2322 );
INSERT INTO table_name VALUES ( 2323 );

DECLARE @UserID INT = 2323;

BEGIN TRANSACTION;

DELETE FROM table_name
WHERE  User_ID = @UserID;

COMMIT TRANSACTION;

Query 1:

SELECT * FROM table_name

Results:

| User_ID |
|---------|
|    2322 |