Display exception messages

OK when you write a PLSQL procedure or function you should trap for exceptions.

Sometimes you might want to display these exceptions to the user. In this example it is the logging into the application and displaying if the user is locked.

So here is the snipped of the PLSQL code. Do not worry about what it is doing the main line is highlighted

if v_locked = 'N' then
 v_authenticated := authenticate_password(p_username => p_username,p_password => p_password);
 return(v_authenticated);
 else

 apex_util.set_session_state('LOGIN_MESSAGE','Your account is currently locked');

 return false;

 end if;

The important part here is the LOGIN_MESSAGE this will be used later on.

 

OK now go to the APEX application and open the application in question that you want to add a message to

There are a few screen grabs to make the teach in a bit easier

Please note that LOGIN_MESSAGE is used throughout and when creating the region the source is &LOGIN_MESSAGE.

Please take note of the . after MESSAGE.

 

Create the application item first

 

Now create a region

 

Testing the application

 

There is more to come, I am just getting started. This may not be the best way to do things but it is a start šŸ™‚

 

 

 

 

 

 


Apex ORA-44004: invalid qualified SQL name error

Arrrrrggggghhhhhh!!!!!!!

 

5 hours spent trying to debug this. Ā I am starting of in the wonderful world of Apex and need a custom login.

 

So I read the books, watched the Youtube clips, all of which did not really cover Apex 4.1, but hey there can not be that much difference.

Well there is!!!!!!!!!

The code for the login I will postĀ separately but just to let you know where you need to add the function name and in what format.

My function is in a package ans is called pkg_auth.authenticate.

And here is where you put it.

Shared components –>Ā Authentication Schemes

Choose the authentication method in place and change to below

Apex_Error1

 

Note the place it is held and the format.

You place it in the Authentication Function Name and as you can see it is call pkg_auth.authenticate.

You will see many references that would indicate that you need to enter

return pkg_auth.authenticate

return pkg_auth.authenticate;

orĀ pkg_auth.authenticate;

You do not

 

Good luck with any development

 

Al

 

 

 

 

 

 

 

 


Validate multiple fields on Save

How to validate two date fields in Oracle Apex.

Into the wonderful world of Oracle Apex development, I must admit, if you know Oracle SQL and PL/SQL then this is a great application development tool.

Any way I wanted to ensure that the a date entered was greater than another date column, pretty simple šŸ™‚

The application field names are called

PERIOD_START_DATE

PERIOD_END_DATE

To create validation on the PERIOD_END_DATE field. Navigate to the field in the report columns section of the application.

Right click on the field name and choose Create Validation

Oracle Apex Create Validation

Create validation option

Once the validation screen is open there areĀ numerous types, we are going to use theĀ Ā “Function Returning Boolean” for more options and examples read up on the subject matter Understanding Validations

The documentation is OK but they do leave some bits out, go figure :).

Now we have chosen the correct type, we need to enter a Validation Expression

BEGIN
IF : PERIOD_END_DATE <= : PERIOD_START_DATE THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END;

Set always execute to Yes

Enter and appropriate error message.

The final area is the conditions.

This is when the validation will take place and on what data.

You pick the button and execution scope.

The button is self explanatory, I would set the scope to created and modified rows.