i have a master and detail blocks in the detail block i have the date feild
i execute the data with the max date by pressing a button after execute query on the master table to get the information in the detail.
i need to be able to find data between dates but this if the user wanted
i remeber once that i saw a form that when i write :x in the date field it opens another window that i can write in it date between ... and ....
and then it retrives the data.
i'm not sure if this was programmed or it is a feature in oracle developer .
It is a standard feature of Forms that if the user enters text beginning with a colon while in Enter Query mode, a window pops up where they can enter their own criteria.
I wouldn't rely on it for an end-user production application though, because:
1) it assumes the users know SQL syntax well
2) it opens up your database to the "SQL injection" issue. For example, suppose your block is based on table DEPT, items DEPTNO and DNAME. A knowledgable user could modify the query so that it actually selected from EMP and gets EMPNO and SAL for example, by typing something like this into the pop-up box:
1=0) union all (select rowid, empno, to_char(sal) from emp
For that reason, on one project I worked on years ago we actually prevented the users from doing this by rejecting queries when the user entered a colon in any item! Of course, these days the data should be protected better in the server anyway, rather than relying on application screens to control access.
A more sophisticated way to provide different optional criteria is to have a control block where the user can specify optional values like "Earliest Start Date" and "Latest Start Date", and then use those to modify the default where clause.
Thank you so much for the reply,but can you tell how can i write the code.
i made two date feild in a control block called date_from and date_to
and i put a button and i wrote in the when button presssed trigger:
where code =:block1.seg_code and date between :control.stdatefrom and :control.stdateto;
when i run the form i execute query and th master block data appears .
then i write the between date and press the button ,the cursor goes to block2,but nothing happen.
Note:in this block i set the block property manually because i need the default values returned are the max date.is this may be the problem. which also appear when i execute the master and press a button.
No, you don't write the SELECT statement for the block; instead, you modify the DEFAULT_WHERE property for the block by writing a PRE-QUERY trigger on the block level like this:
v_where VARCHAR2(2000) := NVL(GET_BLOCK_PROPERTY('EMP',DEFAULT_WHERE),'1=1');
IF :ctrl.date_from IS NOT NULL THEN
v_where := v_where || ' AND hiredate >= :CTRL.DATE_FROM';
IF :ctrl.date_to IS NOT NULL THEN
v_where := v_where || ' AND hiredate <= :CTRL.DATE_TO';
This DEFAULT_WHERE property is then used in the query Forms generates.