[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 649
  • Last Modified:

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 ???
0
ramitharahul
Asked:
ramitharahul
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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)?
0
 
ramitharahulAuthor Commented:
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;
0
 
ramitharahulAuthor Commented:
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Jinesh KamdarCommented:
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

0
 
sujith80Commented:
Assuming that above shown is the full code that you are trying to execute;
You dont need a ROLLBACK in the exception handler, as the record itself didnt get inserted because of the constraint violation.
Is it necessary to RAISE the exception again?

Your code can be re-written as below:
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
   IF sqlcode <> -1 then
      Na_P_Error( sqlcode ,
                 'na_p_delete_acct',
                 sqlerrm ,
                 'delete '
                );
      RAISE;
    END IF;
END;

Open in new window

0
 
ramitharahulAuthor Commented:
make sense to me. good guidance...
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now