Friday, December 28, 2018
Wednesday, September 19, 2018
Alert
SET_ALERT_PROPERTY('ALT_TWO',ALERT_MESSAGE_TEXT,'Do you want to Send Requisition ?');
V_ALERT:=SHOW_ALERT('ALT_TWO');
PICTURE
IF V_ALERT=ALERT_BUTTON1 THEN
GO_BLOCK('SRV_REQUISITION');
LAST_RECORD;
IF :SRV_REQUISITION.TEST_NO IS NULL THEN
DELETE_RECORD;
END IF;
V_TEST_COUNT:=:SYSTEM.CURSOR_RECORD;
FIRST_RECORD;
FOR I IN 1..V_TEST_COUNT LOOP
IF :SRV_REQUISITION.TEST_NO IS NOT NULL THEN
--MSGBOX(:SRV_REQUISITION.TEST_NO);
LIST_TEST(I) :=:SRV_REQUISITION.TEST_NO;
LIST_TEST_QTY(I) :=NVL(:SRV_REQUISITION.QUANTITY,1) ; --:CTRL_INVESTIGATIONINFO.QTY;
LIST_STATUS(I) :=:SRV_REQUISITION.DELIVERY_STATUS; --:CTRL_INVESTIGATIONINFO.DELIVERY_STATUS;
LIST_SERVICE(I) :=:SRV_REQUISITION.TEST_TYPE;
LIST_REMARKS(I) :=:SRV_REQUISITION.REMARKS; --:CTRL_INVESTIGATIONINFO.REMARKS;
LIST_TEST_AMT(I) :=:SRV_REQUISITION.RATE;
LIST_INSTRUCTION(I) :=:SRV_REQUISITION.INSTRUCTION;
LIST_SAMPLE(I) :=:SRV_REQUISITION.SAMPLE_ID;
LIST_DELIVERY_DT(I) :=SYSDATE;
-- LIST_BILL_TYPE(I) :=0; --HOLDS BILL TYPE
LIST_LETTER_NO(I) :=:CTRL_CCPATIENT.REF_LETTERNO;
LIST_DISC(I) :=NULL;
LIST_DISC_AMT(I) :=0; --HOLDS TEST'S DISCOUNT AMOUNT
LIST_VAT_AMT(I) :=0; --HOLDS TEST'S VAT AMOUT
LIST_DX_RX_NO(I) :='';
--GRANT_DISCAMT :=0; --HOLDS TEST'S DISCOUNT TOTAL AMOUNT
--LIST_CLNT_ID(I) :=NVL(:CTRL_CASHPOINT.CLIENT,1);---1 FRO SERVICE PROVIDER
LIST_RATEGRP(I) :=NVL(:CTRL_CASHPOINT.RATE_GROUP,1) ;
IF :CTRL_CCPATIENT.REF_LETTERNO is not null then
LIST_BILL_TYPE(I):=1;--- IF IT IS CORPORATE NEED NOT TO PAY
LIST_PATPAY(I) :='N';
LIST_CLNT_ID(I) :=:CTRL_CCPATIENT.CLNT_ID;
ELSE
LIST_BILL_TYPE(I):=0;
LIST_PATPAY(I) :='Y';
LIST_CLNT_ID(I) :=1;
END IF;
-- V_CHECKEXIST:=V_CHECKEXIST+1;
NEXT_RECORD;
ELSE
NEXT_RECORD;
END IF;
END LOOP;
Sunday, September 16, 2018
logon page center window.
Call procedure in when new form instance
CENTER_FORM('WINDOW1',0);
PROCEDURE CENTER_FORM(MyWindowName IN varchar2,UpperMargin IN number) IS
x number(6);
y number(6);
BEGIN
SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW,TITLE,'India');
Set_window_property(forms_mdi_window,window_state,maximize);
x:=((get_window_property(forms_mdi_window,width)-get_window_property(MyWindowName ,width))/2);
y:=((get_window_property(forms_mdi_window,height)-get_window_property(MyWindowName ,height))/2+UpperMargin);
set_window_property(MyWindowName , position,x,y);
END;
Wednesday, September 5, 2018
Friday, August 17, 2018
Monday, August 13, 2018
Trigger
$$ Trigger with use when condition $$
CREATE OR REPLACE TRIGGER trg_hpms_room
AFTER INSERT OR UPDATE
ON hpms_room
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (NEW.occupancy_service IN ('E', 'C', 'D'))
BEGIN
IF INSERTING
THEN
IF :NEW.occupancy_service = 'C'
THEN
DECLARE
sl NUMBER (3);
BEGIN
SELECT MAX (view_sl) + 1
INTO sl
FROM op_patdisplay
WHERE tvdisplay_id = 1;
INSERT INTO op_patdisplay
(room_name, movement_type_no, room_no, view_sl,
show_fg, view_page, tvdisplay_id, check_inout
)
VALUES (:NEW.room_name, '5', :NEW.room_no, sl,
'1', '1', '1', '0'
);
END;
ELSIF :NEW.occupancy_service = 'E'
THEN
DECLARE
sl NUMBER (3);
BEGIN
SELECT MAX (view_sl) + 1
INTO sl
FROM op_patdisplay
WHERE tvdisplay_id = 3;
INSERT INTO op_patdisplay
(room_name, movement_type_no, room_no, view_sl,
show_fg, view_page, tvdisplay_id, check_inout
)
VALUES (:NEW.room_name, '5', :NEW.room_no, sl,
'1', '1', '3', '0'
);
END;
ELSIF :NEW.occupancy_service = 'D'
THEN
DECLARE
sl NUMBER (3);
BEGIN
SELECT MAX (view_sl) + 1
INTO sl
FROM op_patdisplay
WHERE tvdisplay_id = 2;
INSERT INTO op_patdisplay
(room_name, movement_type_no, room_no, view_sl,
show_fg, view_page, tvdisplay_id, check_inout
)
VALUES (:NEW.room_name, '5', :NEW.room_no, sl,
'1', '1', '2', '0'
);
END;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/
CREATE OR REPLACE TRIGGER trg_hpms_room
AFTER INSERT OR UPDATE
ON hpms_room
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (NEW.occupancy_service IN ('E', 'C', 'D'))
BEGIN
IF INSERTING
THEN
IF :NEW.occupancy_service = 'C'
THEN
DECLARE
sl NUMBER (3);
BEGIN
SELECT MAX (view_sl) + 1
INTO sl
FROM op_patdisplay
WHERE tvdisplay_id = 1;
INSERT INTO op_patdisplay
(room_name, movement_type_no, room_no, view_sl,
show_fg, view_page, tvdisplay_id, check_inout
)
VALUES (:NEW.room_name, '5', :NEW.room_no, sl,
'1', '1', '1', '0'
);
END;
ELSIF :NEW.occupancy_service = 'E'
THEN
DECLARE
sl NUMBER (3);
BEGIN
SELECT MAX (view_sl) + 1
INTO sl
FROM op_patdisplay
WHERE tvdisplay_id = 3;
INSERT INTO op_patdisplay
(room_name, movement_type_no, room_no, view_sl,
show_fg, view_page, tvdisplay_id, check_inout
)
VALUES (:NEW.room_name, '5', :NEW.room_no, sl,
'1', '1', '3', '0'
);
END;
ELSIF :NEW.occupancy_service = 'D'
THEN
DECLARE
sl NUMBER (3);
BEGIN
SELECT MAX (view_sl) + 1
INTO sl
FROM op_patdisplay
WHERE tvdisplay_id = 2;
INSERT INTO op_patdisplay
(room_name, movement_type_no, room_no, view_sl,
show_fg, view_page, tvdisplay_id, check_inout
)
VALUES (:NEW.room_name, '5', :NEW.room_no, sl,
'1', '1', '2', '0'
);
END;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/
Thursday, August 9, 2018
Tuesday, July 31, 2018
Computer Basic
ALU: An arithmetic logic unit (ALU) is a digital
circuit used to perform arithmetic and logic operations.It represents the
fundamental building block of the central processing unit (CPU) of a computer.
Modern CPUs contain very powerful and complex ALUs. The purpose of the
ALU is to perform mathematical operations such as addition, subtraction,
multiplication and division. Additionally, the ALU
processes basic logical operations like AND/OR calculations
Friday, July 27, 2018
Tuesday, July 24, 2018
Sunday, July 15, 2018
Cursor
Purpose : Used to update table column:
DECLARE
CURSOR C1
IS
SELECT EMPLOYEENAME,EMPLOYEECODE
FROM ATI_PMIS_EMPLOYEE
WHERE EMPLOYEECODE IN (SELECT POLICE_BP_NO FROM OP_REGISTRATION
WHERE FULL_NAME IS NULL
AND POLICE_BP_NO IS NOT NULL
AND REL_ID=0)
AND EMPLOYEENAME IS NOT NULL;
BEGIN
FOR I IN C1 LOOP
UPDATE OP_REGISTRATION
SET FULL_NAME=I.EMPLOYEENAME
WHERE POLICE_BP_NO=I.EMPLOYEECODE;
COMMIT;
END LOOP;
END;
DECLARE
CURSOR C1
IS
SELECT EMPLOYEENAME,EMPLOYEECODE
FROM ATI_PMIS_EMPLOYEE
WHERE EMPLOYEECODE IN (SELECT POLICE_BP_NO FROM OP_REGISTRATION
WHERE FULL_NAME IS NULL
AND POLICE_BP_NO IS NOT NULL
AND REL_ID=0)
AND EMPLOYEENAME IS NOT NULL;
BEGIN
FOR I IN C1 LOOP
UPDATE OP_REGISTRATION
SET FULL_NAME=I.EMPLOYEENAME
WHERE POLICE_BP_NO=I.EMPLOYEECODE;
COMMIT;
END LOOP;
END;
Saturday, July 14, 2018
Wednesday, July 11, 2018
Tuesday, July 3, 2018
Sunday, July 1, 2018
Triks for oracle
To assign search text in search box :
<>:CTRL_BLK.COMPLAIN_SRC:='<<Search Complain>>';
To assing item record sl. no:
use post query trigger block level:
<>:EMV_COMPLAIN.sl :=:system.trigger_record;
<>:CTRL_BLK.COMPLAIN_SRC:='<<Search Complain>>';
To assing item record sl. no:
use post query trigger block level:
<>:EMV_COMPLAIN.sl :=:system.trigger_record;
Wednesday, June 27, 2018
To show and tik mark and croos mark in oracle report;
1. first try to keep value for tik mark P and for cross mark O
for help try as following query :
C.COMP_PRIORITY_NM, C.COMPLAIN_FOR, C.COMP_FOR_NM,
C.FOR_REF_NO, C.COMP_4REF_NM, C.COMP_DSCP,
C.PRIMARY_ACT, C.COMP_TO_DEPT_NO,
(SELECT DEPT_NAME FROM HR_DEPT WHERE DEPT_NO = C.COMP_TO_DEPT_NO) COMP_TO_DEPT_NAME,
C.COMP_TO_EMP_NO,
C.LOC_ROOM_NO, C.LOC_DEPT_NO, C.LOC_DESC,
C.COMP_BY, C.COMP_BYDEPT_NM, C.COMP_DTTM,
C.IDENTIFIED_BY, C.IDENTIFIED_DTTM, C.IDENTIFIED_BYDEPT_NO,
DECODE(C.VERIFY_FLAG, 'Y', 'P', 'O') VERIFY_FLAG,
C.VERIFY_DTTM, C.VERIFIED_BY,
DECODE(C.VALID_FLAG, 'Y', 'P', 'O') VALID_FLAG,
DECODE(C.SOLVE_FG, 'Y', 'P', 'O') SOLVE_FG,
DECODE(C.DECI_FG, 'Y', 'P', 'O') DECI_FG,
C.ACTION_FG, C.VERIFY_COMMENT, C.COMPANY_NO
FROM CMV_COMPLAIN C
---
then just take filed font ~ windings(western)2 and enjoy it.
Screen short :
Monday, June 11, 2018
Trigger used in forms :
Type : User defined.
Oracle provided.
go_field('adm_rec_amount');----same block so no need prefix.
--- KEY-NEXT-ITME :
This trigger can use procedure(user defined) like prc_search procedure ;
Example :
PROCEDURE prc_search IS
V_DW_NEW VARCHAR2(4000);
BEGIN
V_DW_NEW :=GET_BLOCK_PROPERTY('EMV_COMPLAIN',DEFAULT_WHERE);
V_DW_NEW := 'company_no = :global.g_company_no';
IF :ctrl_blk.complain_src IS NOT NULL THEN
V_DW_NEW := V_DW_NEW||' AND upper(COMPLAIN_NAME) LIKE ''%''||upper(:CTRL_BLK.COMPLAIN_SRC)|| ''%'' ';
END IF;
GO_BLOCK('EMV_COMPLAIN');
CLEAR_BLOCK(NO_VALIDATE);
SET_BLOCK_PROPERTY('EMV_COMPLAIN',ONETIME_WHERE,V_DW_NEW );
:SYSTEM.MESSAGE_LEVEL:=5;
EXECUTE_QUERY;
:SYSTEM.MESSAGE_LEVEL:=0;
EXCEPTION
WHEN OTHERS THEN
MSGBOX(SQLCODE||' '||SQLERRM);
END;
------WHEN-VALIDATE-ITEM (( Trigger fires when item value is changed).)
example 1:
begin
if :ename is null then
message (‘Employee name cannot be blank’);
message(‘ ‘);
raise form_trigger_failure;
end if;
end;
example 3:
-------When-new-item-instance;
Properties : Item level trigger .
Effect in form :
Image after use this trigger :
Image before use this trigger :
Oracle provided.
go_field('adm_rec_amount');----same block so no need prefix.
--- KEY-NEXT-ITME :
This trigger can use procedure(user defined) like prc_search procedure ;
Example :
PROCEDURE prc_search IS
V_DW_NEW VARCHAR2(4000);
BEGIN
V_DW_NEW :=GET_BLOCK_PROPERTY('EMV_COMPLAIN',DEFAULT_WHERE);
V_DW_NEW := 'company_no = :global.g_company_no';
IF :ctrl_blk.complain_src IS NOT NULL THEN
V_DW_NEW := V_DW_NEW||' AND upper(COMPLAIN_NAME) LIKE ''%''||upper(:CTRL_BLK.COMPLAIN_SRC)|| ''%'' ';
END IF;
GO_BLOCK('EMV_COMPLAIN');
CLEAR_BLOCK(NO_VALIDATE);
SET_BLOCK_PROPERTY('EMV_COMPLAIN',ONETIME_WHERE,V_DW_NEW );
:SYSTEM.MESSAGE_LEVEL:=5;
EXECUTE_QUERY;
:SYSTEM.MESSAGE_LEVEL:=0;
EXCEPTION
WHEN OTHERS THEN
MSGBOX(SQLCODE||' '||SQLERRM);
END;
------WHEN-VALIDATE-ITEM (( Trigger fires when item value is changed).)
example 1:
begin
if :ename is null then
message (‘Employee name cannot be blank’);
message(‘ ‘);
raise form_trigger_failure;
end if;
end;
example 2:
BEGIN
IF :sal < 3000
THEN
MESSAGE (‘Employee sal must NOT be less than 3000’);
MESSAGE(‘ ‘);
raise form_trigger_failure;
end if;
end;
--Here use hr schema table employees filed(EMPLOYEE_ID)
--
begin
message('Here use global variable,record status');
message(:system.record_status);
select :EMPLOYEES.EMPLOYEE_ID into :global.v1
from dual;
if :global.v1 =1
then message(:global.v1);
else
:global.v1:=2;
message(:global.v1);
end if;
end;
screen short :
Properties : Item level trigger .
Effect in form :
Image after use this trigger :
Block Level Trigger :
When-new-record-instance
Example : Auto number generate:
:PM_RXCHD.MED_SL := :system.trigger_record;
Query Optimization
Link youtube to learn :
## Oracle Advanced SQL Optimization Techniques
blogs.oracle.com
Cursor for loop :
SELECT-INTO
1. select distinct hpms_doctor.doctor_no, hpms_doctor.DOCTOR_NAME
from op_appointment,hpms_doctor
where hpms_doctor.DOCTOR_NO=op_appointment.DOCTOR_NO
and trunc(appoint_date) = trunc(sysdate)
and hpms_doctor.doctor_no in (select consult_by from opv_appexchange)
/
2. select distinct D.doctor_no, D.DOCTOR_NAME
from hpms_doctor D, op_appointment A
where D.DOCTOR_NO=A.DOCTOR_NO
and trunc(a.appoint_date) = trunc(sysdate)
and exists (select consult_by from opv_appexchange)
order by D.DOCTOR_NAME
ABOVE two query 2. is faster than one.
----20190206---
use with clause
## Oracle Advanced SQL Optimization Techniques
blogs.oracle.com
Cursor for loop :
SELECT-INTO
SELECT-INTO offers the fastest and simplest way to fetch a single row from a SELECT statement. The syntax of this statement is
SELECT select_list INTO variable_list FROM remainder_of_query;
where remainder_of_query contains the list of tables or views, the WHERE clause, and other clauses of the query. The number and types of elements in the variable_list must match those of the select_list.
If the SELECT statement identifies more than one row to be fetched, Oracle Database will raise the TOO_MANY_ROWS exception. If the statement doesn’t identify any rows to be fetched, Oracle Database will raise the NO_DATA_FOUND exception.
Here are some examples of using SELECT-INTO:
Get the last name for a specific employee ID (the primary key in the employees table):
DECLARE
l_last_name employees.last_name%TYPE;
BEGIN
SELECT last_name
INTO l_last_name
FROM employees
WHERE employee_id = 138;
DBMS_OUTPUT.put_line (
l_last_name);
END;
If there is a row in the employees table with ID 138, this block will display the last name of that employee. If there is no such row, the block will fail with an unhandled NO_DATA_FOUND exception. Assuming that a unique index is defined on the employee_id column, this block will never raise the TOO_MANY_ROWS exception.
Fetch an entire row from the employees table for a specific employee ID:
DECLARE
l_employee employees%ROWTYPE;
BEGIN
SELECT *
INTO l_employee
FROM employees
WHERE employee_id = 138;
DBMS_OUTPUT.put_line (
l_employee.last_name);
END;
Again, if an employee exists for that ID, the last name will be displayed. In this case, I declare a record based on the employees table and fetch all columns (with a SELECT *) into that record for the specified row.
Fetch columns from different tables:
DECLARE
l_last_name
employees.last_name%TYPE;
l_department_name
departments.department_name%TYPE;
BEGIN
SELECT last_name, department_name
INTO l_last_name, l_department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id
AND e.employee_id=138;
DBMS_OUTPUT.put_line (
l_last_name ||
' in ' ||
l_department_name);
END;
In this case, I need more than one column value but not all the column values in either or both of the tables. So I declare two variables and fetch the two column values into those variables.
What happens if the list of variables in the INTO clause does not match the SELECT list of the query? You will see one of the error messages shown in Table 1.
| ORA-00947: not enough values | The INTO list contains fewer variables than the SELECT list. |
| ORA-00913: too many values | The INTO list contains more variables than the SELECT list. |
| ORA-06502: PL/SQL: numeric or value error | The number of variables in the INTO and SELECT lists matches, but the datatypes do not match and Oracle Database was unable to convert implicitly from one type to the other. |
Table 1: Possible error messages if INTO and SELECT lists do not match
Fetching from Explicit Cursors
A SELECT-INTO is also referred to as an implicit query, because Oracle Database implicitly opens a cursor for the SELECT statement, fetches the row, and then closes the cursor when it finishes doing that (or when an exception is raised).
You can, alternatively, explicitly declare a cursor and then perform the open, fetch, and close operations yourself.
Suppose I need to write a block that fetches employees in ascending salary order and gives them a bonus from a total pool of funds by calling the assign_bonus procedure, whose header is
PROCEDURE assign_bonus (
employee_id_in IN
employees.employee_id%TYPE,
bonus_pool_io IN OUT INTEGER)
Each time assign_bonus is called, the procedure subtracts the bonus given from the total and returns that reduced total. When that bonus pool is exhausted, it stops fetching and commits all changes.
Listing 1 includes a block that uses an explicit cursor to implement this logic, and it describes the operations in the block at specified line numbers.
Code Listing 1: Block and description of explicit cursor implementation
1 DECLARE 2 l_total INTEGER := 10000; 3 4 CURSOR employee_id_cur 5 IS 6 SELECT employee_id 7 FROM plch_employees 8 ORDER BY salary ASC; 9 10 l_employee_id employee_id_cur%ROWTYPE; 11 BEGIN 12 OPEN employee_id_cur; 13 14 LOOP 15 FETCH employee_id_cur INTO l_employee_id; 16 EXIT WHEN employee_id_cur%NOTFOUND; 17 18 assign_bonus (l_employee_id, l_total); 19 EXIT WHEN l_total <= 0; 20 END LOOP; 21 22 CLOSE employees_cur; 23 END;
| Line(s) | Description |
| 4–8 | The explicit cursor declaration. Move the query from the executable section (where the SELECT-INTO must reside), and use the CURSOR keyword to declare (give a name to) that query. |
| 10 | Declare a record based on the row of data returned by the query. In this case, there is just a single column value, so you could just as easily have declared l_employee_id as employees.employee_id%TYPE. But whenever you use an explicit cursor, it is best to declare a record by using %ROWTYPE, so that if the SELECT list of the cursor ever changes, that variable will change with it. |
| 12 | Open the cursor, so that rows can now be fetched from the query. Note: This is a step Oracle Database performs with the SELECT-INTO statement. |
| 14 | Start a loop to fetch rows. |
| 15 | Fetch the next row for the cursor, and deposit that row’s information into the record specified in the INTO clause. Note: This is a step Oracle Database performs with the SELECT-INTO statement. |
| 16 | If the FETCH does not find a row, exit the loop. |
| 18 | Call assign_bonus, which applies the bonus and also decrements the value of the l_total variable by that bonus amount. |
| 19 | Exit the loop if all the bonus funds have been exhausted. |
| 22 | Close the cursor. Note: This is a step Oracle Database performs with the SELECT-INTO statement. |
Here are some things to keep in mind when working with explicit cursors:
- If the query does not identify any rows, Oracle Database will not raise NO_DATA_FOUND. Instead, the cursor_name%NOTFOUND attribute will return TRUE.
- Your query can return more than one row, and Oracle Database will not raise TOO_MANY_ROWS.
- When you declare a cursor in a package (that is, not inside a subprogram of the package) and the cursor is opened, it will stay open until you explicitly close it or your session is terminated.
- When the cursor is declared in a declaration section (and not in a package), Oracle Database will also automatically close it when the block in which it is declared terminates. It is still, however, a good idea to explicitly close the cursor yourself. If the cursor is moved to a package, you will have the now necessary CLOSE already in place. And if it is local, then including a CLOSE statement will also show other developers and your manager that you are paying attention.
The cursor FOR loop is an elegant and natural extension of the numeric FOR loop in PL/SQL. With a numeric FOR loop, the body of the loop executes once for every integer value between the low and high values specified in the range. With a cursor FOR loop, the body of the loop is executed for each row returned by the query.
The following block uses a cursor FOR loop to display the last names of all employees in department 10:
BEGIN
FOR employee_rec IN (
SELECT *
FROM employees
WHERE department_id = 10)
LOOP
DBMS_OUTPUT.put_line (
employee_rec.last_name);
END LOOP;
END;
You can also use a cursor FOR loop with an explicitly declared cursor:
DECLARE
CURSOR employees_in_10_cur
IS
SELECT *
FROM employees
WHERE department_id = 10;
BEGIN
FOR employee_rec
IN employees_in_10_cur
LOOP
DBMS_OUTPUT.put_line (
employee_rec.last_name);
END LOOP;
END;
The nice thing about the cursor FOR loop is that Oracle Database opens the cursor, declares a record by using %ROWTYPE against the cursor, fetches each row into a record, and then closes the loop when all the rows have been fetched (or the loop terminates for any other reason).
Best of all, Oracle Database automatically optimizes cursor FOR loops to perform similarly to BULK COLLECT queries (covered in “Bulk Processing with BULK COLLECT and FORALL,” in the September/October 2012 issue of Oracle Magazine). So even though your code looks as if you are fetching one row at a time, Oracle Database will actually fetch 100 rows at a time—and enable you to work with each row individually.
Dynamic Queries with EXECUTE IMMEDIATE
Dynamic SQL means that at the time you write (and then compile) your code, you do not have all the information you need for parsing a SQL statement. Instead, you must wait for runtime to complete the SQL statement and then parse and execute it.
Oracle Database makes it easy to execute SQL statements (and PL/SQL blocks) dynamically with the EXECUTE IMMEDIATE statement. And querying data is the easiest dynamic SQL operation of all!
You can fetch a single row or multiple rows. Here is a generic function that fetches the value of a numeric column in any table, for the specified WHERE clause:
CREATE OR REPLACE FUNCTION
single_number_value (
table_in IN VARCHAR2,
column_in IN VARCHAR2,
where_in IN VARCHAR2)
RETURN NUMBER
IS
l_return NUMBER;
BEGIN
EXECUTE IMMEDIATE
'SELECT '
|| column_in
|| ' FROM '
|| table_in
|| ' WHERE '
|| where_in
INTO l_return;
RETURN l_return;
END;
As you can see, instead of SELECT-INTO, I use EXECUTE IMMEDIATE-INTO and construct the SELECT statement from the arguments passed to the function. Here’s an example of calling the function:
BEGIN
DBMS_OUTPUT.put_line (
single_number_value (
'employees',
'salary',
'employee_id=138'));
END;
As with SELECT-INTO, EXECUTE IMMEDIATE-INTO will raise NO_DATA_FOUND if no rows are found and TOO_MANY_ROWS if more than one row is found.
You can also use EXECUTE IMMEDIATE to fetch multiple rows of data, which means that you will populate a collection, so you must use BULK COLLECT. The following is a procedure that will display the values of any numeric column for all rows specified in the WHERE clause:
CREATE OR REPLACE PROCEDURE
show_number_values (
table_in IN VARCHAR2,
column_in IN VARCHAR2,
where_in IN VARCHAR2)
IS
TYPE values_t IS TABLE OF NUMBER;
l_values values_t;
BEGIN
EXECUTE IMMEDIATE
'SELECT '
|| column_in
|| ' FROM '
|| table_in
|| ' WHERE '
|| where_in
BULK COLLECT INTO l_values;
FOR indx IN 1 .. l_values.COUNT
LOOP
DBMS_OUTPUT.put_line
(l_values (indx));
END LOOP;
END;
And when I call the procedure for the standard employees table
BEGIN
show_number_values (
'employees',
'salary',
'department_id = 10
order by salary desc');
END;
I see the following two rows of output:
4400 3200
------------
1. select distinct hpms_doctor.doctor_no, hpms_doctor.DOCTOR_NAME
from op_appointment,hpms_doctor
where hpms_doctor.DOCTOR_NO=op_appointment.DOCTOR_NO
and trunc(appoint_date) = trunc(sysdate)
and hpms_doctor.doctor_no in (select consult_by from opv_appexchange)
/
2. select distinct D.doctor_no, D.DOCTOR_NAME
from hpms_doctor D, op_appointment A
where D.DOCTOR_NO=A.DOCTOR_NO
and trunc(a.appoint_date) = trunc(sysdate)
and exists (select consult_by from opv_appexchange)
order by D.DOCTOR_NAME
ABOVE two query 2. is faster than one.
----20190206---
use with clause
WITH dept_count AS
(select identifyed_no,count(
where trunc(entry_timestamp)=trunc(
group by identifyed_no
having
count(identifyed_no) >1)
select a.* from
(select identifyed_no,sl#,time,
from
(select identifyed_no,sl#,to_char(ref_
union
(select identifyed_no,null sl#,null time,null rdoctor_no,null doctor,null advice_by,null advicer,null operator,to_char(START_DATE,'
where trunc(entry_timestamp)=trunc(
))
order by identifyed_no,sl#) a,dept_count b
where a.identifyed_no =b.identifyed_no
----------------EXECUTION PLAN SCRIPT.
explain plan for select * from product;
/
select * from table(dbms_xplan.display);
Thursday, May 31, 2018
Trigger in oracle forms
All lINK :
Built_in sub programs used in Form Triggers .
1 commit_form used to save changes ( Valid for DML )
2 clear_form clears all the blocks in form and place the cursor in first block
3 clear_block clears the current block
4 clear_record clears the current record
5 clear_item clears the current text item
6 exit_form quits the application
7 next_block moves to the next block
8 previous_block moves to the previous block
9 go_block ( ‘BLOCK_NAME’) places the cursor in the block specified
10 next_record moves to the next record
11 previous_record moves to the previous record
12 first_record moves to the first record
13 last_record moves to the last record
14 next_item place the cursor in next item ( TAB key )
15 previous_item place the cursor in previous item
16 go_item ( ‘BLOCKNAME.ITEMNAME’) place the cursor in the specified item
17 show_view(‘CANVAS’) used to display canvas programatically
18 hide_view (‘CANVAS’) used to hide the canvas
19 enter_query prompts to accept a condition for query
20 execute_query retrieves the data from table into the block
21 abort_query cancel query
22 show_alert ( ‘ALERT_NAME’) used to display alert at run time
23 show_lov (‘LOV_NAME’) used to display LOV at runtime
24 set_block_property used to change properties of block
25 set_item_property used to change properties of the item
26 set_window_property used to change properties of the window
27 set_lov_property used to change lov properties at runtime
28 set_alert_property used to change alert properties at runtime
29 set_alert_button_property used to change properties of alert buttons at runtime
30 create_record used to add new record – ( It clears the screen and allows to insert a record )
31 delete_record used to remove record – ( applied to the current record pointer by cursor ) After adding a row or removing a row “commit” has to be applied to save changes permanently)
32 message (‘message text’||variable); used to print messages
33 call_form used to call a form from existing form ( used to navigate between forms )
34 run_product used to run the other products of oracle developer suite like Oracle Graphics or Oracle reports
Form Triggers
Trigger is collection of SQL ,
PL/SQL and Form coding statements executed based on the event.
Triggers are executed automatically at run time based on an event.
Triggers are executed automatically at run time based on an event.
Triggers can be created in three levels.
- Form level triggers
- Block level triggers
- Item level triggers
Form Level Triggers
These triggers are applied to all the components in the form.
Example
These triggers are applied to all the components in the form.
Example
Slno
|
Trigger
|
Explanation
|
1
|
pre-form
|
Activated before entering into the
form.
|
2
|
when-new-form-instance
|
Activated whenever the form is
ready to accept data from user
|
3
|
on-clear-details
|
Activated when form is cleared (
clear screen )
|
4
|
post-form
|
Activated when cursor leaves the
form.
|
5
|
on-logon
|
fires when user enters into
application.
|
This is the first trigger executed
in forms.
|
||
Application loses database
connection as soon as on-logon triggers is executed.
|
||
User has to provide database
connection information in this trigger code to re-establish the connection
|
||
6
|
on-logout
|
Fired when user quits the
application
|
This is the last trigger executed
in forms
|
||
7
|
when-timer-expired
|
fired whenever there is a change
in system time
|
Block level triggers
Applied to all items in the block.
Example
Applied to all items in the block.
Example
Slno
|
Trigger
|
Explanation
|
1
|
pre-block
|
Fired before entering into the
block.
|
2
|
when-new-block-instance
|
Fried when block is ready to
accept input.
|
3
|
when-clear-block
|
Fired whe block is cleared.
|
4
|
pre-query
|
Fired when executing a query.
|
5
|
post-query
|
Fired after executing the query.
|
6
|
post-block
|
Fired when cursor leaves the block
|
7
|
when-create-record
|
Fired whenever a new record is
inserted.
|
Item level triggers
Applied for a particular item.
Applied for a particular item.
Slno
|
Trigger
|
Explanation
|
1
|
pre-text-item
|
Fired before entering into the
text item
|
2
|
post-text-item
|
Fired when cursor moves to the
next item
|
( Fired after placing cursor in
next item )
|
||
3
|
when-new-item-instance
|
Fired when item is ready to accept
input
|
4
|
when-validate-item
|
Fired when item value is changed
|
5
|
when-clear-item
|
Fired when item is cleared
|
6
|
key-next-item
|
Fired when cursor is moving to
next item.
|
(Fired before placing cursor in
next item )
|
||
Cursor will not be placed in next
item after executing this trigger. ( Use next_item built_in )
|
||
Fired only when TAB key is
pressed.
|
Button
Slno
|
Trigger
|
Explanation
|
1
|
when-button-pressed
|
Fired when button is pressed
|
Radio group
Slno
|
Trigger
|
Explanation
|
1
|
when-radio-changed
|
Fired whenever radio group value
is changed
|
Check box
Slno
|
Trigger
|
Explanation
|
1
|
when-checkbox-changed
|
Fired whenever checkbox value is
changed
|
List Item
Slno
|
Trigger
|
Explanation
|
1
|
when-list-changed
|
Fired when list value is changed
|
Lets workout with few
examples
Example of on-logon trigger
Create a table student.
SQL> create table student ( sno number(3),
Sname varchar2(10),
Marks number(3));
Insert two rows into the table.
SQL> insert into student values ( 101 , ‘ARUN’, 40);
SQL> insert into student values ( 102 , ‘KIRAN’, 60);
SQL> commit;
Example of on-logon trigger
Create a table student.
SQL> create table student ( sno number(3),
Sname varchar2(10),
Marks number(3));
Insert two rows into the table.
SQL> insert into student values ( 101 , ‘ARUN’, 40);
SQL> insert into student values ( 102 , ‘KIRAN’, 60);
SQL> commit;
Now, create form based on student
table ( using data block and layout wizard )
Save the from with the name STUDENT.FMB
In object navigator of STUDENT.FMB
Create a new trigger under STUDENT form.
Select trigger click on create button
Save the from with the name STUDENT.FMB
In object navigator of STUDENT.FMB
Create a new trigger under STUDENT form.
Select trigger click on create button
form48
Select ON-LOGON Ok
Write the following code in the trigger
Message (‘Welcome to my Application’);
Logon (‘scott’, ‘tiger@oracle’);
Click on compile button.
You should get a message “Successfully compiled”
Write the following code in the trigger
Message (‘Welcome to my Application’);
Logon (‘scott’, ‘tiger@oracle’);
Click on compile button.
You should get a message “Successfully compiled”
form49
Message ( ) — is a built_in,
used to display message
Logon ( ) — is a built_in, used to extablish database connection through code.
Note: Application loses the database connection as soon as ON-LOGON trigger is executed.
Hence, to re-establish the connection we use logon ( ) — built-in
Logon ( ) — is a built_in, used to extablish database connection through code.
Note: Application loses the database connection as soon as ON-LOGON trigger is executed.
Hence, to re-establish the connection we use logon ( ) — built-in
Similarly , Lets us create ON_LOGOUT
trigger ( It is a form level trigger).
Create a new trigger under STUDENT
form.
Select trigger click on create button
Select ON-LOGOUT Ok
Write the following code in the trigger
Message (‘Thank you for using my Application’);
Select trigger click on create button
Select ON-LOGOUT Ok
Write the following code in the trigger
Message (‘Thank you for using my Application’);
Click on compile button.
You should get a message “Successfully compiled”
You should get a message “Successfully compiled”
form50
So, we have created two triggers in
our form.
form51
Program Compile Module
Program Run Form
Program Run Form
As we know, ON-LOGON trigger is the
first trigger which is fired,
We get a message “Welcome to my
Application” at the bottom of the form.
form52
Select Action in Menu Bar Exit ,
ON-LOGOUT trigger is executed.
form53
Now, lets make few changes in the
existing form ( STUDENT.FMB)
Open the existing form ( STUDENT.FMB ) Open ON-LOGON trigger ( Right click Pl/SQL editor )
Include another message( ) – built_in
Now, Compile.
Open the existing form ( STUDENT.FMB ) Open ON-LOGON trigger ( Right click Pl/SQL editor )
Include another message( ) – built_in
Now, Compile.
form54
Close the trigger.
Program Compile Module
Program Run form
Program Compile Module
Program Run form
form55
We get welcome message in pop up
window.
By using additional message( ) – built_in , we get message in pop up window.
By using additional message( ) – built_in , we get message in pop up window.
Example of WHEN-VALIDATE-ITEM
trigger ( Trigger fires when item value is changed).
Create a table EMPONE in our
database ( It is same as standard EMP table).
Create a form based on EMPONE table ( I think you already have it. EMPONE.FMB ).
If you already have EMPONE.FMB, Open the existing form ( EMPONE.FMB)
Create a form based on EMPONE table ( I think you already have it. EMPONE.FMB ).
If you already have EMPONE.FMB, Open the existing form ( EMPONE.FMB)
In Object Navigator ,
Under data block (EMPONE) à Item ( ENAME) à Create trigger à WHEN-VALIDATE-ITEM
Under data block (EMPONE) à Item ( ENAME) à Create trigger à WHEN-VALIDATE-ITEM
form57
Write the following code in WHEN-VALIDATE-ITEM
trigger
begin
if :ename is null then
message (‘Employee name cannot be blank’);
message(‘ ‘);
raise form_trigger_failure;
end if;
end;
Click on compile button.
You should get confirmation message “successfully compiled”.
if :ename is null then
message (‘Employee name cannot be blank’);
message(‘ ‘);
raise form_trigger_failure;
end if;
end;
Click on compile button.
You should get confirmation message “successfully compiled”.
form58
Let’s create another trigger (
WHEN-VALIDATE-ITEM )which checks for valid salary.
In Object navigator à under data block ( EMPONE) à under SAL item à Create trigger à WHEN-VALIDATE-ITEM
In Object navigator à under data block ( EMPONE) à under SAL item à Create trigger à WHEN-VALIDATE-ITEM
begin
if :sal <3000 then
message(‘Employee sal must not be less than 3000’);
message(‘ ‘);
raise form_trigger_failure;
end if;
end;
Click on compile button.
You should get confirmation message “successfully compiled”.
if :sal <3000 then
message(‘Employee sal must not be less than 3000’);
message(‘ ‘);
raise form_trigger_failure;
end if;
end;
Click on compile button.
You should get confirmation message “successfully compiled”.
form59
Raise
form_trigger_failure :
It stops the user action. Cursor cannot be moved to next item until restriction is satisfied.
It is similar to raise_application_error in database trigger.
It stops the user action. Cursor cannot be moved to next item until restriction is satisfied.
It is similar to raise_application_error in database trigger.
Note
Items are referred with : ( COLON ) before them in coding.
Items are referred with : ( COLON ) before them in coding.
So, we have create two trigger. Both
are Item level triggers.
One trigger in ENAME item.
Another trigger in SAL item.
One trigger in ENAME item.
Another trigger in SAL item.
form60
Program à Compile Module
Program à Run Form
Program à Run Form
In the form run time environment (
Oracle Application Server Form Services )
Enter Empno – 101
Keep the cursor on ENAME ( do not enter anything)
Keep the cursor on JOB item, we get a message “Employee name cannot be blank”
Enter Empno – 101
Keep the cursor on ENAME ( do not enter anything)
Keep the cursor on JOB item, we get a message “Employee name cannot be blank”
form61
Click on OK à Cursor will be
back on ENAME item.
That is the advantage of raise form_trigger_failure.
Cursor cannot be moved to next item until restriction is satisfied.
That is the advantage of raise form_trigger_failure.
Cursor cannot be moved to next item until restriction is satisfied.
Lets check the trigger on SAL Item.
Enter SAL value 2000, we get message “Employee salary must not be less than 3000”.
Enter SAL value 2000, we get message “Employee salary must not be less than 3000”.
form62
Order of Execution of triggers at
runtime
Hierarchy of Form level triggers
Hierarchy of Form level triggers
- ON-LOGON
- PRE-FORM
- WHEN-NEW-FORM-INSTANCE
- POST-FORM
- ON-LOGOUT
Hierarchy of block
level triggers
- PRE-BLOCK
- WHEN-NEW-BLOCK-INSTANCE
- POST-BLOCK
Hierarchy of Item
level triggers
- PRE-TEXT-ITEM
- WHEN-NEW-ITEM-INSTANCE
- KEY-NEXT-ITEM ( TAB )
- POST-TEXT-ITEM
System Varialbe in forms
System Variables Tutorial in ORACLE FORMS DEVELOPER:
System Variables:
1. About System Variables
A system variable is an Oracle Forms variable that keeps track of an internal Oracle Forms state. You can reference the value of a system variable to control the way an application behaves.
Oracle Forms maintains the values of system variables on a per form basis. That is, the values of all system variables correspond only to the current form. The following list presents the names of the available system variables
1.1 List of system variables
1.1.1 SYSTEM.BLOCK_STATUS
Represents the status of the block where the cursor is located, or the current block during trigger processing. The value can be one of three character strings:
• CHANGED -Indicates that the block contains at least one Changed record.
• NEW - Indicates that the block contains only New records.
• QUERY - Indicates that the block contains only Valid records that have been retrieved from the database.
*** Example of thread:
1. How to change the block or form status to 'insert' in oracle forms 10g using when-button-pressed trigger. I want to do the same thing like the 'insert record' button in the default tool bar but using a custom button
any ideas?
May be answer:
Initial answer:insert' is not a valid block or form status - only records can be marked for insert. The form can be in 'changed' status though.
1.2 SYSTEM.COORDINATION_OPERATION
This system variable works with its companion SYSTEM.MASTER_BLOCK to help an On-Clear-Details trigger determine what type of coordination-causing operation fired the trigger, and on which master block of a master-detail relation.
1.1.3 SYSTEM.CURRENT_BLOCK
The value that the SYSTEM.CURRENT_BLOCK system variable represents depends on the current navigation unit:
• If the current navigation unit is the block, record, or item (as in the Pre- and Post- Item, Record, and Block triggers), the value of SYSTEM.CURRENT_BLOCK is the name of the block that Oracle Forms is processing or that the cursor is in.
• If the current navigation unit is the form (as in the Pre- and Post-Form triggers), the value of SYSTEM.CURRENT_BLOCK is NULL.
The value is always a character string.
Note: SYSTEM.CURRENT_BLOCK is included for compatibility with previous versions. Oracle Corporation recommends that you use SYSTEM.CURSOR_BLOCK and SYSTEM.TRIGGER_BLOCK instead.
1.1.4 SYSTEM.CURRENT_DATETIME
Is a variable representing the operating system date. The value is a CHAR string in the following format:
DD-MON-YYYY HH24:MI:SS
1.1.5 SYSTEM.CURRENT_FORM
Represents the name of the form that Oracle Forms is executing. The value is always a character string.
1.1.6 SYSTEM.CURRENT_ITEM
The value that the SYSTEM.CURRENT_ITEM system variable represents depends on the current navigation unit:
• If the current navigation unit is the item (as in the Pre- and Post-Item triggers), the value of SYSTEM.CURRENT_ITEM is the name of the item that Oracle Forms is processing or that the cursor is in. The returned item name does not include a block name prefix.
• If the current navigation unit is the record, block, or form (as in the Pre- and Post- Record, Block, and Form triggers), the value of SYSTEM.CURRENT_ITEM is NULL.
The value is always a character string.
Note: SYSTEM.CURRENT_ITEM is included for compatibility with previous versions. Oracle Corporation recommends that you use SYSTEM.CURSOR_ITEM or SYSTEM.TRIGGER_ITEM instead.
1.1.7 SYSTEM.CURRENT_VALUE
Represents the value of the item that is registered in SYSTEM.CURRENT_ITEM.
The value is always a character string.
Note: SYSTEM.CURRENT_VALUE is included for compatibility with previous versions. Oracle Corporation recommends that you use SYSTEM.CURSOR_ITEM and SYSTEM.CURSOR_VALUE instead.
1.1.8 SYSTEM.CURSOR_BLOCK
The value that the SYSTEM.CURSOR_BLOCK system variable represents depends on the current navigation unit:
• If the current navigation unit is the block, record, or item (as in the Pre- and Post- Item, Record, and Block triggers), the value of SYSTEM.CURSOR_BLOCK is the name of the block where the cursor is located. The value is always a character string.
• If the current navigation unit is the form (as in the Pre- and Post-Form triggers), the value of SYSTEM.CURSOR_BLOCK is NULL.
1.1.9 SYSTEM.CURSOR_ITEM
Represents the name of the block and item, block.item, where the input focus (cursor) is located.
The value is always a character string.
1.1.10 SYSTEM.CURSOR_RECORD
Represents the number of the record where the cursor is located. This number represents the record's current physical order in the block's list of records. The value is always a character string.
1.1.11 SYSTEM.CURSOR_VALUE
Represents the value of the item where the cursor is located. The value is always a character string.
1.1.12 SYSTEM.CUSTOM_ITEM_EVENT
Stores the name of the event fired by a VBX control.
1.1.13 SYSTEM.CUSTOM_ITEM_EVENT_PARAMETERS
Stores the supplementary arguments for an event fired by a VBX control.
1.1.14 SYSTEM.DATE_THRESHOLD*
Represents the database date requery threshold. This variable works in conjunction with the three system variables $$DBDATE$$, $$DBDATETIME$$, and $$DBTIME$$, and controls how often Oracle Forms synchronizes the database date with the RDBMS. The value of this variable must be specified in the following format: MI:SS .
Because frequent RDBMS queries can degrade performance, it is best to keep this value reasonably high. However, keep in mind that if the value is not synchronized often enough, some time discrepancy can occur. In addition, if you are building a client-server application, the performance implications of SYSTEM.DATE_THRESHOLD could vary depending on the complexity of your network configuration.
1.1.15 SYSTEM.EFFECTIVE_DATE*
Represents the effective database date. The variable value must always be in the following format:
DD-MON-YYYY HH24:MI:SS .
1.1.16 SYSTEM.EVENT_WINDOW
The SYSTEM.EVENT_WINDOW system variable represents the name of the last window that was affected by an action that caused one of the window event triggers to fire. The following triggers cause this variable to be updated:
• WHEN-WINDOW-ACTIVATED
• WHEN-WINDOW-CLOSED
• WHEN-WINDOW-DEACTIVATED
• WHEN-WINDOW-RESIZED
From within these triggers, you can assign the value of the variable to any of the following:
• global variable
• parameter
• variable
• item, including a null canvas item
1.1.17 SYSTEM.FORM_STATUS
Represents the status of the current form. The value can be one of three character strings:
• CHANGED - Indicates that the form contains at least one block with a Changed record. The value of SYSTEM.FORM_STATUS becomes CHANGED only after at least one record in the form has been changed and the associated navigation unit has also changed.
• NEW - Indicates that the form contains only New records.
• QUERY - Indicates that a query is open. The form contains at least one block with QUERY records and no blocks with CHANGED records.
Example : if :system.form_status = 'CHANGED' THEN
COMMIT_FORM;
END IF;
2.1.1.18 SYSTEM.LAST_FORM
Represents the form module ID of the previous form in a multi-form application, where multiple forms have been invoked using OPEN_FORM. The value can be one of two character strings: either the form module ID or NULL.
2.1.1.19 SYSTEM.LAST_QUERY
Represents the query SELECT statement that Oracle Forms most recently used to populate a block during the current Run form session. The value is always a character string.
2.1.1.20 SYSTEM.LAST_RECORD
Indicates whether the current record is the last record in a block's list of records. The value is one of the following two CHAR values:
• TRUE - Indicates that the current record is the last record in the current block's list of records.
• FALSE - Indicates that the current record is not the last record in the current block's list of records.
2.1.1.21 SYSTEM.MASTER_BLOCK
This system variable works with its companion SYSTEM.COORDINATION_OPERATION to help an On-Clear-Details trigger determine what type of coordination-causing operation fired the trigger, and on which master block of a master-detail relation. The values of the two system variables remain constant throughout the clearing phase of any block synchronization. SYSTEM.MASTER_BLOCK represents the name of the driving master block, and SYSTEM.COORDINATION_OPERATION represents the coordination-causing event that occurred on the master block.
More details are in the description for SYSTEM.COORDINATION_OPERATION.
2.1.1.22 SYSTEM.MESSAGE_LEVEL*
Represents one of the following message severity levels: 0, 5, 10, 15, 20, or 25. The value is always a character string.
During a Runform session, Oracle Forms suppresses all messages with a severity level that is the same or lower (less severe) than the indicated severity level.
Assign a value to the SYSTEM.MESSAGE_LEVEL system variable with standard PL/SQL syntax:
:System.Message_Level := value;
The legal values for SYSTEM.MESSAGE_LEVEL are 0, 5, 10, 15, 20, and 25. Oracle Forms does not suppress prompts or vital error messages, no matter what severity level you select.
2.1.1.23 SYSTEM.MODE
SYSTEM.MODE indicates whether the form is in Normal, Enter Query, or Fetch Processing mode. The value is always a character string.
• NORMAL - Indicates that the form is currently in normal processing mode.
• ENTER-QUERY - Indicates that the form is currently in Enter Query mode.
• QUERY - Indicates that the form is currently in fetch processing mode, meaning that a query is currently being processed.
2.1.1.24 SYSTEM.MOUSE_BUTTON_PRESSED
Indicates the number of the button that was clicked. Mouse button support is limited to buttons 1 and 2 (left or middle) on a three button mouse. The value is always a character string.
2.1.1.25 SYSTEM.MOUSE_BUTTON_SHIFT_STATE
Indicates the key that was pressed during the click, such as SHIFT, ALT, or CONTROL. The value is always a character string.
2.1.1.26 SYSTEM.MOUSE_CANVAS
If the mouse is in a canvas, SYSTEM.MOUSE_CANVAS represents the name of that canvas as a CHAR value. If the mouse is in an item, this variable represents the name of the canvas containing the item.
SYSTEM.MOUSE_CANVAS is NULL if:
• the mouse is not in a canvas
• the platform is non-GUI
2.1.1.27 SYSTEM.MOUSE_FORM
If the mouse is in a form module, SYSTEM.MOUSE_FORM represents the name of that form module as a CHAR value. For example, if the mouse is in Form_Module1, the value for SYSTEM.MOUSE_ITEM is FORM_MODULE1.
Note: SYSTEM.MOUSE_FORM is NULL if the platform is not a GUI platform.
2.1.1.28 SYSTEM.MOUSE_ITEM
If the mouse is in an item, SYSTEM.MOUSE_ITEM represents the name of that item as a CHAR value. For example, if the mouse is in Item1 in Block2, the value for SYSTEM.MOUSE_ITEM is :BLOCK2.ITEM1.
SYSTEM.MOUSE_ITEM is NULL if:
• the mouse is not in an item
• the platform is not a GUI platform
2.1.1.29 SYSTEM.MOUSE_RECORD
If the mouse is in a record, SYSTEM.MOUSE_RECORD represents that record's record number as a CHAR value.
Note: SYSTEM.MOUSE_RECORD is 0 if the mouse is not in an item (and thus, not in a record).
2.1.1.30 SYSTEM.MOUSE_RECORD_OFFSET
If the mouse is in a record, SYSTEM.MOUSE_RECORD_OFFSET represents the offset from the first visible record as a CHAR value.
For example, if the mouse is in the second of five visible records in a multi-record block, SYSTEM.MOUSE_RECORD_OFFSET is 2. (SYSTEM.MOUSE_RECORD_OFFSET uses a 1-based index).
Note: SYSTEM.MOUSE_RECORD_OFFSET is 0 if the mouse is not in an item (and thus, not in a record).
2.1.1.31 SYSTEM.MOUSE_X_POS
Represents (as a CHAR value) the x coordinate of the mouse in the units of the current form coordinate system. If the mouse is in an item, the value is relative to the upper left corner of the item's bounding box. If the mouse is on a canvas, the value is relative to the upper left corner of the canvas.
Note: SYSTEM.MOUSE_X_POS is always NULL on character mode platforms.
2.1.1.32 SYSTEM.MOUSE_Y_POS
Represents (as a CHAR value) the y coordinate of the mouse, using units of the current coordinate system. If the mouse is in an item, the value is relative to the upper left corner of the item's bounding box. If the mouse is on a canvas, the value is relative to the upper left corner of the canvas.
Note: SYSTEM.MOUSE_Y_POS is always NULL on character mode platforms.
2.1.1.33 SYSTEM.RECORD_STATUS
Represents the status of the record where the cursor is located. The value can be one of four character strings:
• CHANGED - Indicates that a queried record's validation status is Changed.
• INSERT - Indicates that the record's validation status is Changed and that the record does not exist in the database.
• NEW - Indicates that the record's validation status is New.
• QUERY - Indicates that the record's validation status is Valid and that it was retrieved from the database.
2.1.1.34 SYSTEM.SUPPRESS_WORKING*
suppresses the "Working..." message in Runform, in order to prevent the screen update usually caused by the display of the "Working..." message. The value of the variable is one of the following two CHAR values:
• TRUE - Prevents Oracle Forms from issuing the "Working..." message.
• FALSE - Allows Oracle Forms to continue to issue the "Working..." message.
2.1.1.35 SYSTEM.TRIGGER_BLOCK
Represents the name of the block where the cursor was located when the current trigger initially fired. The value is NULL if the current trigger is a Pre- or Post-Form trigger. The value is always a character string.
2.1.1.36 SYSTEM.TRIGGER_ITEM
Represents the item (BLOCK.ITEM) in the scope for which the trigger is currently firing. When referenced in a key trigger, it represents the item where the cursor was located when the trigger began. The value is always a character string.
2.1.1.37 SYSTEM.TRIGGER_RECORD
Represents the number of the record that Oracle Forms is processing. This number represents the record's current physical order in the block's list of records. The value is always a character string.
All system variables, except the four indicated with an asterisk (*), are read-only variables. These four variables are the only system variables to which you can explicitly assign values.
2.1.2 Date and Time System Default Values
Oracle Forms also supplies six special default values -- $$DATE$$, $$DATETIME$$, $$TIME$$, $$DBDATE$$, $$DBDATETIME$$, and $$DBTIME$$ -- that supply date and time information and have special restrictions on their use:
• If you're building client/server applications, consider the performance implications of going across the network to get date and time information.
• If you're accessing a non-ORACLE datasource, avoid using $$DBDATE$$ and $$DBDATETIME$$. Instead, use a When-Create-Record trigger to select the current date in a datasource-specific manner.
• Use $$DATE$$, $$DATETIME$$, and $$TIME$$ to obtain the local system date/time; use $$DBDATE$$, $$DBDATETIME$$, and $$DBTIME$$ to obtain the database date/time, which may differ from the local system date/time if, for example, you're connecting to a remote database in a different time zone.
• Use these variables only to set the value of the Default Value, Range Low Value or Range High Value property.
2.1.2.1 $$DATE$$
Retrieves the current operating system date. You can use $$DATE$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR, DATE, or DATETIME data type. You also can use $$DATE$$ as a default value for form parameters. In this case, the parameter's value is computed once, at form startup.
2.1.2.2 $$DATETIME$$
Retrieves the current operating system date and time. You can use $$DATETIME$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR or DATETIME data type. You also can use $$DATETIME$$ as a default value for form parameters. In this case, the parameter's value is computed once, at form startup.
The difference between $$DATE$$ and $$DATETIME$$ is that the time component for $$DATE$$ is always fixed to 00:00:00, compared to $$DATETIME$$, which includes a meaningful time component, such as 09:17:59.
Note: Do not use $$DATETIME$$ instead of $$DATE$$ unless you plan to specify the time component. If, for example, you use $$DATETIME$$ with the default DATE format mask of DD-MON-YY, you would be committing values to the database that the user would not see, because the format mask does not include a time component. Then, because you had committed specific time information, when you later queried on date, the values would not match and you would not return any rows.
2.1.2.3 $$DBDATE$$
Retrieves the current database date. You can use $$DBDATE$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR, DATE, or DATETIME data type.
2.1.2.4 $$DBDATETIME$$
Retrieves the current date and time from the local database. You can use $$DBDATETIME$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR or DATETIME data type.
2.1.2.5 $$DBTIME$$
Retrieves the current time from the local database. You can use $$DBTIME$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR or TIME data type.
2.1.2.6 $$TIME$$
Retrieves the current operating system time. You can use $$TIME$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR or TIME data type.
You also can use $$TIME$$ as a default value for form parameters. In this case, the parameter's value is computed once, at form startup.
2.1.3 Local Variables
Because system variables are derived, if the value is not expected to change over the life of the trigger, you can save the system value in a local variable and use the local variable multiple times
System Variables:
1. About System Variables
A system variable is an Oracle Forms variable that keeps track of an internal Oracle Forms state. You can reference the value of a system variable to control the way an application behaves.
Oracle Forms maintains the values of system variables on a per form basis. That is, the values of all system variables correspond only to the current form. The following list presents the names of the available system variables
1.1 List of system variables
1.1.1 SYSTEM.BLOCK_STATUS
Represents the status of the block where the cursor is located, or the current block during trigger processing. The value can be one of three character strings:
• CHANGED -Indicates that the block contains at least one Changed record.
• NEW - Indicates that the block contains only New records.
• QUERY - Indicates that the block contains only Valid records that have been retrieved from the database.
*** Example of thread:
1. How to change the block or form status to 'insert' in oracle forms 10g using when-button-pressed trigger. I want to do the same thing like the 'insert record' button in the default tool bar but using a custom button
any ideas?
May be answer:
Initial answer:insert' is not a valid block or form status - only records can be marked for insert. The form can be in 'changed' status though.
1.2 SYSTEM.COORDINATION_OPERATION
This system variable works with its companion SYSTEM.MASTER_BLOCK to help an On-Clear-Details trigger determine what type of coordination-causing operation fired the trigger, and on which master block of a master-detail relation.
1.1.3 SYSTEM.CURRENT_BLOCK
The value that the SYSTEM.CURRENT_BLOCK system variable represents depends on the current navigation unit:
• If the current navigation unit is the block, record, or item (as in the Pre- and Post- Item, Record, and Block triggers), the value of SYSTEM.CURRENT_BLOCK is the name of the block that Oracle Forms is processing or that the cursor is in.
• If the current navigation unit is the form (as in the Pre- and Post-Form triggers), the value of SYSTEM.CURRENT_BLOCK is NULL.
The value is always a character string.
Note: SYSTEM.CURRENT_BLOCK is included for compatibility with previous versions. Oracle Corporation recommends that you use SYSTEM.CURSOR_BLOCK and SYSTEM.TRIGGER_BLOCK instead.
1.1.4 SYSTEM.CURRENT_DATETIME
Is a variable representing the operating system date. The value is a CHAR string in the following format:
DD-MON-YYYY HH24:MI:SS
1.1.5 SYSTEM.CURRENT_FORM
Represents the name of the form that Oracle Forms is executing. The value is always a character string.
1.1.6 SYSTEM.CURRENT_ITEM
The value that the SYSTEM.CURRENT_ITEM system variable represents depends on the current navigation unit:
• If the current navigation unit is the item (as in the Pre- and Post-Item triggers), the value of SYSTEM.CURRENT_ITEM is the name of the item that Oracle Forms is processing or that the cursor is in. The returned item name does not include a block name prefix.
• If the current navigation unit is the record, block, or form (as in the Pre- and Post- Record, Block, and Form triggers), the value of SYSTEM.CURRENT_ITEM is NULL.
The value is always a character string.
Note: SYSTEM.CURRENT_ITEM is included for compatibility with previous versions. Oracle Corporation recommends that you use SYSTEM.CURSOR_ITEM or SYSTEM.TRIGGER_ITEM instead.
1.1.7 SYSTEM.CURRENT_VALUE
Represents the value of the item that is registered in SYSTEM.CURRENT_ITEM.
The value is always a character string.
Note: SYSTEM.CURRENT_VALUE is included for compatibility with previous versions. Oracle Corporation recommends that you use SYSTEM.CURSOR_ITEM and SYSTEM.CURSOR_VALUE instead.
1.1.8 SYSTEM.CURSOR_BLOCK
The value that the SYSTEM.CURSOR_BLOCK system variable represents depends on the current navigation unit:
• If the current navigation unit is the block, record, or item (as in the Pre- and Post- Item, Record, and Block triggers), the value of SYSTEM.CURSOR_BLOCK is the name of the block where the cursor is located. The value is always a character string.
• If the current navigation unit is the form (as in the Pre- and Post-Form triggers), the value of SYSTEM.CURSOR_BLOCK is NULL.
1.1.9 SYSTEM.CURSOR_ITEM
Represents the name of the block and item, block.item, where the input focus (cursor) is located.
The value is always a character string.
1.1.10 SYSTEM.CURSOR_RECORD
Represents the number of the record where the cursor is located. This number represents the record's current physical order in the block's list of records. The value is always a character string.
1.1.11 SYSTEM.CURSOR_VALUE
Represents the value of the item where the cursor is located. The value is always a character string.
1.1.12 SYSTEM.CUSTOM_ITEM_EVENT
Stores the name of the event fired by a VBX control.
1.1.13 SYSTEM.CUSTOM_ITEM_EVENT_PARAMETERS
Stores the supplementary arguments for an event fired by a VBX control.
1.1.14 SYSTEM.DATE_THRESHOLD*
Represents the database date requery threshold. This variable works in conjunction with the three system variables $$DBDATE$$, $$DBDATETIME$$, and $$DBTIME$$, and controls how often Oracle Forms synchronizes the database date with the RDBMS. The value of this variable must be specified in the following format: MI:SS .
Because frequent RDBMS queries can degrade performance, it is best to keep this value reasonably high. However, keep in mind that if the value is not synchronized often enough, some time discrepancy can occur. In addition, if you are building a client-server application, the performance implications of SYSTEM.DATE_THRESHOLD could vary depending on the complexity of your network configuration.
1.1.15 SYSTEM.EFFECTIVE_DATE*
Represents the effective database date. The variable value must always be in the following format:
DD-MON-YYYY HH24:MI:SS .
1.1.16 SYSTEM.EVENT_WINDOW
The SYSTEM.EVENT_WINDOW system variable represents the name of the last window that was affected by an action that caused one of the window event triggers to fire. The following triggers cause this variable to be updated:
• WHEN-WINDOW-ACTIVATED
• WHEN-WINDOW-CLOSED
• WHEN-WINDOW-DEACTIVATED
• WHEN-WINDOW-RESIZED
From within these triggers, you can assign the value of the variable to any of the following:
• global variable
• parameter
• variable
• item, including a null canvas item
1.1.17 SYSTEM.FORM_STATUS
Represents the status of the current form. The value can be one of three character strings:
• CHANGED - Indicates that the form contains at least one block with a Changed record. The value of SYSTEM.FORM_STATUS becomes CHANGED only after at least one record in the form has been changed and the associated navigation unit has also changed.
• NEW - Indicates that the form contains only New records.
• QUERY - Indicates that a query is open. The form contains at least one block with QUERY records and no blocks with CHANGED records.
Example : if :system.form_status = 'CHANGED' THEN
COMMIT_FORM;
END IF;
2.1.1.18 SYSTEM.LAST_FORM
Represents the form module ID of the previous form in a multi-form application, where multiple forms have been invoked using OPEN_FORM. The value can be one of two character strings: either the form module ID or NULL.
2.1.1.19 SYSTEM.LAST_QUERY
Represents the query SELECT statement that Oracle Forms most recently used to populate a block during the current Run form session. The value is always a character string.
2.1.1.20 SYSTEM.LAST_RECORD
Indicates whether the current record is the last record in a block's list of records. The value is one of the following two CHAR values:
• TRUE - Indicates that the current record is the last record in the current block's list of records.
• FALSE - Indicates that the current record is not the last record in the current block's list of records.
2.1.1.21 SYSTEM.MASTER_BLOCK
This system variable works with its companion SYSTEM.COORDINATION_OPERATION to help an On-Clear-Details trigger determine what type of coordination-causing operation fired the trigger, and on which master block of a master-detail relation. The values of the two system variables remain constant throughout the clearing phase of any block synchronization. SYSTEM.MASTER_BLOCK represents the name of the driving master block, and SYSTEM.COORDINATION_OPERATION represents the coordination-causing event that occurred on the master block.
More details are in the description for SYSTEM.COORDINATION_OPERATION.
2.1.1.22 SYSTEM.MESSAGE_LEVEL*
Represents one of the following message severity levels: 0, 5, 10, 15, 20, or 25. The value is always a character string.
During a Runform session, Oracle Forms suppresses all messages with a severity level that is the same or lower (less severe) than the indicated severity level.
Assign a value to the SYSTEM.MESSAGE_LEVEL system variable with standard PL/SQL syntax:
:System.Message_Level := value;
The legal values for SYSTEM.MESSAGE_LEVEL are 0, 5, 10, 15, 20, and 25. Oracle Forms does not suppress prompts or vital error messages, no matter what severity level you select.
2.1.1.23 SYSTEM.MODE
SYSTEM.MODE indicates whether the form is in Normal, Enter Query, or Fetch Processing mode. The value is always a character string.
• NORMAL - Indicates that the form is currently in normal processing mode.
• ENTER-QUERY - Indicates that the form is currently in Enter Query mode.
• QUERY - Indicates that the form is currently in fetch processing mode, meaning that a query is currently being processed.
2.1.1.24 SYSTEM.MOUSE_BUTTON_PRESSED
Indicates the number of the button that was clicked. Mouse button support is limited to buttons 1 and 2 (left or middle) on a three button mouse. The value is always a character string.
2.1.1.25 SYSTEM.MOUSE_BUTTON_SHIFT_STATE
Indicates the key that was pressed during the click, such as SHIFT, ALT, or CONTROL. The value is always a character string.
2.1.1.26 SYSTEM.MOUSE_CANVAS
If the mouse is in a canvas, SYSTEM.MOUSE_CANVAS represents the name of that canvas as a CHAR value. If the mouse is in an item, this variable represents the name of the canvas containing the item.
SYSTEM.MOUSE_CANVAS is NULL if:
• the mouse is not in a canvas
• the platform is non-GUI
2.1.1.27 SYSTEM.MOUSE_FORM
If the mouse is in a form module, SYSTEM.MOUSE_FORM represents the name of that form module as a CHAR value. For example, if the mouse is in Form_Module1, the value for SYSTEM.MOUSE_ITEM is FORM_MODULE1.
Note: SYSTEM.MOUSE_FORM is NULL if the platform is not a GUI platform.
2.1.1.28 SYSTEM.MOUSE_ITEM
If the mouse is in an item, SYSTEM.MOUSE_ITEM represents the name of that item as a CHAR value. For example, if the mouse is in Item1 in Block2, the value for SYSTEM.MOUSE_ITEM is :BLOCK2.ITEM1.
SYSTEM.MOUSE_ITEM is NULL if:
• the mouse is not in an item
• the platform is not a GUI platform
2.1.1.29 SYSTEM.MOUSE_RECORD
If the mouse is in a record, SYSTEM.MOUSE_RECORD represents that record's record number as a CHAR value.
Note: SYSTEM.MOUSE_RECORD is 0 if the mouse is not in an item (and thus, not in a record).
2.1.1.30 SYSTEM.MOUSE_RECORD_OFFSET
If the mouse is in a record, SYSTEM.MOUSE_RECORD_OFFSET represents the offset from the first visible record as a CHAR value.
For example, if the mouse is in the second of five visible records in a multi-record block, SYSTEM.MOUSE_RECORD_OFFSET is 2. (SYSTEM.MOUSE_RECORD_OFFSET uses a 1-based index).
Note: SYSTEM.MOUSE_RECORD_OFFSET is 0 if the mouse is not in an item (and thus, not in a record).
2.1.1.31 SYSTEM.MOUSE_X_POS
Represents (as a CHAR value) the x coordinate of the mouse in the units of the current form coordinate system. If the mouse is in an item, the value is relative to the upper left corner of the item's bounding box. If the mouse is on a canvas, the value is relative to the upper left corner of the canvas.
Note: SYSTEM.MOUSE_X_POS is always NULL on character mode platforms.
2.1.1.32 SYSTEM.MOUSE_Y_POS
Represents (as a CHAR value) the y coordinate of the mouse, using units of the current coordinate system. If the mouse is in an item, the value is relative to the upper left corner of the item's bounding box. If the mouse is on a canvas, the value is relative to the upper left corner of the canvas.
Note: SYSTEM.MOUSE_Y_POS is always NULL on character mode platforms.
2.1.1.33 SYSTEM.RECORD_STATUS
Represents the status of the record where the cursor is located. The value can be one of four character strings:
• CHANGED - Indicates that a queried record's validation status is Changed.
• INSERT - Indicates that the record's validation status is Changed and that the record does not exist in the database.
• NEW - Indicates that the record's validation status is New.
• QUERY - Indicates that the record's validation status is Valid and that it was retrieved from the database.
2.1.1.34 SYSTEM.SUPPRESS_WORKING*
suppresses the "Working..." message in Runform, in order to prevent the screen update usually caused by the display of the "Working..." message. The value of the variable is one of the following two CHAR values:
• TRUE - Prevents Oracle Forms from issuing the "Working..." message.
• FALSE - Allows Oracle Forms to continue to issue the "Working..." message.
2.1.1.35 SYSTEM.TRIGGER_BLOCK
Represents the name of the block where the cursor was located when the current trigger initially fired. The value is NULL if the current trigger is a Pre- or Post-Form trigger. The value is always a character string.
2.1.1.36 SYSTEM.TRIGGER_ITEM
Represents the item (BLOCK.ITEM) in the scope for which the trigger is currently firing. When referenced in a key trigger, it represents the item where the cursor was located when the trigger began. The value is always a character string.
2.1.1.37 SYSTEM.TRIGGER_RECORD
Represents the number of the record that Oracle Forms is processing. This number represents the record's current physical order in the block's list of records. The value is always a character string.
All system variables, except the four indicated with an asterisk (*), are read-only variables. These four variables are the only system variables to which you can explicitly assign values.
2.1.2 Date and Time System Default Values
Oracle Forms also supplies six special default values -- $$DATE$$, $$DATETIME$$, $$TIME$$, $$DBDATE$$, $$DBDATETIME$$, and $$DBTIME$$ -- that supply date and time information and have special restrictions on their use:
• If you're building client/server applications, consider the performance implications of going across the network to get date and time information.
• If you're accessing a non-ORACLE datasource, avoid using $$DBDATE$$ and $$DBDATETIME$$. Instead, use a When-Create-Record trigger to select the current date in a datasource-specific manner.
• Use $$DATE$$, $$DATETIME$$, and $$TIME$$ to obtain the local system date/time; use $$DBDATE$$, $$DBDATETIME$$, and $$DBTIME$$ to obtain the database date/time, which may differ from the local system date/time if, for example, you're connecting to a remote database in a different time zone.
• Use these variables only to set the value of the Default Value, Range Low Value or Range High Value property.
2.1.2.1 $$DATE$$
Retrieves the current operating system date. You can use $$DATE$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR, DATE, or DATETIME data type. You also can use $$DATE$$ as a default value for form parameters. In this case, the parameter's value is computed once, at form startup.
2.1.2.2 $$DATETIME$$
Retrieves the current operating system date and time. You can use $$DATETIME$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR or DATETIME data type. You also can use $$DATETIME$$ as a default value for form parameters. In this case, the parameter's value is computed once, at form startup.
The difference between $$DATE$$ and $$DATETIME$$ is that the time component for $$DATE$$ is always fixed to 00:00:00, compared to $$DATETIME$$, which includes a meaningful time component, such as 09:17:59.
Note: Do not use $$DATETIME$$ instead of $$DATE$$ unless you plan to specify the time component. If, for example, you use $$DATETIME$$ with the default DATE format mask of DD-MON-YY, you would be committing values to the database that the user would not see, because the format mask does not include a time component. Then, because you had committed specific time information, when you later queried on date, the values would not match and you would not return any rows.
2.1.2.3 $$DBDATE$$
Retrieves the current database date. You can use $$DBDATE$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR, DATE, or DATETIME data type.
2.1.2.4 $$DBDATETIME$$
Retrieves the current date and time from the local database. You can use $$DBDATETIME$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR or DATETIME data type.
2.1.2.5 $$DBTIME$$
Retrieves the current time from the local database. You can use $$DBTIME$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR or TIME data type.
2.1.2.6 $$TIME$$
Retrieves the current operating system time. You can use $$TIME$$ to designate a default value or range for a text item using the Default or Range property. The text item must be of the CHAR or TIME data type.
You also can use $$TIME$$ as a default value for form parameters. In this case, the parameter's value is computed once, at form startup.
2.1.3 Local Variables
Because system variables are derived, if the value is not expected to change over the life of the trigger, you can save the system value in a local variable and use the local variable multiple times
-------When-new-item-instance;
Properties : Item level trigger .
Effect in form :
Image after use this trigger :
Time in Dhaka 
