How Display messages from database to oracle form

hi
when i have a trigger in oracel form to execute database procedure
the database procedure having :
dbms_output for step info
or error message when raise trigger failur
or to confirm that proceduer compiled successfully
how i can display those 3 types of  messages in oracle form
NiceMan331Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helena Markováprogrammer-analystCommented:
There are ON-ERROR and ON-MESSAGE triggers for displaying all messages from database.

In the forms on-line help there is topic "Error Handling for Stored Procedures" where you can read how to display messages:
There are three primary methods for trapping ORACLE errors that are returned from the kernel during the processing of your PL/SQL code:
1.checking DBMS_ERROR_TEXT and DBMS_ERROR_CODE Built-in subprograms within a form-level ON-ERROR trigger
2.creating appropriate user-defined exceptions
3.evaluating the SQLCODE and SQLERRM functions in a WHEN OTHERS exception handler
NiceMan331Author Commented:
can you set the codes here ? or even the link of the documentation plz
Helena Markováprogrammer-analystCommented:
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NiceMan331Author Commented:
i didn't find any thing related to forms triggers
it is spoken about procedures and handling errors
Helena Markováprogrammer-analystCommented:
All about forms triggers - ON-ERROR and ON-MESSAGE triggers - is in the forms help in your Forms Builder.
NiceMan331Author Commented:
Can you copy the code for both triggers ?
flow01Commented:
1.  dbms_output for step info
   --   you can use the dbms_output,get_line procedure in oracle forms  to 'read'  the info that was 'stored' by the procedure that was executed
   procedure show_dbms_output
   is
     v_line varchar2(32767);
     status INTEGER;
   begin
     loop
       dbms_output.get_line(v_line,status);
       exit when status <> 0;
       message(v_line); -- -- the message can be to long to show
     end loop;
   end;
   
   (to get dbms_output from a procedure in oracle forms somewhere in that session you should have enable dbms_output)
or
2. error message when raise trigger failur
  -- if the procedure failes:  check the methods Henka mentioned
or
3. to confirm that procedurer compiled successfully
 -- I assume you created and thus compiled your procedure in advance , you probably want to confirm it executed successfully
    You will have to create your own message.
     
     
   you can think of something like
   
      ...
    DECLARE
      v_procedure_message varchar2(32767);  
    BEGIN
      -- dbms_output.enable(1000000);  -- uncomment if you did not do this else where , be aware : in initialises the output stack of dbms_output
      BEGIN
        your_procedure;
        v_procedure_message := 'Procedure has run succesfully';
        show_dbms_output;
        message(v_procedure_message);
      EXCEPTION WHEN OTHERS THEN  
        v_procedure_message := SQLERRM;  -- save the message to be able to show dbms_output first
        show_dbms_output;
        message(v_procedure_message);  -- the message can be to long to show
        RAISE;
      END;
    END;  
    ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
Thanx very much for the expanded explanation , I'll try it on Sunday cause the office closed already , thanx
NiceMan331Author Commented:
regarding this procedure :
procedure show_dbms_output
 i will create it in program unit of the form then i will execute it in the trigger before calling my procedure ?  or i will its cods  inside the database procedure ?
NiceMan331Author Commented:
ok now
regarding : showing dbms_output is ok
when proceduer success , ok
remaining when proceduer fail i will wait till i have some transactions has error to see if it will show error message or not
NiceMan331Author Commented:
YES ALSO OK
i tried one sample of procedure having error , and the error message displayed
thanx
now complete solved , just want to know , what "on_error" will do more here and what may be the code
flow01Commented:
" show_dbms_output"
The 'message' built-in is only available in forms .

"on_error" :  
What happened  with your test having a procedure error:  you get 1 or 2 error-messages ?
If 2 :  are the message the same ?
NiceMan331Author Commented:
I got one error message which on when others at the end of my database procedure
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.