sql nosql

SQL/NoSql/Oracle >> Oracle Form >> Oracle Form Development

Oracle Form Development

By ukmodak | March 31st 2024 10:32:28 AM | viewed 508 times

Oracle Forms Object Navigator

File menu has options to create, open, save and print forms. This menu also includes an option to connect to the database and the Administration option that is used to compile forms and menu code. The last option on the File menu is the Exit option that will exit Oracle Forms Builder.
Edit menu has options to cut, copy and paste objects, to invoke an editor and to undo an operation.
View menu toggles the object navigator display between visual elements and ownership elements.
The Layoutmenu has items that control objects on a form much in the way a drawing package does. Objects can be resized, moved, painted and otherwise manipulated using items on this menu.
The Program menu has items that control form compilation, generation and execution (Run). Triggers, Procedures and other PL/SQL code can also be created from this menu. The Smart Triggers menu option displays the most commonly used triggers according to the type of object currently selected in the object navigator.
The Debug menu has options for debugging forms as they are running. It includes menu items to establish break points and to step over the execution of code one line at a time.
The Tools menu has options to switch between several different screens including the Layout editor, Menu editor and Object Navigator. There are also several wizards that can be launched from this menu including the Data Block wizard, the Layout wizard, and the LOV (List of Values) wizard.
The Windows menu displays menu items for each of the windows you have open. By default, only one item – the Object Navigator – is displayed.

The content of the Object Navigator is broken down into 6 major sections:

Forms(default 0)– (or data entry form) acts like a window into the database schema and Contains form objects and all of their sub-objects such as
1. data blocks(two type one is used data block wozard whis is related to database another is build a nes data block(control block) is not related to database)
2. triggers
3. alerts
4. canvases
5. program units (procedures and functions)
6. parameters
7. windows
Menus(default 0)– Contains menu objects and all of their sub-objects such as
1. menu items
2. parameters
3. program units
PL/SQL Libraries(default 0)– These are libraries of PL/SQL code (stored either in the database or in .plb files). Libraries can be shared among different forms and among different developers.
Object Libraries(default 0)These are libraries containing a collection of objects (anything that can appear on a form).
Built-in Packages(default 17 package)– Lists all of the packages (PL/SQL procedures) available by default to all forms, reports, etc.
Database Objects(default 0)– Lists all of the database objects (Tables, Views, PL/SQL Libraries, and Stored Program Units) in the user’s Oracle database schema.

How to create a new block with in a form

Step-1

To create a new block, pull down the Tools menu ->select the Data Block wizard menu item Or select Block-> + -> check display this page next time ->next

Step-2

click on table or view /store procedure ->next

Step-3

Select table or view by browsing and select assential column ->next

Step-4

Select table or view by browsing and select assential column ->next

Step-5

Enter the block name ->next

Step-6

select the create data Block and call the layout wizard / just create data block ->finish

Step-7(to show table column on layout)

select column name ->finish

Step-8

select form/tabular ->next

Step-9

select frame title + Record Displayed+ distance between record + display scrollable ->finish

Canvas/layout editor

To view the actual form and its blocks and fields, pull down the Tools menu and select the Layout Editor menu item.

In the Layout Editor, fields and labels can be moved around by clicking and dragging. Other text, lines and boxes, etc. can be added using the tool palette on the left side of the window.

Some common operations on the Layout Editor on the left include:

Button,Text,TextItem.....

Displaying Properties of the layout/canvas

While in the Layout Editor, double clicking on an object will bring up that object’s properties. Optionally, clicking on an object with the right mouse button will bring up a small menu which has the Properties menu item on it. The following figure shows the properties for the FNAME item.

Some common elements of Properties

Note: each block has its own properties

