Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to handle error and rowcount just after insert /update statement in Oracle 12c or upper version

User_CSOQUDec 18 2019 — edited Dec 18 2019

I can select @@error and @@rowcount just after insert /update statement in sql server in one line, how to handle or write this procedure in oracle 12c or upper version

so that it can handle  error and rowcount just after insert /update statement like sql server in one line.

I have describe my procedure with sample data and execution process in sql server.

USE [Test]

GO

CREATE TABLE [dbo].[Employee](

[Employee_id] [varchar](50) NULL,

[Name] [varchar](50) NULL,

[Father_Name] [varchar](50) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

INSERT [dbo].[Employee] ([Employee_id], [Name], [Father_Name]) VALUES (N'0001', N'Khan', N'Bahadur')

GO

INSERT [dbo].[Employee] ([Employee_id], [Name], [Father_Name]) VALUES (N'0002', N'Musa', N'Misha')

GO

INSERT [dbo].[Employee] ([Employee_id], [Name], [Father_Name]) VALUES (N'0003', N'Don', N'Donald')

GO

USE [Test]

GO

CREATE PROCEDURE [dbo].[Proc_InsUpdEmployee]

(

   @VEmp_id  varchar(50),

   @VName varchar(50),

   @VFather_Name varchar(50),

   @VVMSG VARCHAR(100) OUTPUT,

   @Vmsg_code VARCHAR(10) OUTPUT

)

AS

SET NOCOUNT ON;

DECLARE @RowsCount int, @err int

BEGIN TRANSACTION

if exists ( select 1 from Employee WHERE Employee_id = @VEmp_id)

begin

UPDATE Employee

SET    Name=@VName, Father_Name = @VFather_Name

WHERE Employee_id = @VEmp_id;

SELECT @RowsCount = @@ROWCOUNT,@err = @@ERROR

IF @err <> 0

BEGIN

    ROLLBACK TRANSACTION

    SELECT @Vmsg_code = 'ERR',

           @VVMSG = 'Update Failed! Data Rollback.';

    RETURN;

END

IF @RowsCount = 0

BEGIN

    ROLLBACK TRANSACTION

    SELECT @Vmsg_code = 'ERR',

           @VVMSG = 'NO Rows Updated! Data Rollback.';

    RETURN;

END

COMMIT TRANSACTION;

SELECT @Vmsg_code = 'Success',

           @VVMSG = 'Data Updated Successfully.';

    RETURN;

end

else

begin

insert into Employee(Employee_id,Name,Father_Name)

select @VEmp_id,@VName,@VFather_Name;

SELECT @RowsCount = @@ROWCOUNT,@err = @@ERROR

IF @err <> 0

BEGIN

    ROLLBACK TRANSACTION

    SELECT @Vmsg_code = 'ERR',

           @VVMSG = 'Insert Failed! Data Rollback.';

    RETURN;

END

IF @RowsCount = 0

BEGIN

    ROLLBACK TRANSACTION

    SELECT @Vmsg_code = 'ERR',

           @VVMSG = 'NO Rows Inserted! Data Rollback.';

    RETURN;

END

COMMIT TRANSACTION;

SELECT @Vmsg_code = 'Success',

           @VVMSG = 'Data Inserted Successfully.';

    RETURN;

end

go

--- Execute procedure

DECLARE @return_value int,

@VVMSG varchar(100),

@Vmsg_code varchar(10)

EXEC @return_value = [dbo].[Proc_InsUpdEmployee]

@VEmp_id = N'0004',

@VName = N'Mac Hill',

@VFather_Name = N'Banzamin',

@VVMSG = @VVMSG OUTPUT,

@Vmsg_code = @Vmsg_code OUTPUT

SELECT @VVMSG as N'@VVMSG',@Vmsg_code as N'@Vmsg_code'

Output:

@VVMSG                               @Vmsg_code

Data Inserted Successfully.           Success

GO

Comments
Post Details
Added on Dec 18 2019
1 comment
748 views