Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Error message in PL/SQL package to APEX page (APEX 5.1)
Error message in PL/SQL package to APEX page [message #675611] Tue, 09 April 2019 18:51 Go to next message
Janning
Messages: 36
Registered: April 2016
Member

I have an APEX page that calls a stored procedure in a package. When an error occurs (ie TOO_MANY_ROWS) I want a message to appear on the APEX page.
I tried using raise application, but this did not work. What are my options?
Re: Error message in PL/SQL package to APEX page [message #675628 is a reply to message #675611] Wed, 10 April 2019 10:46 Go to previous message
Littlefoot
Messages: 21514
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Add yet another parameter (OUT) to the procedure; it'll return a custom message. For example:

SQL> create or replace procedure p_test (par_deptno in emp.deptno%type,
  2                                      par_msg   out varchar2)
  3  is
  4    l_ename emp.ename%type;
  5  begin
  6    select e.ename
  7      into l_ename
  8      from emp e
  9      where e.deptno = par_deptno;
 10  exception
 11    when too_many_rows then
 12      par_msg := 'There are too many rows returned by the query';
 13    when others then
 14      par_msg := 'Unknown error; code = ' || sqlcode;
 15  end;
 16  /

Procedure created.

Testing:
SQL> declare
  2    l_out varchar2(200);
  3  begin
  4    p_test (&v_deptno, l_out);
  5    dbms_output.put_line(l_out);
  6  end;
  7  /
Enter value for v_deptno: 10
There are too many rows returned by the query

PL/SQL procedure successfully completed.

SQL> /
Enter value for v_deptno: 99
Unknown error; code = 100

PL/SQL procedure successfully completed.

SQL>

In Apex, you'd do similarly (but not exactly the same):

  • create a hidden item (let's call it P1_MSG)
  • in the process, call the procedure as
    p_test(:P1_DEPTNO, :P1_MSG);
  • As a success message, use that item by specifying
    &P1_MSG.
    (note the leading ampersand and the trailing dot)
That's all; if there's an error, you'll see it displayed. If everything went OK, you won't see any error message (but nothing prevents you from displaying a success message by returning some nice text from the procedure.
Previous Topic: Correct Use of LOV
Next Topic: Embedded PL/SQL Gateway EPG in DB 12.2
Goto Forum:
  


Current Time: Sat Dec 07 06:17:13 CST 2019