General
1. Name
2. Type
3. Subclass Information
4. Comments
5. Help Book Topic
Functional
1. Enable
2. Justification
3. Implementation Class
4. Multi line
5. Case Restriction
6. Conceal Data
7. Keep Cursor Position
8. Automatic Skip
9. Popup Menu
Nevigation
1. Keyboard Nevigation
2. Previous Nevigation Item
3. Next Nevigation Item
Data
1. Data Type
2. Data Length Semantic
3. Initial Value
4. Required
5. Maximum Length
6. Format Mast
7. Lowest Allowed Value
8. Highest Allowed Value
9. Copy Value from Item
10. Syncronize with item
Calculation
1. Calculation Mode
2. Formula
3. Sumarized Block
4. Sumarized Item
Records
1. Current Record Visual Attribute Group
2. Distance Between Records
3. Number of item display
Database
1. Database Item
2. Column Name
3. Primary Key
4. Query Only
5. Query Allowed
6. Query Length
7. Case Insensative Query
8. Insert Allowed
9. Update only if null
10. Lock Record
List of values(LOV)
1. List Of Values
2. List x position
3. List y position
4. Validate from list
Editor
1. Editor
2. Editor x position
3. Editor y position
physical
1. Visible
2. Canvas
3. Tab Page
4. X position
5. Y position
6. Width
7. Height
8. Bevel
9. Rendered
10. Show vertical scrollbar
Visual Attribute
1. Visual Attribute Group
2. Prompt Visual Attribute
Color
1. Foreground Color
2. Background Color
3. Fill Pattern
Font
1. Font Name
2. Font Size
3. Font Weight
4. Font Style
5. Font Spacing
Prompt
1. Prompt
2. Prompt Display Style
3. Prompt Justification
4. Prompt Attached Adge
5. Prompt Allignment
6. Prompt Attached Offset
7. Prompt Allignment Offset
8. Prompt Reading Order
Prompt Color
1. Prompt Foreground Color
Prompt Font
1. Font Name
2. Font Size
3. Font Weight
4. Font Style
5. Font Spacing
Help
International
1. Initial keyboard State
2. Reading Order
3. Kayboard Status

Block and Item

Follow the link Block and Item

Master detail

Follow the link Master detail

Form builder using text item

Follow the link Form builder using text item

Radio group and change radio

Follow the link Radio group and change radio

Form builder using list item

Follow the link Form builder using list item

Form builder using checkbox

Follow the link Form builder using checkbox

Push button and trigger

Follow the link Push button and trigger

Form built-in-trigger list

INSERT_RECORD;
PREVIOUS_RECORD;
NEXT_RECORD;
LAST_RECORDS;
FAST_RECORDS;
EXIT_FORM;
CLEAR_RECORD;
DELETE_RECORD;
CREATE_RECORD;
CLEAR_FORM;
COMMIT;
COMMIT_FORM - permanently Save Data in the table.
UPDATE_FORM;
TEXT_ITEM;
CLEAR_ITEM;
EXECUTE_QUERY;
ENTER_QUERY;
OPEN_FORM(PATH OF FMX FILE)- nevigate between form
CALL_FORM(PATH OF FMX FILE)- one the new form on the old one
NEW_FORM(PATH OF FMX FILE)- open the new form and close the old form
GO_FORM(PATH OF FMX FILE)- if the form is already open then go to form,if not then open the form
x:=SHOW_ALERT('name of alert')- where x is the number type variable in declare section
x:=SHOW_LOV('name of lov')- where x is boolean type variable in declare section
GO_BLOCK('block name')- activate the block name

Form built-in-function list

MESSAGE('message')
recordgroup;
find_group('name')
CREATE_GROUP_FROM_QUERY('')
Populate_group(rg_id1)
populate_list('PROGID',rg_id1);
Forms_DDL('Commit')--just sends a command to the database, so nothing is done inside the Forms module.
set_window_property(FORMS_MDI_WINDOW,WINDOW_STATE,MAXIMIZE);
SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW, WINDOW_STATE, MAXIMIZE);
SET_CANVAS_PROPERTY('', WIDTH, GET_WINDOW_PROPERTY(FORMS_MDI_WINDOW, WIDTH) - 33);
SET_WINDOW_PROPERTY('', WIDTH, GET_WINDOW_PROPERTY(FORMS_MDI_WINDOW, WIDTH) - 33);
SET_CANVAS_PROPERTY('', HEIGHT, GET_WINDOW_PROPERTY(FORMS_MDI_WINDOW, HEIGHT) - 131);
SET_WINDOW_PROPERTY('', HEIGHT, GET_WINDOW_PROPERTY(FORMS_MDI_WINDOW, HEIGHT) - 131);
Get_Window_Property(FORMS_MDI_WINDOW, Width)<=900
SET_BLOCK_PROPERTY('HRM_ATTENDANCE_REGISTER_DAILY',DEFAULT_WHERE,V_WHERE);
Set_Item_Property('DATE.D'||X, Visual_Attribute, '')
hide_view('report_canvas')-- hide canvas
show_view('report_canvas') - show canvas
GO_ITEM('NDB_PASWORD.STOCK_TAKING_PASWORD')--after query execute then go to item list
SYS_CONTEXT ('DYN_CTX', 'P_EMP_ID')) --Returns the number being used in the System Global Area by the globally accessed contextshow tutorial
query_block('projects') -- a block which is execute row query
Copy(I, 'DATE.D'||(I+V_Start-1))- To copy values from one form to another form
generate_list('','')--
get_application_property()--Returns information about the current Form Builder application. You must call the built-in once for each
DISPLAY_ITEM('V_ATTENDANCE.IN_TIME','VA_LEAVE');
SET_MENU_ITEM_PROPERTY()
SET_ITEM_INSTANCE_PROPERTY('PROD_DAYS.txt_month',CURRENT_RECORD,VISUAL_ATTRIBUTE,'off2');

