Solved

message from server to user

Posted on 2006-11-28
22
269 Views
Last Modified: 2012-06-27
Hello,
We need to send a message from server to user forms if it doesn't have privileges, such as modify, delete or other?
What do I need to do, please help

Solve
0
Comment
Question by:Solve
  • 11
  • 6
  • 5
22 Comments
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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).
0
 
LVL 1

Author Comment

by:Solve
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Which form tool do you use?  Is that Oracle Forms, or is it a different form (screen) tool?  
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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>
0
 
LVL 1

Author Comment

by:Solve
Comment Utility
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.
 
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
In Oracle Forms, I know how to code for Oracle errors.  In Access, I have no idea - I have never used that tool.
0
 
LVL 1

Author Comment

by:Solve
Comment Utility
ok, so tell me about how it is in oracle forms :)
please please.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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.
0
 
LVL 1

Author Comment

by:Solve
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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.
0
 
LVL 1

Author Comment

by:Solve
Comment Utility
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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.
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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!
0
 
LVL 1

Author Comment

by:Solve
Comment Utility
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.
0
 
LVL 1

Author Comment

by:Solve
Comment Utility
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?
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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 ...
0
 
LVL 1

Author Comment

by:Solve
Comment Utility
yes, I tried another possibility, the same
going to try first one

s.
0
 
LVL 1

Author Comment

by:Solve
Comment Utility
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.
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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');
0
 
LVL 1

Author Comment

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

s.
0
 
LVL 47

Accepted Solution

by:
schwertner earned 125 total points
Comment Utility
EXCEPTION
    WHEN OTHERS THEN
     -- dbms_output.put_line (SQLCODE || '    '  || SQLERRM);
     IF SQLCODE= 'ORA-01400'
     THEN          
         RAISE_APPLICATION_ERROR (-20001, 'You forgot to enter date');
     ELSE
          RAISE_APPLICATION_ERROR (-20002, SQLCODE || ' '  || SQLERRM);
    END IF;
END;

To sort out the exact structure of the error message at the beginning decomment the
dbms_output.put_line  statement
0
 
LVL 1

Author Comment

by:Solve
Comment Utility
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now