Set Up Custom Authentication

**Warning, the following example function does not make use of HASH, passwords are stored as clear text which is a security risk....this is only an example for functionality sake!

So should it have taken me all day today to set up a custom authentication system for my APEX application? I like to think not, but in reality, I wish I had known a couple of things in order to do this.

Point 1: The code for the authentication function goes into the Object Browser/Functions area.......not within the authentication page within Shared Objects.

Point 2: On the Shared Objects, Authentication Scheme page, all that goes in there is a call to the function: return check_login; or whatever your function is called.

Point 3: APEX adds in code to your function. You have to annotate your variables in the wizzard, and then you only insert the code between the BEGIN and END statements in your code. Also, in contrast to the ORACLE HTML DB handbook page 380, you must declare your cursor as a nunber...I had some trouble in translating the book style with the demonstration application into my own needs. The following ended up being my code for authentication:

create or replace function "KEY_LOGIN"
(p_username in VARCHAR2,
p_password in VARCHAR2)
return BOOLEAN
is
result_rows number;
begin
SELECT COUNT(*) INTO result_rows FROM personnel WHERE NATO_ID=p_username AND PASSWORD=p_password;
IF result_rows > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
end;

All in all, between the auto program code and the books and the demonstration applications, this turned out to be pretty confusing. I hope this discombublated information helps someone out there.

Helpful PL/SQL Expressions for Form Validation

Here are a few form validation functions that I am finding helpful:

Email Checking:
instr(:p2_per_email,'@',1,2) <= 2 (this expression checks for only one @symbol)
REGEXP_LIKE(:p2_per_email,'.int') (this expression returns true or false if the .int string is found in the string variable).


Text input checking
to_char(:p2_per_rank) != 'CAPT, MAJ'
(this expressions checks to make sure that the string matches one of the forms provided...CAPT, MAJ)

Passing Session Variables in a URL

When attempting to reference another page in your application with a URL link, it is important to know how to use APEX formatting. Here is an example:

http://apex.oracle.com/pls/apex/f?p=4350:1:220883407765693447

We will look at this starting with the pls and apex words. Basically, these two terms reference the apex application server, and are fairly common syntax for links.

f?p=4350:1:220883407765693447

The number 4350 represents the application id number, the number 1 represents the page number that will be referenced. The long number at the end of the link is the session id number. So, overall, a link will follow the following format:

http://apex.oracle.com/pls/apex/f?p=App:Page:Session

Other terms can be added in on the end, but I will not go into those at this point in time.

Now, if you want to develop dynamic links, the issue of setting the application,session id becomes an issue. Here is a little syntax to deal with that:

APP_SESSION.

Enter this term in place of the session ID number to take care of the dynamic data situation. The period at the end of the term is important, the link will not work without it.

If you want to make the application number dynamic, use the terminology:

APP_ID.

Again, the period needs to be at the end of the statement. So, overall, to make a totally dynamic url request, the following would be the pattern:

http://www.apex.com/pls/apex/f?p=&APP_ID.:1:&APP_SESSION.

&APP_ID will be the dynamic application id, &app_session will be the dynamic session variable.
Here is a helpful link.


Hope this helps.

Cheers
Jason

Link Javascript to Calendar Date

Greetings Yall......

Well, of course the head honcho at work desires a pop-up box when he clicks on a calendar date. This took a little figuering yesterday.....but I can say that I finally figured this one out after putting together a few different blog and forum postings.

First off, to link anything to a date in a calendar, insert the link into the calendar day link field on the Calendar Attributes page of the Region Definition section. What to insert in that section....well if you just want to link to another page in the application, it is pretty straight forward.....
1. Select "Page in this application"
2. Select the page number
3. Set the variables, if any, that you want to set from this page, into the next page.
4. Click Apply Settings.

However, should you have a handy javascript that you want to use, your link procedures will change. In this case do the following within the Day Link Field of the Calendar Attributes Page:
1. Select "URL" to link to
2. You will see all of the text fields go gray except the URL field at the bottom.
3. In this field, just type in the javascript call...for example.....javascript:callMyPopup()....without any other formatting or html.

As usual, be sure that the HTML Header section of the page contains your javascript code, included in the normal javascript formatting.

That should work to link the days in your calendar to a javascript.

Cheers and have a great weekend and holiday (if you are in Germany).

Jason

APEX + PL/SQL + HTML + JAVASCRIPT (Part 2)

OK, the second half of this problem....I am not sure if I have a bad Application Express program or something somewhere in the C drive of my computer......but, through all of the onMouseover tutorials and application downloads, I could not get any to work out correctly. For some reason the "BODY HEADER" section on my HTML regions would not put the javascript onMouseover graphic on the cursor...it would automatically go to the upper left corner of the application......I used the following tutorials and had the same result:
APEX Javascript Documentation:
http://download.oracle.com/docs/cd/B32472_01/doc/appdev.300/b32469/javascript.htm#CIHEBFFC
Aria Employee Look Up Application:
http://www.oracle.com/technology/products/database/application_express/packaged_apps/packaged_apps.html#PEOPLE

Neither of these would work, so I went to something more user friendly...Patrick Wolf's blog post for onMouseOver calls....a very simple and straight forward command to be used, and it worked:

http://inside-apex.blogspot.com/2007/08/using-tooltips-in-oracle-apex.html

Like I said, this worked, but I could not get it to work for dynamic PL/SQL content. So, after much nashing of teeth, searching of the Oracle Forum's and Googling like mad....one week later and a few Grimbergers later....I finally found a solution that would allow me to put:
DYNAMIC PL/SQL INFORMATION INTO A JAVASCRIPT TOOLTIP THAT SHOWS IN AN HTML TABLE CELL when the mouse moves over it.....here is the skinny:

First, link up your javascript in the HTML header output using this:
htp.p('<script language="javascript" src="#WORKSPACE_IMAGES#apexwonder_tooltip.js"></script>');

Second, put the call to the javascript and the dynamic data (capframe) into the table cell using this:

htp.tableData( ' ', cattributes => ' bgcolor=#990066 onmouseover="Tip('''||capframe||''')"');

CATTRIBUTES is something that I still need to get to know. It looks like it has some powerful HTML output functionality. "TIP" is the call to the javascript function. The term "capframe" is the variable holding the dynamic data to put in the tooltip itself. The three single quotes and the concatenation operators are very important to note! It is also hard to see the apostrophes on the right side at the end, but that is a double quote and a single quote, not three single quotes:)

So there we have it, a dynamic tooltip for use in a PL/SQL region.

Here is a picture...please forgive the lack of nice CSS formatting....

APEX + PL/SQL + HTML + JAVASCRIPT = Mas Confusion (PART 1)

I don't know if I am the only one who has had this problem, but here it is:

How do you call a JAVASCRIPT through HTML using PLSQL in an APEX application? I have the answer......or another answer I should say......

HTP.P('')

did not work......

Setting Up Oracle Business Intelligence (BI) Publisher

Well, it appears that if you want nice looking reports from APEX, you need a little dingy called Oracle BI (Business Intelligence). This program helps you to deal with building interfaces for report printing. To do this, you need to download the latest version of Oracle BI Publisher from the Oracle Website. After this, you will need to install it per the instructions. Next, you will need to log into APEX to set it up. This is an important point, you must log in with the ADMIN username in order to get to the right areas for setting up the program. So, you must be in the ADMIN configuration to set up BI within APEX. Spent 2 hours finding this one out tonight. You also must know that in order to log on in this configuration, you will need to go to the following page in your application express system:

http://localhost:8080/apex/apex_admin

Cheers
Jason