asked on

Encountered error while running PL/SQL block from c#.NET

Hi,

I am writing the following PL/SQL block in .NET.
DECLARE
BEGIN
INSERT INTO na_acct_add
(acct_request_id, processing_code_1022_cd,
stk_cmsn_sch_191_cd,bond_cmsn_sch_192_cd,
optn_cmsn_sch_193_cd
)
VALUES (@AcctRequestId, 'A',
'1', '1',
'1'
);
EXCEPTION WHEN OTHERS THEN
NULL;
END;

I am getting the following error while executing:
System.Data.OracleClient.OracleException: ORA-06550: line 1, column 305:
PLS-00103: Encountered the symbol "ROLLBACK" when expecting one of the following:

* & - + / at mod remainder rem then <an exponent (**)> and or
||
The symbol "then" was substituted for "ROLLBACK" to continue.
ORA-06550: line 1, column 345:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

can anyone help on this ???

Guy Hengel [angelIII / a3]

as there is no ROLLBACK in the code you showed, it must be somewhere else.
also, why do you put a DECLARE, when you have no pl/sql variables?

now, is there maybe a trigger on that table (na_acct_add)?

ramitharahul

ASKER

Hi angellll,

The below is more refined code & Iam still getting the same error:

DECLARE
proc_error INTEGER;
proc_errmsg VARCHAR2(255);
BEGIN
INSERT INTO na_acct_add(acct_request_id,
processing_code_1022_cd,
stk_cmsn_sch_191_cd,
bond_cmsn_sch_192_cd,
optn_cmsn_sch_193_cd
)
VALUES (195655,
'A',
'1',
'1',
'1'
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
proc_error := SQLCODE;
proc_errmsg := SQLERRM;
IF (proc_errmsg = 'ORA-00001: unique constraint (DEV1NA1.NA_ACCT_ADD_PK) violated') THEN
ROLLBACK;
ELSE
Na_P_Error(proc_error,
'na_p_delete_acct',
proc_errmsg ,
'delete '
);
RAISE;
END IF;
END;

ramitharahul

ASKER

Hi Angellll

195655 record already exists & the acct_request_id is a primary key on that table. I am just testing if by mistake the user enters the existing account id. It should throw the error message, but it was never happening.

Thanks

Jinesh Kamdar

Try this correction in ur code.

...
EXCEPTION 
  WHEN OTHERS THEN
    proc_error := SQLCODE;
    proc_errmsg := SQLERRM;
    IF (proc_errmsg = 'ORA-00001: unique constraint (DEV1NA1.NA_ACCT_ADD_PK) violated') THEN
      ROLLBACK;
    ELSE
      Na_P_Error(proc_error,
                 'na_p_delete_acct',
                 proc_errmsg ,
                 'delete '
                );
    END IF;
    RAISE;
END;

Open in new window

ASKER CERTIFIED SOLUTION

Sujith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ramitharahul

ASKER

make sense to me. good guidance...