Error message when data not inserted in oracle apex-CodePudding

I have an insert query in oracle apex, sometimes the data are not inserted because of different reasons (for example writing a text in number field). is there a way to display an error message that check if data is inserted in the database or not?

CodePudding user response：

This is how this can be done for a page process of type pl/sql. Example is on the sample dept table.

Create 2 page hidden page items: P48_SUCCESS_MESSAGE and P48_ERROR_MESSAGE
In the page process that does the update use the following code

DECLARE
  e_invalid_insert exception;
BEGIN
  INSERT INTO dept(dname, loc) VALUES (:P48_DNAME,:P48_LOC);
  :P48_SUCCESS_MESSAGE := 'Insert succesful';
EXCEPTION WHEN OTHERS THEN
  -- below code will also show database error, adjust for your requirements
  :P48_ERROR_MESSAGE := 'Insert failed:' ||SQLERRM; 
  RAISE e_invalid_insert;
END;

For the attribute "Success Message" of the page process put &P48_SUCCESS_MESSAGE., For the attribute "Error Message" of the page process put &P48_ERROR_MESSAGE.

Check the help text for "Error Message" and "Success Message" for available substitution strings.

Note, you state sometimes the data are not inserted because of different reasons (for example writing a text in number field). As a developer you can put in validations to ensure that the data is correct before the insert process is executed. If a validation fails, the insert statement will not be executed (and it should not be executed).