Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    42

    Unanswered: Return between Date

    hi,
    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 .

    can any one help me in this issue.

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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:
    Code:
    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.

  3. #3
    Join Date
    Jun 2004
    Posts
    42
    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:
    begin
    go_block('block2');
    select
    code,
    desc,
    value,
    date
    into
    :block2.code,
    :block2.desc,
    :block2.value,
    :block2.date
    from table1,table2
    where code =:block1.seg_code and date between :control.stdatefrom and :control.stdateto;
    end;

    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.

    thanks

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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:
    Code:
    DECLARE
      v_where VARCHAR2(2000) := NVL(GET_BLOCK_PROPERTY('EMP',DEFAULT_WHERE),'1=1');
    BEGIN	
      IF :ctrl.date_from IS NOT NULL THEN
        v_where := v_where || ' AND hiredate >= :CTRL.DATE_FROM';
      END IF;
      IF :ctrl.date_to IS NOT NULL THEN
        v_where := v_where || ' AND hiredate <= :CTRL.DATE_TO';
      END IF;
      SET_BLOCK_PROPERTY('EMP',DEFAULT_WHERE,v_where);
    END;
    This DEFAULT_WHERE property is then used in the query Forms generates.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •