-2

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

RafalQA
  • 127
  • 2
  • 3
  • 12
  • 3
    Oracle and tsql?!? One tag should be removed... – jarlh Dec 17 '15 at 11:08
  • 2
    if this is copy/pasted the problem would be the colon after `int` which should be a semicolon – Bill Tür stands with Ukraine Dec 17 '15 at 11:12
  • I change to semicolon but still is wrong :( – RafalQA Dec 17 '15 at 11:28
  • If you are trying to run T-SQL (or PL/SQL) in Oracle's SQL Developer are you running it using either the "Green arrow" button or the keyboard shortcut `CRTL-Enter` to run each statement individually or running it as a script using the "Green arrow with a page behind it" button or the keyboard shortcut `F5`? You ought to be running it as a script. – MT0 Dec 17 '15 at 13:10
  • i INDICATE THEN IT IS WRONG AFTER RUN – RafalQA Dec 17 '15 at 13:45

2 Answers2

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 |
MT0
  • 143,790
  • 11
  • 59
  • 117
0

use this one 

DECLARE @UserID int;  set @UserID = 2323
Neeraj Sharma
  • 568
  • 6
  • 18