SQL*Forms Technical Tips

Do you find postings like this helpful ? Please let us know.

	This paper describes common methods how to develop
	applications with SQL*Forms V3 and SQL*Menu V5.
	Hope You find it useful.
	Author: Timo Siniranta / ORACLE Finland Oy
	Revised: 07.11.91/Timo Siniranta

1)	Plan the use of 'common' triggers and what should be the
	naming standard of these. When creating that 'library'
	form, remember, that you must create a 'dummy' block
	to get the .inp file (And the file is needed if you are
	going to transfer the application to another platform).

	Try to create those 'common' triggers so, that every application
	can use them. Return the set of columns which is a UNION of
	columns needed in the different applications. Always use
	parameters. For example, a procedure which gets a contacts name,
	id and phone should be made like
	procedure get_contact (p_id number in out, p_name char in out,...) is
	declare cursor c1 is select ... where id=p_id;
		cursor c2 is select ... where name=p_name;
	begin
	if p_id is not null then 
	  open c1;fetch c1;close c1;
	elsif p_name is not null then 
	  open c2;fetch c2;close c2;  
	etc.

	Now the same procedure can be used when a contact's name is known
	and when a contact's id is known.

	Return your own error number in a parameter, for translation 
	purposes.

2)	Beware that exception is not executed when a select returns


	> 1 row, the next example is INVALID: (The block tries to


	see if there are any emps left before deleting the dept).

	declare wx char(1);
	cursor c1 is select 'x' from emp where deptno=:dept.deptno;
	begin
	open c1; fetch c1 into wx;
	exception
	 when no_data_found then null;
	 when others then message('This dept has emps, delete them first');
	   raise form_trigger_failure;
	 end;
	end;

	The message never appears because the exception is NOT executed
	when the cursor fetches > 0 rows. The CORRECT block follows

	declare wx char(1);
	cursor c1 is select 'x' from emp where deptno=:dept.deptno;
	begin
	open c1; fetch c1 into wx;
	message('This dept has emps, delete them first');
	raise form_trigger_failure;
	exception
	 when no_data_found then null;
	 end;
	end;

3)	Altough SQL*Forms (char mode) aligns the LOV pop-ups so, that
	if you create a lov with x-position 80, y-pos 24, and the width of the
	window is 25 it is aligned at the lower right corner to the screen.
	BUT, IN WINDOW ENVIRONMENTS IT IS NOT. So assign 'right values'
	to those x/y positions every time. 

4)	Do NOT use anchor_view/move_view without a check if the user has
	moved the window into another place. 
	This is for window environments.

5)	Remember to check form_failure and raise after EVERY execute_trigger.
	(In V2 the calling trigger failed automatically).
	The check is NOT needed after you execute a PROCEDURE.

6)	In V2 trigger like select ... where :b1.field1 <> 'X'
	and the field1's value was NULL the select DID fetch a record.
	In V3 you must use select ... where NVL(:b1.field1,'Y') <> 'X'.

7)	When You are converting V2 triggers to V3 format, use 'Disable
	trigger compilation' because in the middle of conversion you
	might have a trigger that does not compile. The method I used
	when the old trigger had many steps was:
	go to the first step. <Cut> the whole step. Delete the step.
	Go to the next step. <Paste>. <Cut> the whole step. Delete the
	step. Go to the next step etc. Then change the style to V3 
	and <Paste> everything. Then edit it.
	In 3.0.16 you must also paste the text back to every step
	but it will disappear when you change the trigger type to V3.

8)	Remember that the on-validate-field fires also when the field is
	null/changed to null.

9)	Remember that when the user has created 3 new rows, the
	cursor is in the last one and the user presses <Commit>, in the
	pre-insert trigger in that block the system variables
	system.cursor_record is 3 for ALL the records and
	system.trigger_record is 1,2,3 for the records 1,2 and 3.

10)	The example of the syntax for default_value (for a global
	variable x) is: default_value('X','global.x'). The documentation
	is not clear in this.

11)	Place the field in every page in the upper left corner
	and make 'room' for the menu bar having all the fields in one 
	page requiring max 22 lines. If the page is marked
	as a pop-up, the menu is shown automatically when the form
	is started.

