CODEWITHSUNDEEP
oracle-databasealertoracleforms
Parco Guan
Parco Guan

Reputation: 33

oracle Form Alert syntax

I want show alert when the Investor has not any transaction

Tables:

Investor    
Investor_Number PK  Number (7,0)    Not Null
    First_Name      Varchar2 (25)   Not Null
    Last_Name       Varchar2 (30)   Not Null
    Account_Number      Number (7,0)        FK  Not Null

Portfolio   
    Portfolio_Number    PK  Number (7,0)    Not Null
    Investor_Number     Number (7,0)        FK  Not Null
    Portfolio_Description       Varchar2 (50)   Null

Transaction 
    Portfolio_Number    PK  Number (7,0)        FK
    Stock_Code      Varchar2 (10)       FK
    Transaction_Date        Date
    Exchange_Code       Varchar2 (4)        FK
    Broker_Number       Number (7,0)        FK
    Buy_Sell        Char (1)


DECLARE

    V_count number;
BEGIN

select count(portfolio_number)
into V_count
FROM Transaction
where portfolio_number = :PORTFOLIO.portfolio_number;

    If  V_count = null then 
        Message('No Transaction');
    End if;
END;

The code above not work, i don't know the syntax.

now I am using post-query at block level, when the Investor have not any Transaction will show an alert. Two block display "Investor" and "Portfolio". How it work is when click the next record, the record if has not any transaction, it will pop up an alert.

Upvotes: 0

Views: 445

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Supposing that query you wrote works correctly, then two changes to be made:

  • COUNT won't return NULL; even if it would, you'd use if v_count is null, not if v_count = null
  • two consecutive MESSAGE calls will force Forms to display the message in a pop-up window on the screen; if you use only one MESSAGE, it'll be displayed at the bottom of the screen, in its status line
    • alternatively, see how real alerts work; but yes - 2 MESSAGEs is simpler
DECLARE
  V_count number;
BEGIN
  select count(portfolio_number)
    into V_count
    FROM Transaction
    where portfolio_number = :PORTFOLIO.portfolio_number;

  If  V_count = 0 then           --> COUNT never returns NULL
     Message('No Transaction');  --> 2 consecutive MESSAGE calls to "simulate" an alert
     Message('No Transaction');   
  else
     Message('Number of transactions = ' || to_char(v_count));   
     Message('Number of transactions = ' || to_char(v_count));   
  End if;
END;

Upvotes: 1

Related Questions