oracle built-in function

Control block and Image(To call or insert image)

 Read_image_file('url','image-type','image_item');

Follow the link Control block and Image

Display Item(Difference between summary and formula)

 formula: can use addition,subtractiob,multiplition,division 
 summary: can use any one of min.max,count,avg of an item

Follow the link Display Item

Format Mask

is used for validating date,mobile, phone number,....

Follow the link Format Mask

Email id validation code

is used for validating date,mobile, phone number,....

code...

Follow the link Email id validation

Tab Canvas

Manage Tab and it's page

Follow the link Tab Canvas

Menu and popup menu

Follow the link Menu and popup menu

Follow the link menu tutorial

Follow the link tree menu tutorial VVI

DECLARE     -- tree menu 
htree   ITEM;
v_ignore NUMBER;
rg_emps recordgroup;
BEGIN
htree := find_item('tree_block_name.item_name');
rg_emps :=CREATE_GROUP_FROM_QUERY('rg_emps','select 1,lavel,last_name,null,to_char(employee_id)'
          ||'from_employees'
		  ||'connect by prior employee_id=manager_id'
		  ||'start width job_id =''ADD_PRESS'''
		 );
v_ignore :=Populate_Group(rg_emps);
Ftree.Set_Tree_property(htree,Ftree.RECORD_GROUP,rg_emps);
END;

Auto primary key generation

 DECLARE
 a NUMBER;
 BEGIN
   SELECT MAX(ID) INTO a FROM TABLE;
   :block_one.primary_id := a+1;
 END;

Follow the link Auto primary key

Visual Attribute

Follow the link Visual Attribute

Alert

DECLARE
p_value   NUMBER;
BEGIN
p_value := 120;

SET_ALERT_BUTTON_PROPERTY (‘alert_name’, alert_button1, label, ‘Yes’);
SET_ALERT_BUTTON_PROPERTY (‘alert_name’, alert_button2, label, ‘No’);
SET_ALERT_PROPERTY (‘alert_name’,alert_message_text,‘Do you want continue value Exceeds’ || p_value || '?');

n := SHOW_ALERT (‘alert_name’);
	IF n = alert_button1 THEN
	 :sel_button := ‘You choose Yes to Continue’;
	ELSE
	:sel_button := ‘You choose NO to Stop’;
	END IF;
END;

Follow the link Alert

set_item_property

Follow the link set_item_property

login validation 1

Follow the link login validation 1

change password

Follow the link change password

Post Query

Follow the link Post Query

Dynamic List Item

create a procedure on program unit as dynamic_list_item and call on when-new-form_instance trigger

PROCEDURE dynamic_list_item IS
rg_name1 VARCHAR2(20) :='PRONAM';    -- field name corrending to progid
rg_id1 recordgroup;
n1 NUMBER;

BEGIN
rg_id1 := find_group(rg_name1);
IF ID_NULL(rg_id1) THEN
rg_id1 := CREATE_GROUP_FROM_QUERY('rg_name1','SELECT PRONAME,TO_CHAR(PROGID) FROM PROGRAM');
END IF;
n1 := Populate_group(rg_id1);
populate_list('PROGID',rg_id1);   -- show list  on progid field
END;

Dynamic List Item

Login validation 2

Follow the link Login validation 2

Standared Commit

user when use control block

Follow the link Standared Commit

Add Listitem Runtime

we can add item on the list table on the instance

add_list_element();

Follow the link Add Listitem Runtime

Editor

Follow the link Editor

LOV

list of record on a separate window show

Follow the link LOV

Date

Follow the link Date

Search

Follow the link Search

Follow the link Search 2

Image Upload

Follow the link Image Upload

How to Run oracle form in Mozilla or Any browser

