message from server to user

The only way to do this is to capture the error message the server sends back when the statement fails.
It is different in different language environments (In PL/SQL there are SQLERRM and SQLCODE functions).

Or mabe you could send some small example to put me on the right way?
As I understood, I neer to capture error: insufficient privileges

s.

Which form tool do you use?  Is that Oracle Forms, or is it a different form (screen) tool?  

If the user has no rights the message looks like
"Table or view doesn't exist"
Try to similate this and you will get the messages (numbers and texts)

Example:

SQL> connect scott/tiger@phr7b
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL> alter sys/manager@phr7b as sysdba
  2  ;
SP2-0640: Not connected
SQL> connect sys/manager@phr7b as sysdba
Connected.
SQL> alter user scott identified by tiger account unlock;

User altered.

SQL> connect scott/tiger@phr7b
Connected.
SQL> select * from my_wishes;
select * from my_wishes
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

This time I use Access forms,

For example:
1. DB server trigger catchs exception ORA-01031:insufficient privileges
2. User in access form see the user friendly message "Sorry man, you can't do this"
s.
 

In Oracle Forms, I know how to code for Oracle errors.  In Access, I have no idea - I have never used that tool.

ok, so tell me about how it is in oracle forms :)
please please.

Do you even have Oracle Forms?  That is *NOT* included with the Oracle database software, it is sold and licensed separately from the database software.

Yes, I've been working with oracle forms for 4 years :)
but it is a small problem: I know how to declare my own or existing exception, catch it and handle througth pragma_init, but I wonder to know if there is a possibilities to send user friendly message directly from oracle server to user forms, isn't it?

s.

No, but Oracle Forms offers an "on-error" trigger.  That allows you to intercept the error coming from the database, and display whatever message you want to display in the form instead.  The problem with this approach is that you must check for specific error codes in the "on-error" trigger.

understood,
so, this specific list of errors I have to generate in server by checking user roles, or what?
and when, to send this list somehow someway to other application...

s.

No, an Oracle database trigger will not be able to send a specific message to a particular application, that is different from what it sends to other applications, unless that application always uses a different login (or different set of logins) than other applications.  Usually, you have to do the coding in the application if you want something specific to that application.

May be you can send specific messages from Oracle triggers using the
m,echanism in PL/SQL to raise user defined errors.
This is explained in Steven Feuerstein book "Oracle PL/SQL Programming", First eddition, 1995, pg. 235, 257


RAISE_APPLICATION_ERROR (-20001, 'Employee must at least eighteen years of age.');

Put statement like this, take it in the execition stream in your trigger and enjoy!

ok, I am moving my question to Access forum :)
mabe somebody will help me, because I am not able to solve this problem :(((
Access forms simply close and that's all, no messages, no errors, nothings... but I need to manage somehow this roles problem

s.

Hi again, about the same question.

I put statment like this (described above) by schwertner:

RAISE_APPLICATION_ERROR (-20001, 'You forgot to enter date');
in before insert trigger, so I see in my access form this message, but also a lot of statments like:
Insert failed,
ORA-06512: at ....., line 11
ORA-04088: error during execution of trigger ...

Is there a possibility to leave only this user friendly message 'You forgot to enter date!', isn't it?

Please make an exception section in your procedure,
catch the error there and after that
issue RAISE_APPLICATION_ERROR (-20001, 'You forgot to enter date');

Another possibility is to define your own exception and to RAISE this exception,
but this is the same as above ...

yes, I tried another possibility, the same
going to try first one

s.

and how to catch error?
I tried to declare pragma exception_init(ex_name,-01400);
and then in exception section raise_application_error, nothing happens ...

s.

You have to catch the particular exception (or ALL!!! WHEN OTHERS THEN) in the exception section.
Using SQLERRM and SQLCODE functions you can write code to analyze WHAT is the error
and to do what you need, for instance at the end to issue
RAISE_APPLICATION_ERROR (-20001, 'You forgot to enter date');

But I know exactly - error is ORA-01400 :) can not insert null into related table

s.

Ok, I will try this also, but I am afraid, that I will get the same awful message in my Access form anyway, because this: RAISE_APPLICATION_ERROR, sent to client a lot of stuff including this wanted message "You forgot to enter date"

it seems like this:
ODBC--insert on a linked table 'Table name' failed

[Oracle][ODBC][Ora]-01400" : "You forgo to enter date"
ORA-06512: at trigger "Trigger name", line 103
ORA-04088: error during the execution of trigger "Trigger name"

So I don't need these all lines:)

But I think I need to learn how to handle errors in access form, too...
uf.. so different from Oracle forms :)  


s.