Editor in Oracle Developer 2000/d2k/forms

Oracle forms/Oracle Reports/Oracle /Oracle D2k/Oracle 2000 /Oracle 10G
Editor :- (Cnt+e)

1. this is used to show an editor user to enter information in a more comfortable way, used for fields like, address, comments, name etc
2. there are 3 type of editor
• default editor
• system defined editor
• user defined editor


DEFAULT EDITOR :- every text box is by default associated with a default editor

SYSTEM EDITOR :- in the property of text item we can use ‘system editor’ to show system editor at run time.
( who notepad/textpat/wordpad depending on what is editor that is associated with oracle in the file init.ora)

User defined editor :-

1. we can create our own editor and show it to user as per our requirement using a built in procedure i.e show_editor

Syntax

Show editor (‘name of editor’,message_in,x_pos,y-pos,message_out,Boolean_val,out parameter);

Out parameter will return true if user clicked ok on editor and false if user cancelled the
Editor.

Various ways to used editor at run time.

1. on the text itme.
2. edit menu :- edit on the text item.
3. Built in procedure:- ‘edit –texttem’ in the triggers
4. show_editor

Steps for create an editor.
1. create an editor
2. Make changes in the property of editor as per requirement.
3. Show editor using show editor/others ways at run time.





Code for editor button (when-button-pressed) of job field of emp block :-

Declare
B Boolean;
V_job varchar2(4000);
Begin
Show_editor
(‘ed_job’,:emp.job,100,100,v_job,b);
If b then
If length(v_job)>9 then
Message(‘you can enter 9 letter only’);
Message(‘’);
Raise form_trigger_failure;
Else
:emp.job := v_job;
End if;
Else
Message(‘cancelled’);
Message(‘’);
End if ;
End;

Trigger in Oracle 10g

Trigger in Pl-sql/oracle/Oracle 10g forms and reports

