Solved

message from server to user

Posted on 2006-11-28
22
276 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 48

Expert Comment

by:schwertner
ID: 18027384
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
ID: 18027508
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 35

Expert Comment

by:Mark Geerlings
ID: 18027670
Which form tool do you use?  Is that Oracle Forms, or is it a different form (screen) tool?  
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 48

Expert Comment

by:schwertner
ID: 18027736
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
ID: 18027923
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 35

Expert Comment

by:Mark Geerlings
ID: 18027951
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
ID: 18027962
ok, so tell me about how it is in oracle forms :)
please please.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 18027992
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
ID: 18028075
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 35

Expert Comment

by:Mark Geerlings
ID: 18028131
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
ID: 18028170
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 18028276
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 48

Expert Comment

by:schwertner
ID: 18035031
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
ID: 18035383
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
ID: 18121159
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 48

Expert Comment

by:schwertner
ID: 18121736
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
ID: 18121848
yes, I tried another possibility, the same
going to try first one

s.
0
 
LVL 1

Author Comment

by:Solve
ID: 18122644
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 48

Expert Comment

by:schwertner
ID: 18128865
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
ID: 18128893
But I know exactly - error is ORA-01400 :) can not insert null into related table

s.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 125 total points
ID: 18128947
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
ID: 18129036
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Listener Not Starting 11 52
pl/sql - query very slow 26 71
Fill Null values 5 28
How to get the current date and Time upon oracle insert into a database table 9 38
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

856 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