Home » SQL & PL/SQL » SQL & PL/SQL » Use ":NEW" in Dynamic SQL in Trigger?
Use ":NEW" in Dynamic SQL in Trigger? [message #662939] Tue, 16 May 2017 15:42 Go to next message
whdyck
Messages: 17
Registered: May 2017
Junior Member
Is it possible to use :NEW in a dynamic-SQL statement within a trigger?

I'm trying to extend the ALL_CONSTRAINTS table using a table defined thus:

CREATE TABLE USRBASE.TLKPCONSTRAINTERRORMSG
(
  FLDCONSTRAINTERRORMSGID  NUMBER(11)           NOT NULL,
  FLDOWNER                 VARCHAR2(30 BYTE)    NOT NULL,
  FLDTABLENAME             VARCHAR2(30 BYTE)    NOT NULL,
  FLDCONSTRAINTNAME        VARCHAR2(30 BYTE)    NOT NULL,
  FLDERRORTITLE            VARCHAR2(256 BYTE)   NOT NULL,
  FLDERRORMSG              VARCHAR2(2048 BYTE)  NOT NULL,
  FLDDFU                   DATE                 DEFAULT SYSDATE               NOT NULL,
  FLDUFU                   VARCHAR2(30 BYTE)    DEFAULT USER                  NOT NULL,
  FLDDLU                   DATE                 DEFAULT SYSDATE               NOT NULL,
  FLDULU                   VARCHAR2(30 BYTE)    DEFAULT USER                  NOT NULL
);

This table allows me to establish a user-friendly fldErrorMsg (and fldErrorTitle) for each constraint, which I'd like to use in a trigger using this function:

   FUNCTION GetConstraintValidation(pcOwner             usrBase.tlkpConstraintErrorMsg.fldOwner%TYPE
                                  , pcTableName         usrBase.tlkpConstraintErrorMsg.fldTableName%TYPE
                                  , pcConstraintName    usrBase.tlkpConstraintErrorMsg.fldConstraintName%TYPE)
      RETURN VARCHAR2
   IS
      cSearchCondition       ALL_CONSTRAINTS.SEARCH_CONDITION%TYPE;
      cErrorTitle            usrBase.tlkpConstraintErrorMsg.fldErrorTitle%TYPE;
      cErrorMsg              usrBase.tlkpConstraintErrorMsg.fldErrorMsg%TYPE;
      cDynamicSql            VARCHAR2(1024);

   BEGIN
      SELECT a.SEARCH_CONDITION
           , m.fldErrorTitle
           , m.fldErrorMsg
        INTO cSearchCondition
           , cErrorTitle
           , cErrorMsg
        FROM ALL_CONSTRAINTS a
           , usrBase.tlkpConstraintErrorMsg m
       WHERE     a.OWNER = m.fldOwner
             AND a.CONSTRAINT_NAME = m.fldConstraintName
             AND a.OWNER = pcOwner
             AND a.CONSTRAINT_NAME = pcConstraintName;

      cSearchCondition := REPLACE(cSearchCondition
                                , 'fld'
                                , ':NEW.fld');

      cDynamicSql := 'SELECT CASE WHEN (' || cSearchCondition || ') THEN -1 ELSE 0 END FROM DUAL';

      RETURN cDynamicSql;

   EXCEPTION
      WHEN excConstraintFailed THEN
         raise_application_error(-20001
                               , CHR(10) || CHR(10) || cErrorTitle || CHR(10) || CHR(10) || cErrorMsg || CHR(10) || CHR(10) || CHR(13));
      WHEN OTHERS THEN
         raise_application_error(-20020
                               , SQLERRM);
   END GetConstraintValidation;

If I do this in a trigger on the relevant table, I get an error ORA-01008 (Not all variables bound):

   EXECUTE IMMEDIATE cConstraintValidationSql INTO nValidationResult;

   IF nValidationResult = 0 THEN
      RAISE excConstraintFailed;
   END IF;

Should I be able to do this in a trigger?

Thanks.

Wayne
Re: Use ":NEW" in Dynamic SQL in Trigger? [message #662941 is a reply to message #662939] Tue, 16 May 2017 15:59 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
As a general rule & more often than not TRIGGER should be avoided.
As a general rule & more often than not DYNAMIC SQL should be avoided.
What problem are you REALLY trying to solve?

http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2575882200346616184

http://rwijk.blogspot.com/2007/09/database-triggers-are-evil.html
Re: Use ":NEW" in Dynamic SQL in Trigger? [message #662942 is a reply to message #662941] Tue, 16 May 2017 16:07 Go to previous messageGo to next message
whdyck
Messages: 17
Registered: May 2017
Junior Member
I want to be able to display a user-friendly message to the user rather than the not-very-informative message
"Contraint {constraint-name} violated."

Wayne
Re: Use ":NEW" in Dynamic SQL in Trigger? [message #662943 is a reply to message #662942] Tue, 16 May 2017 16:20 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
So your "solution" is to fire a trigger for every row of DML against EVERY table in the application that has any CONSTRAINT on it.
That will certainly scale as well as my goat can fly.

IMO, application errors should be handled by the front end code that directly interfaces with the user.
How does your "solution" handle a multi-user application that utilizes Connection Pooling?
Re: Use ":NEW" in Dynamic SQL in Trigger? [message #662944 is a reply to message #662943] Tue, 16 May 2017 17:51 Go to previous messageGo to next message
whdyck
Messages: 17
Registered: May 2017
Junior Member
I'm not suggesting my solution is optimal, but I have to work around Oracle's limitation of not allowing a user-friendly message to display when a constraint is violated. And if the update is happening by someone running an update using SQL, is it reasonable to expect the user to understand the issue within a limitation of 30 characters? (Oh, I know they can look it up, but is that really the best use of one's time?) Oracle should allow the constraint to be more user-friendly.

As for "evil" triggers, in my world we have lots of triggers (in place long before I came along). We also have lots of constraints that the users complain about, and I'm trying to make the error messages meaningful. I had hoped that, at the very least, I could run some common code in a trigger to make the constraint errors more intelligible. Seems like you're saying I have to duplicate the constraint logic on the front end.
Re: Use ":NEW" in Dynamic SQL in Trigger? [message #662946 is a reply to message #662944] Tue, 16 May 2017 19:18 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
When a table has multiple constraints, how do you know which constraint threw the error?

How would you proposed solution handle the common situation below?
which constraint was violated?
SQL> @sample.sql
SQL> CREATE TABLE FUBAR (
  2  ID NUMBER,
  3  COL1 VARCHAR2(8))
  4  /

Table created.

SQL> INSERT INTO FUBAR VALUES(1,'ok');

1 row created.

SQL> 
SQL> UPDATE	 FUBAR set id ='a' where ID=1;
UPDATE	    FUBAR set id ='a' where ID=1
                          *
ERROR at line 1:
ORA-01722: invalid number


SQL> UPDATE	 FUBAR set col1 ='a12345678' where ID=1;
UPDATE	    FUBAR set col1 ='a12345678' where ID=1
                            *
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."FUBAR"."COL1" (actual: 9,
maximum: 8)

Re: Use ":NEW" in Dynamic SQL in Trigger? [message #662948 is a reply to message #662946] Wed, 17 May 2017 03:15 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
If users are using SQL directly then they definitely should be expected to understand an ORA-00001 error.
A well designed front end is generally coded to avoid most common ORA errors, and most shops, if they're worried about users understanding ORA-00001 put code in the front end to handle it.

The reason your trigger doesn't work is because dynamic sql can't see any variables from the environment that called it. You need to use bind variables with the USING clause of execute immediate.

That exception handler in the procedure doesn't make any sense. excConstraintFailed isn't raised until after the procedure is called and the when others is just a bad idea - you're just hiding the line that raised the error.
Re: Use ":NEW" in Dynamic SQL in Trigger? [message #662969 is a reply to message #662948] Wed, 17 May 2017 08:49 Go to previous messageGo to next message
whdyck
Messages: 17
Registered: May 2017
Junior Member
@cookiemonster:
Thanks for your helpful post. (The first such in this discussion, I might add.)

Quote:
If users are using SQL directly then they definitely should be expected to understand an ORA-00001 error.
Actually, I've been referring to a check constraint violation (ORA-02290). Yes, SQL users will understand what a check constraint violation is in a generic sense, but they will not understand exactly what is wrong. (e.g., they won't know what the error message "check constraint (USRUNTASN.CHKUNTASNAGRMASTERSTART) violated" means without looking it up). I'd like to automate (in some fashion) a more user-friendly description of the problem for both types of users.

We have thousands of these Oracle custom check constraints that have no front-end equivalent. If painful for the SQL user, even more so for the business client who sees one of these cryptic messages from Oracle.

You're right about my exception handler in the function not making sense. It was a leftover from a previous iteration where I was trying to run the dynamic SQL inside the function. Now I should just let the error bubble up to the calling trigger. I was hoping that I could do the error handling inside the function so I wouldn't have to replicate that in all the triggers that used this code.

Is there any way to pass the trigger's entire :NEW vector into this type of function so I can run the dynamic SQL inside that function?

Your point about the USING clause is helpful, so I'll try that.

Thanks.

Wayne

P.S. I've considered using a similar extension table usrBase.tlkpConstraintErrorMsg so I can construct the user-friendly error messages on the front-end. However, I think then I'd need to map every front-end control to the back-end column I'm checking, which would probably be more complex. Or maybe not. I'll give that more thought. Thanks.
Re: Use ":NEW" in Dynamic SQL in Trigger? [message #662971 is a reply to message #662969] Wed, 17 May 2017 09:53 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can pass :new.<whatever> to procedures/functions same as any variable.
icon14.gif  Re: Use ":NEW" in Dynamic SQL in Trigger? [message #662992 is a reply to message #662948] Thu, 18 May 2017 06:44 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
cookiemonster wrote on Wed, 17 May 2017 03:15
If users are using SQL directly then they definitely should be expected to understand an ORA-00001 error.
A well designed front end is generally coded to avoid most common ORA errors, and most shops, if they're worried about users understanding ORA-00001 put code in the front end to handle it.
And I have yet to see an application-injected "user friendly" message that actually helped the user to solve the problem themselves. What really happens is the message is totally devoid of actionable information and the user comes to the DBA for assistance. And since the definitive message was swallowed by the app, the DBA has nothing to work with.
Re: Use ":NEW" in Dynamic SQL in Trigger? [message #662998 is a reply to message #662992] Thu, 18 May 2017 07:04 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
I've seen it done with ORA-00001, but nothing else.
Re: Use ":NEW" in Dynamic SQL in Trigger? [message #663001 is a reply to message #662992] Thu, 18 May 2017 07:12 Go to previous messageGo to next message
whdyck
Messages: 17
Registered: May 2017
Junior Member
EdStevens wrote on Thu, 18 May 2017 06:44

And I have yet to see an application-injected "user friendly" message that actually helped the user to solve the problem themselves. What really happens is the message is totally devoid of actionable information and the user comes to the DBA for assistance. And since the definitive message was swallowed by the app, the DBA has nothing to work with.
Some of our trigger exceptions have been written with fairly explicit actionable information that would help the user (business end user or SQL user) to identify how to fix the problem. However, the check constraints are a bigger problem. When a business user sees this:
"check constraint (USRUNTASN.CHKUNTASNAGRMASTERSTART) violated"
they *might* come to a developer and ask for help. But even a developer is not gonna know without looking up the code, so we tell the client "I'll go figure it out and get back to you". (How embarrassing is that?) Remember, we have thousands of these things, so no way we'll know how to answer without research.

That's why I want to create a system that maps every custom check constraint to a user-friendly message (read: in English that my mother would understand) that helps the user know what to do. So, for example, if the above constraint threw an error, the user would instead see this message:
"End Date cannot be before Start Date"
The user will likely understand that, as opposed to the other gibberish.

Thanks.

Wayne
Re: Use ":NEW" in Dynamic SQL in Trigger? [message #663002 is a reply to message #663001] Thu, 18 May 2017 07:19 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
IMO, it would be easier to implement a static LOOK-UP utility that took as input actual error stack detail to provide appropriate user-friendly message.
Re: Use ":NEW" in Dynamic SQL in Trigger? [message #663003 is a reply to message #663001] Thu, 18 May 2017 07:19 Go to previous messageGo to next message
whdyck
Messages: 17
Registered: May 2017
Junior Member
whdyck wrote on Thu, 18 May 2017 07:12
That's why I want to create a system that maps every custom check constraint to a user-friendly message
I should be clear: In order for this to happen, we'll have to create a mapping table in which a human writes an "English, user-friendly" message for every one of these constraints. It will still be a lot of work, yes. Problem right now is that the custom constraint object in Oracle does not even allow a custom message, so I need to create the mapping table described above and figure out where and how we can extract the custom message when an error occurs.

Wayne
Re: Use ":NEW" in Dynamic SQL in Trigger? [message #663004 is a reply to message #663002] Thu, 18 May 2017 07:21 Go to previous messageGo to next message
whdyck
Messages: 17
Registered: May 2017
Junior Member
BlackSwan wrote on Thu, 18 May 2017 07:19
IMO, it would be easier to implement a static LOOK-UP utility that took as input actual error stack detail to provide appropriate user-friendly message.
Could you elaborate?
Re: Use ":NEW" in Dynamic SQL in Trigger? [message #663005 is a reply to message #663003] Thu, 18 May 2017 07:23 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
>how we can extract the custom message when an error occurs.
Why MUST it be real time part of application code?

I view you desire much like "oerr" utility that Oracle provides on *NIX systems
Re: Use ":NEW" in Dynamic SQL in Trigger? [message #663014 is a reply to message #663005] Thu, 18 May 2017 08:00 Go to previous message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
Below is an example of "oerr" error reporting utility provided by Oracle Corp.

[oracle@vbgeneric ~]$ oerr ora 12514
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a service name for a service (usually a database service)
// that either has not yet dynamically registered with the listener or has
// not been statically configured for the listener.  This may be a temporary
// condition such as after the listener has started, but before the database
// instance has registered with the listener.
// *Action: 
//  - Wait a moment and try to connect a second time.
//  - Check which services are currently known by the listener by executing:
//    lsnrctl services <listener name>
//  - Check that the SERVICE_NAME parameter in the connect descriptor of the
//    net service name used specifies a service known by the listener.
//  - If an easy connect naming connect identifier was used, check that
//    the service name specified is a service known by the listener.
//  - Check for an event in the listener.log file.
[oracle@vbgeneric ~]$ 
Previous Topic: Procedure not functioning properly
Next Topic: listagg function in oracle
Goto Forum:
  


Current Time: Wed Dec 13 03:38:58 CST 2017

Total time taken to generate the page: 0.02351 seconds