4 Replies Latest reply: Nov 27, 2012 5:39 PM by user346369 RSS

    why is this code not working ?

    salute-Salem
      hi all ,
      i have a block named patients of form type , in which i insert patients data ,
      another block below (inserted_pat) ,this one of tabular type ,to show the data inserted after retrieving it again from the database .

      when-button-pressed , insert the data in the form block ,
      then
      go below block to execute the query ;

      i use this code , and it works well ,

      DECLARE
           V NUMBER(9) ;
           BEGIN
           if :system.block_status = 'CHANGED'
           THEN
                COMMIT ;
                END IF ;
           V := :PATIENTS.PAT_ID ;
                SET_BLOCK_PROPERTY('INSERTED_PAT',DEFAULT_WHERE,'pat_id='||v) ;
           GO_BLOCK('INSERTED_PAT') ;
           execute_query ;
           END ;

      but when i use it like so , it does not work ,

      DECLARE
           V NUMBER(9) ;
           BEGIN
           if :system.block_status = 'CHANGED'
           THEN
                COMMIT ;
                V := :PATIENTS.PAT_ID ;
                SET_BLOCK_PROPERTY('INSERTED_PAT',DEFAULT_WHERE,'pat_id='||v) ;
           GO_BLOCK('INSERTED_PAT') ;
           execute_query ;
                END IF ;
           
           END ;

      i want to know why the second way does not work , i think the two examples are the same , there is no difference .

      why is it not working ?

      thanks
        • 1. Re: why is this code not working ?
          Andreas Weiden
          You might check what value SYSTEM.BLOCK_STATUS has. I would recommend using SYSTEM.FORM_STATUS instead.
          • 2. Re: why is this code not working ?
            salute-Salem
            thank you for your prompt reply , but i do not know how to do the task ,
            because i already did it , but i just want to know why it did not work to understand the
            situation correctly to avoid any problems happen in other codes .
            • 3. Re: why is this code not working ?
              MLBrown
              The first bit of code commits the record only if the block is changed then builds the where clause and executes it:
              DECLARE
                V     NUMBER (9);
              BEGIN
                if :system.block_status = 'CHANGED' THEN
                  COMMIT;
                END IF;
              
                V := :PATIENTS.PAT_ID;
                SET_BLOCK_PROPERTY ('INSERTED_PAT', DEFAULT_WHERE, 'pat_id=' || v);
                GO_BLOCK ('INSERTED_PAT');
                execute_query;
              END;
              The second bit of code will commit records if they are changed, but will only build the where clause and query if the record is changed. If the record was not in a changed status because it was queried, then the query portion will not work.
              DECLARE
                V     NUMBER (9);
              BEGIN
                if :system.block_status = 'CHANGED' THEN
                  COMMIT;
                  V := :PATIENTS.PAT_ID;
                  SET_BLOCK_PROPERTY ('INSERTED_PAT', DEFAULT_WHERE, 'pat_id=' || v);
                  GO_BLOCK ('INSERTED_PAT');
                  execute_query;
                END IF;
              end;
              • 4. Re: why is this code not working ?
                user346369
                I would not do this:
                SET_BLOCK_PROPERTY ('INSERTED_PAT', DEFAULT_WHERE, 'pat_id=' || v);
                Why not remove that line, and just put this in the INSERTED_PAT block's Where Clause property:
                pat_id = :PATIENTS.PAT_ID
                Doing that would make the query reusable by Oracle, since it uses a bind variable rather than a new literal value in every query. Using bind variables allows Oracle to parse the query once, and then execute the query multiple times.