This discussion is archived
4 Replies Latest reply: Nov 27, 2012 3:39 PM by user346369 RSS

why is this code not working ?

newbi_egy Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 ?
    newbi_egy Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points