Follow the link How to Run oracle form in Mozilla or Any browser

Where clause

Follow the link Where clause

Report

DECLARE
report_server VARCHAR2(50);
format VARCHAR2(50);
login_user VARCHAR2(50);
report_path VARCHAR2(50);
BEGIN
report_server :='http://SDI-Uzzal:8889/reports/rwservlet?';
format :='&desformat=pdf&destype=cache';
login_user :='&userid=dalerp/dalfactoryerp@ukapextest';
report_path :='&report=D:\hrmdemo\shift.rdf';
web.show_document(report_server||login_user||format||report_path,'_blank');
END;

go to /devsuit/reports/conf/rwservlet.properties and replace the following

#RELOAD_KEYMAP=NO as  RELOAD_KEYMAP=YES

go to /devsuit/reports/conf/cgicmd.dat and replace the following

ukrpt:[space here]userid=dalerp/dalfactoryerp@ukapextest[space here]%*

DECLARE
report_server VARCHAR2(50);
format VARCHAR2(50);
login_user VARCHAR2(50);
report_path VARCHAR2(50);
BEGIN
report_server :='http://SDI-Uzzal:8889/reports/rwservlet?ukrpt';
format :='&desformat=pdf&destype=cache';
report_path :='&report=D:\hrmdemo\shift.rdf';
web.show_document(report_server||format||report_path,'_blank');
END;

Excel Report Code

DECLARE
 application ole2.obj_type;
 workbooks ole2.obj_type;
 workbook ole2.obj_type;
 worksheets ole2.obj_type;
 worksheet ole2.obj_type;
 cell ole2.obj_type;
 arglist ole2.list_type;
 row_num NUMBER;
 col_num NUMBER;
 fontObj ole2.obj_type;
 
 CURSOR rec is SELECT shift_id,shift_name FROM HRM_SHIFT;
 
 PROCEDURE set_cell_value(rowid NUMBER,colid NUMBER,cellvalue VARCHAR2) is
    BEGIN
	   arglist := ole2.create_arglist;
	   ole2.add_arg(arglist,rowid);
	   ole2.add_arg(arglist,colid);
	   cell := ole2.get_obj_property(worksheet,'Cells',arglist);
	   fontObj := ole2.get_obj_property(cell,'Font');
	   ole2.destroy_arg_list(arglist);
	   ole2.set_property(cell,'value',cellvalue);
	   ole2.set_property(fontObj,'Size',16);
	   ole2.set_property(fontObj,'Bold',1);
	   ole2.set_property(fontObj,'ColorIndex',7);
	   ole2.release_obj(cell);
	END;
 end set_cell_value;
 
 PROCEDURE app_init is
    BEGIN
	   application := ole2.create_obj('Excel.Application');
	   ole2.set_property(application,'Visible',true);
	   workbooks := ole2.get_obj_property(application,'workbooks');
	   workbook := ole2.invoke_obj(workbooks,'add');
	   worksheets := ole2.get_obj_property(application,'worksheets');
	   worksheet := ole2.invoke_obj(worksheets,'add');
	   ole2.set_property(worksheet,'Name',' Shift Information');
	   
	END;
 end app_init;
 
 PROCEDURE save_excel(path VARCHAR2,filename VARCHAR2) is
    BEGIN
	   OLE2.Release_Obj(worksheet);
	   OLE2.Release_Obj(worksheets);
	   
	   IF path IS NOT NULL THEN
	    Arglist := OLE2.Create_Arglist;
		OLE2.Add_Arg(Arglist,path||'\'||filename.'.xls');
		OLE2.Invoke(workbook,'SaveAs',Arglist);
		OLE2.Destroy_Arglist(Arglist);
	   END IF;
	   
	END;
 end app_init;
 
BEGIN
  app_init;
  row_num :=1;
  col_num :=1;
  SetCellValue(row_num,col_num,'shift_id');
  col_num := col_num+1;
  SetCellValue(row_num,col_num,'shift_name');
  
  for i IN rec LOOP 
    row_num := row_num+1;
	col_num :=1;
	SetCellValue(row_num,col_num,i.shift_id);
	col_num :=2;
	SetCellValue(row_num,col_num,i.shift_name);
  END LOOP;
	
	save_excel('d:\excel_report','shift_info');
	OLE2.Release_Obj(workbook);
	OLE2.Release_Obj(workbooks);
	OLE2.Release_Obj(application);
	
END;

Latest Tutorial