Menu’s were the topic of week 9.
- Pop up
- Pull down
- Tabbed
Category: Adv. programming for DB apps.
FIT5059: Advanced programming for database applications
This unit is designed for students who wish to extend their programming abilities in developing relatively large database applications. An integrated system of significant size will be developed using the current industry standard software. Topics covered include the principal aspects of database development and applications, advanced queries, customising forms and professional reporting, business graphics, importing and exporting data, internet applications, debugging and error-handling security and system documentation
As it seems this course is in essence a revision of the Oracle forms builder manual, theoretical revision covered in the blog is somewhat pointless. Most of my revision time for this subject will now be dedicated to the subject’s assignment which is is an all-inclusive one that requires students to implement everything that has been taught.
Week 8 introduced tabbed canvases:
Continuation of previous lecture focusing of multiple forms vs multiple canvasses. This is a major concern for distributed development, an area I don’t think it is a strong point for Oracle forms. The source code from tutorial 7:
Custom Forms, Multiple Form Approach
Form 1: Student Form--------------------
1. Goto Tools/Layout Editor2. Create 6 text items (and labels) e.g. text_item5, text_item6, text_item7, text_item8, text_item29, text_item303. Create 2 push buttons e.g. push_button20, push_button21
4. Create a PRE-FORM trigger in the Triggers of the Form :system.message_level :=25; -- :text_item4 := :global.sid;
5. Create an Alert object a. Name: NOT_NULL_ALERT b. Message: Not null error found c. Alert Style: Stop d. Button 1 Label: OK
6. Create a program unit called DISPLAY_ALERT
PROCEDURE Display_NOTNULL_ALERT IS alert_button NUMBER;BEGIN alert_button := SHOW_ALERT('NOT_NULL_ALERT'); if alert_button = ALERT_BUTTON1 then message('Insertion Cancelled'); end if;END;
Week 6 saw an introduction to custom forms. It makes a lot more sense now why we completed the basic introductions to each component in an automated fashion now. All of the elements that we have already put into practice can be added to a window and customized. Using smart triggers in conjunction with stored procedures gives us the tools that we need to make decent database applications.
The properties palette style customization that is used in Oracle forms developer makes it very similar in feel to ASP.NET development.
More advanced error/exception handling was also discussed this week. Due to the event driven nature of Oracle’s forms, generating exceptions is a convenient way to handle the madness users subject programs to. Customizing systems message will also allow the flow of information from the system to the user to be much more decipherable.
The final topic that was introduced in preparation for next week was the juxtaposition between using multiple canvasses or multiple forms for more complex applications. While multiple forms encompasses much stronger encapsulation, message passing needs to be done using global variables D:<
On the other hand using multiple canvasses allows for much easier message passing, distributed development is decidedly more difficult.
Doing the assignment will be the best way to ensure that the processes for each element have been memorized.
PL/SQL continued in week 5 with some leaning towards integrating small PL/SQL programs into our Forms applications.
Procedures, Functions and triggers have now been added to our repertoire. An example of a simple function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE OR REPLACE FUNCTION patient_age (Current_Patient PATIENT_DETAILS.Pname%TYPE) RETURN NUMBER IS PatientDOB DATE; CurrentAge NUMBER; BEGIN SELECT PDOB INTO PatientDOB FROM Patient_Details WHERE PName = Current_Patient; CurrentAge := TRUNC((SYSDATE - PatientDOB)/365); RETURN CurrentAge; END; |
The query for running/viewing/debugging such a store procedure would be:
1 2 3 4 5 6 7 | SELECT PName, patient_age(PName) AS Agefrom Patient_Details; SELECT object_name FROM user_objects WHERE object_type='FUNCTION'; SELECT * FROM user_source WHERE name='PATIENT_AGE'; SELECT * FROM user_errors; |
David Taniar made a goof portion of source code available on moodle for student, this will assist greatly.
The component that we have covered so far are not particularly complicated. However, when implementing the number of processes we need to remember is beginning to grow. Some practical revision of work done in the tutorials will be needed.
This weeks tutorial involved the implementation of a naive library program. For me, implementing the stored procedures that we had just learnt was the easy part. Remembering how to create a button to interact with a LOV object proved more illusive. I will endevour to find some time whilst at uni to do some revision of the LOV and button objects!
Unfortunately I was absent for week 4’s lecture and tutorial. My review of the week will be limited to the printed material. PL/SQL was the topic of week 4.
Ok, so to start with I have not used PL/SQL before so it is worth defining; a procedural programming language developed by Oracle as an extension for their relational databases. It allows for complex applications to further leverage the DB layer.
The general structure of PL/SQL:
DECLARE
<variable declarations>
BEGIN
<program statements>
EXCEPTION
<error handling statements>
END;
Any oracle datatypes can be used (CHAR, VARCHAR2, NUMBER, etc).
Constructs covered in the lecture were:
- IF/THEN/ELSE
- Loops, pretest and posttest
- Cursor (implicit[select,from,where] and explicit[FOR DroomRow IN DCursor LOOP])
- Exception(pre-defined, undefined, user defined) and error handling
Triggers is an interesting topic, I have always been of the opinion that this sort of procedure should be in the application layer (aside from logging). The syntax:
CREATE OR REPLACE TRIGGER trigger_name
{BEFORE|AFTER|INSTEAD OF]}
{INSERT|UPDATE|DELETE}
[OF <attribute_name>] ON Table_name
[FOR EACH ROW] [WHEN (condition)]
BEGIN
Trigger_body
END;
I will need to work through the tutorial work to get some practice with this material!
Week 3 rolled on with Oracle form builder interface training. Although the power and ease of use that form builder presents is impressive, I think the contents of week three could be extrapolated in a simple tutorial/walkthrough. I am sure as David mentioned that the material ramps up to more complexity.
The items that were covered in the lecture:
- Introduction to commonly used form objects
- Creating list of values (LOV)
- Creating radio buttons
- Creating check boxes
- Creating static lists
- Creating display items
- Form object guidelines
Next week’s topic appears to be PL/SQL which I assume is where the ‘Advanced Programming’ in Advanced Programming for DB apps comes in >:D
Week 2 continue with the basics, however this week some of the power of Oracle forms was revealed. It seems that web based DB apps can be developed very quickly with Oracle form builder. I am very interested to see how the performance of this apps it. I assume that they will automatically leverage Oracle’s middleware and end up being faster than a well written program. However, some further reading shows that Oracle have some fairly detailed documents on improving performance. This suggests that it is not all just click and go when high volumes of users and performance becomes an issue (see: http://download.oracle.com/otn_hosted_doc/forms/forms/A73073_01.pdf and http://www.oracle.com/technetwork/developer-tools/forms/overview/technical-overview-130127.pdf).
The lecture and tutorial focussed on Data Block Forms (although the tutorial was wasted populating a DB from non-sql formatted word doc).
Note **OC4J Instance Program must be running to test forms.
The structure of forms can be defined as:
<Layout>
<Window>
<Canvas>
<block>
</block>
</Canvas>
</Window>
</Layout>
Everything except layout can be in multiples.
The lecture continued with very detailed tutorial on the use of datablocks.
The best way to learn that material will be to install Oracle Developer at home and tinker..
David Taniar presented the first lecture for DB App Dev. Again we covered the usual first week formalities.
The lecture consisted of a refresher in SQL. For this I can refer back to the FIT9019 course (http://mchost/?category_name=database-technology)
This subject will be using Oracle databases and software. Installation of Oracle Express is free and straight forward: http://www.oracle.com/technetwork/database/express-edition/overview/index.html
To access of Oracle services at Monash, connection via the Monash VPN must be completed. Instructions for this can be found here: http://www.its.monash.edu.au/staff/networks/vpn/ . I did have some issues connecting to the Oracle DBs at Monash through the VPN last year, but have been assured that it is doable.
The lecture covered all the basic SQL tools and David suggested that we could do everything that we needed in the subject with relatively simple SQL queries.
