[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

message from server to user

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
Solve
Asked:
Solve
  • 11
  • 6
  • 5
1 Solution
 
schwertnerCommented:
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
 
SolveAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
Which form tool do you use?  Is that Oracle Forms, or is it a different form (screen) tool?  
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
schwertnerCommented:
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
 
SolveAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
In Oracle Forms, I know how to code for Oracle errors.  In Access, I have no idea - I have never used that tool.
0
 
SolveAuthor Commented:
ok, so tell me about how it is in oracle forms :)
please please.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
SolveAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
SolveAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
schwertnerCommented:
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
 
SolveAuthor Commented:
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
 
SolveAuthor Commented:
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
 
schwertnerCommented:
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
 
SolveAuthor Commented:
yes, I tried another possibility, the same
going to try first one

s.
0
 
SolveAuthor Commented:
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
 
schwertnerCommented:
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
 
SolveAuthor Commented:
But I know exactly - error is ORA-01400 :) can not insert null into related table

s.
0
 
schwertnerCommented:
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
 
SolveAuthor Commented:
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 11
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now