12)	When using SQL*Menu V5 and SQL*Forms V3 and you want to 'mix'
	the SQL*Forms menu (with next block, query etc.) and the 
	application menu (SQL*Menu menu), do NOT use command type
	4 (RUNFORM), because, if a form is active, and a trigger
	invokes SQL*Menu menu, when the user starts a new form,
	an error 'Cannot call linked-in SQL*Forms in SQL*Forms'
	is invoked.

	Instead, use a command type 7 (PL/SQL) procedure 
	procedure run_form30 (form_name char) is 
	begin
	if length(:system.current_form) > 0 then
	   new_form('$LOGICAL_NAME/'||form_name);
	else
	   os_command('(type=4) runform30 $LOGICAL_NAME/'||form_name
		     ||' &un/&pw &tt');
	end if;
	end;

	and in every menu option use
	run_form30('offcomp');
	That will execute a command type 4 when no form is active,
	and a 'new_form' when a form is active.
	This will also ease the work when porting the application
	into a different hardware, ie. translating the logical names.

	The same applies to SQL*Plus (we use run_plus('module')) and
	Operating system scripts (we use run_os('module')).

	In every form we use a 'standard' key-startup trigger, which
	invokes a procedure 'startup', which is only referenced in
	the forms. We use a global.menu variable, when the value is
	'F' the forms menu is active, when 'M' the Menu menu is active.
	We use the <block menu> key to toggle menus (forms / menu).

13)	The SQL*Menu does NOT get the password in the
	&PW variable when the menu is started using 
	runmenu ... /. 

	This prevents the use of command lines
	runrep $LOGICAL/report_name userid=&un/&pw 
	and you must use a type 7 procedure instead.
	procedure run_rep (report_name char) is
	begin
	 if :pw is null then
	   os_command('(type=2) runrep $LOGICAL_NAME/'||report_name
		     ||' userid=/');
	 else
	   os_command('(type=2) runrep $LOGICAL_NAME/'||report_name
		     ||' userid=&un/&pw');
	 end if;
        end;

	and the corresponding menu command line is
	run_rep('report_name');

14)	Note that the := assignment fires the validation,
	for example if you have a form with a default block
	against EMP table and PRE-QUERY trigger (in block level)
	to ensure that either an EMPNO or an ENAME is entered
	before executing the query like:

	if :b1.empno is not null or :b1.ename is not null then 
	   null;
	else 
	   message('Either ENAME or EMPNO must be filled before query.');
	   raise form_trigger_failure;
	end if;

	However, if the user enters '>2' into the EMPNO field 
	(in enter query mode) and presses <execute query>, an error 
	FRM-50016: Legal characters are 0-9 - + E . is raised. 
	That is because:
	PLSQL performs type-checking on the variables when they 
	are referened if they are in need of validation. Hence, 
	using a number field in a expression at PRE-QUERY time, 
	might mean that the number field momentarily contains 
	non-numeric characters like the ones you speak of.
	In order to treat the values without regard to what 
	special characters they might contain, use the NAME_IN function:
	if NAME_IN('b1.empno') is null or ... then.

15)	Remember that every 'select ... into ... ' does TWO fetches
	(to find out TOO_MANY_ROWS exception), the better way is
	always to declare a cursor and open/fetch.

16)	Remember that almost all SQL functions are available in PL/SQL
	'itself', no need to 'select substr(:field,1,1) into :field2
	from dual' - all you need is :field2:=substr(:field1,1,1);
	Decode function is not available in PL/SQL - use if-then-else.
	(In the PL/SQL User's Guide and reference 0691 the decode
	function is listed - but it is a documentation bug).

17)	Assignments like a:=sysdate or b:=user generate a select ...
	from dual - a better way is to get those once per application
	startup (maybe in the run_form30 procedure) and store the values
	in global variables. At least get both in same query, select
	sysdate,user into :a,:b from sys.dual.

18)	When deciding whether to convert a V2 trigger into a V3 trigger,
	if there is NO complex logic in the V2 trigger, NO "select ...
	from dual ..." - its better to leave it in V2 mode. 
	"If it's not broken, don't fix it".

19)	If you have big tables (>200rows) beware that a LOV statement
	reads ALL the rows and scans for the desired ones. A better
	way is to create a LOV sql statement like
	select last_name, first_name, contact_id 
	into :block.last_name,:block.forst_name,:block.contact_id
	from contact
	where last_name like :block.last_name||'%' 
	  and first_name like :block.first_name||'%'
	 order by last_name,first_name
	This *will* use indexes on last_name and is MUCH faster
	if the user enters something in last_name field before
	pressing <List>. The same LOV statement can be copied
	into the fist_name field.

20)	In PL/SQL a user must have SELECT privileges to a table
	before he/she can do inserts/updates/deletes. This is 
	required because PL/SQL does an implicit 'Select * from 
	<table>' to find out the column names/types/lengths etc.
	PL/SQL will NOT execute that statement, it only parses
	that and gets the information from RDBMS. 

• Dan

Daniel Druker
Senior Consultant
Oracle Corporation                    

| Dan Druker                    |  work 415.541.5530     fax  415.541.0283    |
| oracle*mail ddruker.us1       |  internet: ddruker_at_us.oracle.com            |
-------------------------------------------------------------------------------

Disclaimer: These are my opinions and mine alone, and don't reflect the views or position of my employer. Received on Sun May 17 1992 - 10:48:55 CEST