1. it is a db object which is a collection of pl/sql codes and it automatically get executed whenever any dml event happens provided trigger has been written for that DML event.
2. Trigger has three section i.e. events, restrictions and when condition (optional.
3. Pl sql trigger is divided into two parts. 1. Row level 2. Statement level.


Row Level Statement Trigger.
1. Row level trigger is executed for each and every row affected by DML stmt.
2. we need to use for each row for row level trigger.
3. used in those cases where we need to depend on the values of every record of any column of any table.
4. eg. Any name begin inserted must be in letter capital.

Statement Level Trigger.

1. Statement level trigger is executed only once irrespective of No. of records affected by the dml statement.
2. Every trigger is by default STMT level trigger.
3. Used for restrictions like No Deletion allowed on Sunday on emp table.

Syntax.

CREATE [ OR REPLACE]
TRIGGER

BEFORE | AFTER
INSERT [ OR DELETE]
OF (OPTIONAL)
ON FOR EACH ROW (OPTIONAL) REFRENCING NEW AS OLD AS WHEN () (OPTIONAL) Note. 1. to follow any value of any col. Of any record of any table within the execution section of a trigger always used :old.and :new.2. Theses two can be followed only with in a row level trigger. :new. Refers to new values or that col for that row (Applicable for updated and insert) :old.Refers to old values for that col for that record (Applicable for both delete and update) Example:- Create or replace Trigger tr_ex Before delete On insert or update On emp For each row Declare V_msg varchar2(200) Begin If inserting then V_msg := ‘inserting….’ Elsif deleting then V_msg := ‘deleting………….’; Elsif updating then V_msg := ‘deleting…’; End if; Dbms_output.put_line(V_msg); End; Q. Create a trigger which will automatic make the first letter of every name inserted | updated into capital letter irrespective of any case enter by user. Create or replace trigger Tr_name Before update or insert Of ename On emp For each row Begin Dbms_output.put_line(‘before change name = ‘ ||.new.ename); End; / Example of Statement level trigger. CREATE OR REPLACE TRIGGER TR_DELETE BEFORE DELTE ON EMP BEGIN IF TRIM(TO_CHAR(SYSDATE,’DAY’)) = ‘SUNDAY’ THEN RAISE _APPLICATION_ERROR ( -20001,’NO DELTETION ALLOW ON SUNDAY); END IF; END; INSTEAD OF TRIGGER. This is used to perform DML operation on base table through join view. Create or replace trigger tr_instead Instead of Insert or update or delete On v_join Referencing new as N old as O For each row Declare V_count number; Begin If inserting then Select count(*) into v_count from dept Where deptno = :n.deptno; If v_count = 0 then Insert into dept values(:n.deptno,:n.dname,:n.loc); End if; Insert into emp (empno,ename,sal) values (:n.empno,:n.ename,:n.sal); Elsif updating then Update emp Set sal = :n.sal where empno = :o.empno; Update dept Set dname = :n.dname where deptno = :o.deptno; Elsif deleting then Delete from emp where empno = :o.empno; Delete from dept where deptno = :o.deptno; End if; End; /

Alert in Oracle D2k

Alert :-

1. This is used to display message to the user to confirm any action.
2. use show_alert to show the alert at run time.
3. the above function returns the following values (number()
88 alert_button1
89 alert_button2
90 alert_button3
4. u can change the following attribute of an alert at run time
(title,message,label of button)

Steps for creating a alert.
1. create an alert in object navigator through + command
2. make changed in the property of alert as per requirement.
3. now show the alert at run time using show_alert.


Code for ‘Key-delrec’ trigger at form level ( name of alert is alert_confirm)

Declare
Alt_ret_val numbers;
Begin
Set_alert_property ( ‘alert_confirm’, TITLE,’Confirming Delete Action’);
Set_alert_property ( ‘alert_confirm’,Alert_message_text,’Sure to delete this record ?? ‘);
Set_alert_button_property(‘alert_confirm’,alert_button,label,’plz delete’);
Alert_ret_val := show_alert(‘alert_confirm’);
If alert_ret_val := alert_button1 then delete_record;
Else
Message(‘you cance the operation’);
Message(‘’);
End if;
End;

Note :-
Max you can have 3 button and min you can have 1 button in an alert.
You can not change no of button at run time.

Code for delte record button is

Do_key(‘delete_record’);

It will make a call to key dec-rec

Trigger in D2kTrigger in D2kTrigger in D2k

Trigger in D2k

1. Trigger in form builder are available at three level ie item level, Block level and form level.
2. The area of application of trigger is dependent on the level where it has been defined.
3. If the same trigger has been defined at more than one level than the execution of trigger depends on the value of the property of trigger i.e. ‘ execution hierarchy’.
Override
1. It will override the entire higher level trigger having same name.
before
1. Lower level will be executed before its immediate parent level
After
1. lower level trigger will be executed after its immediate parent.

The lower level trigger will be always available but reverse is not true.



you can help for more these two pic following link








Trigger in form builder is divided into five parts.

1. Pre
2. post
3. when
4. on
5. key

Pre – Trigger. This trigger is executed before the current event.
Some of the pre trigger event are
1. pre-commit
2. pre-form
3. pre-logon
4. pre-text-item
5. pre-block

Pre-Query:-
1. This trigger is executed before any query is send to the database for execution.
2. it is used to validate the user input before sending the user-input to db to remove unnecessary presence on DB.

Code for Pre Query.


If :emp.deptno not in (10,20,30) then message (:emp.deptno ’ does not exict’);
Message(‘’);
Raise form_trigger_failure;
Length(:emp.empno) <4>







Post – trigger :->
1. These trigger are executed after the corresponding event.
2. some of the post trigger
• post-logon
• post-block
• post-query
• post-insert/update/delete
• post-text-item

Post-query:- this trigger is executed after the execution of query in the block
3. pre-query is executed only once and post-query is executed that many no. of times records returned by the query submitted to DB.


Code for Post –Query Emp block

Declare
V_groeg_sal number;
V_grade char(1);
Begin
V_groeg_sal := :emp.T_gross_sal;
If v_gross_sal >= 50000 and v_gross_sal <= 7000 then v_grade := ‘a’; Else v_gross_sal >= 3000 and v_gross_sal <>
1. this is executed one the cursor/control goes from the item.
2. use this trigger to populate any value like grade on the basis of gross sal(sal + comm.)


When trigger ->

1. These trigger get executed when the corresponding event happens.
2. some of the when –trigger
when –button-pressed
when-validate-item
when-validate-record
when-new-item-instance
when-new-record-instance
when-new-block-instance
when-create-record


when-validate-item :-

it is executed when the control comes out of any item after making changes in th e value that item.


When-validate-record :- executed when the control goes out of the current record after making changes in the record.

When –create-record

This trigger is executed when a new record is created in the block.
This is the only trigger in form builder when the forms does not ask you to save changes if we have allocated some value to my field of the block.

Code for when –create-record at emp block

:emp.sal := 200;
:emp.comm := 100;

Code for when-validate-item of Sal field
If :emp.sal<2000> 2000’);
Raise form_trigger_failure;
End if;

Code at blk level
When-validate-record of emp block

If :emp.sal<:emp.comm Then Message(‘plz enter sal>comm’);
Message(‘’);
Raise form_trigger_failure;
Else if :emp.name is null or length(:emp.ename)<4> these trigger executed during corresponding event.

Some of the trigger are.

On-error
On-message
On-logon
On-insertupdatedelete

On-error:-> in this trigger is executed when any errors happens at run time which is a system defined error.
Some of the keyword are

Error-code
Error-type
Error-text

On-message

This is executed when any message comes at run time from form builder.

Keywords

Message-code
Message-type
Message-text
Code for on-error at form level

If error_code = 50016
Then message(‘plz enter only number’);
Message(‘’);
Else
Message(Error_code - ‘ error_type ’ ‘ error_text);
Message(‘’)


Key trigger :-> these trigger are executed whenever a corr. Runtime key is used

Key Trigger Bultin
F7 Key-enter Enter_query
F8 Key-executed Execute_query
F9 Key-listval List-values
F10 Key-commit Commit
F6 Key-crerec Create_record
Shift+f6 Key-delrec Delete_record

1. we can use key trigger to override the default functionality of run time keys.
2. we can use do_key (‘buil-in’) to call the corr/key trigger.
3. for example do_key(‘commit’) will made a call to key-commit trigger.
4. we can also use execute-trigger (‘any trigger name’) to execute the code of any trigger

execute_trigger(‘key-nxtrec’);

Code :-> for key-nexrec

If :system.last_record = ‘true’ then
Message (‘ you are already at last record’);
Message(‘’);
Raise form_trigger_failure;
Else
Next_record;
End if;

Code for next_record button

Button
D0-key(‘next_record’);
Execute_trigger(‘key-nxtrec’);



Code for key-up

If :system.cursor_record = ‘1’
Then message(‘ you are already at first record’);
Message(‘’);
Raise form_trigger_failure;
Else
Previous_record;
End if;

Oracle Develper 2000/D2k/10g Basics tutorial

Form Builder

1. Minimum component required to develop a module is
• Window
• Canvas
• Block
• Item
Window:- this forms object is used to provide physical support to canvas.
Canvas :- this is used to provide physical support to item.
Item:- this is used to accept value from user at run time.
Some of the GUI item are Text Box, Button, Radio Button, Check Box

Block:-
• This is a collection of item and it does not any physical significance. Block always have logically significance.
• There are two type of block DB block and control block

Imp Point.
1 to follow the value of any item of any block use
:.
2. to refer to any item of block , user block name.itemname/
3. to take cursor to any item at run time user go_item(‘blockname.item.name’);
4. to clear block user clear_block;


some of the system defined built ins are.
1. go_item(‘item name’);
2. go_block(‘block name’);
3. go_record(‘sr.no.of.record’);
4. next_item
5. next_block
6. next_record;
7. previous_item
8. previous_block
9. previous_record
10. clear_block
11. clear_item
12. clear_record
13. clear_form
14. execute_query
15. last_record

Object Navigator has two view i.e

1. Ownership view :-
It show the relationship between block and item.
2. physical view :-
it shows the relation between window, canvass and item.
Items are linked to a canvas through its properties.
Canvas is linked to window through properties.

Base table Block

1. This block is associated with a DB object like table,view,procedure.
2. based table block can be created in two ways i.e through wizard, and through manually

Through the wizard.
1. Create a block through object navigator.
2. Select wizard from dialog box.
3. Keep on clicking on next button till the time you get finish button.
4. Save, compile and run the report.

Note
1. use the runtime menu option and toolbar option to do DML operation on a base table block.
2. Some of imp. Menu option are query menu, record menu, block menu.


Cd
code for + button when-button-pressed
:ankur.result := :ankur.result_no1 + :ankur:result_no_2;

Code for – button when button pressed

:ankur.result := :ankur.result_no1 - :ankur:result_no_2;

Code for * button when button pressed

:ankur.result := :ankur.result_no1 * :ankur:result_no_2;

Code for / button when button pressed

:ankur.result := :ankur.result_no1 / :ankur:result_no_2;

How to create a base table block manually

1. crate a block
2. select the option manual from the new block dialog box and say ok
3. in the layout editor, place the items in the block by selecting the block name from list box for block on top right of canvas.
4. in the property of block mention , Name, Datasource type(table), insert/update/delete/query/allowing (yes/no),
5. Now in the property of items mention Name,Data type, Size Colum name,

Control block

1. this is not associated with any DB object
It is used for following purposes (Calculator, Button to do some action.)

Crate a Control block.
1. create a block.
2. in the prop of block (named blocks (no))
3. place items in the block and make required changed in the property of items
Some of the builtin are

Enter_query;
Execute_query;
Create_Record;
Delete_record;
Commit;
Exit_form
First/last/next/pervious-record;