How to run a trigger in SB in Toad for Oracle?

Discussion:

linorchid

2013-03-19 18:50:30 UTC

Hello All,

Just a little background on why I have to run a trigger for troubleshooting:

A trigger is activated when a column of a table in our database is updated thru an Oracle form application. This column is defined as NUMBER(9) in a database table. But, I receive the following error message each time when I set the column to 100000000 or greater value:

ORA-06502: PL/SQL: numeric or value error:number precision too large ORA-06512:at "<Owner>.<Trigger>", line 194 ORA-04088: error during execution of trigger "<Owner>.<Trigger>"

Shouldn't the acceptable maximum value be 999999999 since the column attribute is defined as NUMBER(9)?

So, I am in the process of troubleshooting the trigger and I need to know how to run a trigger manually in SB. Alternatively, I could run the trigger by updating the database table by setting the value >= 100000000. I just want to simplify the trouble shooting process so that I can concentrate and find the root cause the problem.

Thanks all in advance!

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/toad/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/toad/join
(Yahoo! ID required)

<*> To change settings via email:
toad-***@yahoogroups.com
toad-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
toad-***@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Gregory Liss

2013-03-19 18:53:35 UTC

Permalink

There is a section in the Help file on debugging triggers. Just search for Triggers in the Index.

Greg

-----Original Message-----
From: ***@yahoogroups.com [mailto:***@yahoogroups.com] On Behalf Of linorchid
Sent: Tuesday, March 19, 2013 2:51 PM
To: ***@yahoogroups.com
Subject: [toad] How to run a trigger in SB in Toad for Oracle?

Hello All,

Just a little background on why I have to run a trigger for troubleshooting:

A trigger is activated when a column of a table in our database is updated thru an Oracle form application. This column is defined as NUMBER(9) in a database table. But, I receive the following error message each time when I set the column to 100000000 or greater value:

ORA-06502: PL/SQL: numeric or value error:number precision too large ORA-06512:at "<Owner>.<Trigger>", line 194 ORA-04088: error during execution of trigger "<Owner>.<Trigger>"

Shouldn't the acceptable maximum value be 999999999 since the column attribute is defined as NUMBER(9)?

So, I am in the process of troubleshooting the trigger and I need to know how to run a trigger manually in SB. Alternatively, I could run the trigger by updating the database table by setting the value >= 100000000. I just want to simplify the trouble shooting process so that I can concentrate and find the root cause the problem.

Thanks all in advance!

------------------------------------

Yahoo! Groups Links

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/toad/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/toad/join
(Yahoo! ID required)

<*> To change settings via email:
toad-***@yahoogroups.com
toad-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
toad-***@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

linorchid

2013-03-19 23:46:32 UTC

Permalink

Thanks for the information about the help file on debugging triggers. But after reading, I am still clueless as to how to set parameters and then run a trigger.

The help file mentions about setting the parameters for that trigger before running a trigger. In the Set parameters section, the first step is:

1. Click () on the Execute toolbar.
or
Select Debug | Set Parameters.

Where is the Execute toolbar while the Triggers tab is open in the SB?

All options are gray out in the Debug menu including "()Set Parameters."

I am using Toad for Oracle 11.5 & 11.0.

Any advice, thoughts or suggestions? Thanks.

Post by Gregory Liss
There is a section in the Help file on debugging triggers. Just search for Triggers in the Index.
Greg
-----Original Message-----
Sent: Tuesday, March 19, 2013 2:51 PM
Subject: [toad] How to run a trigger in SB in Toad for Oracle?
Hello All,
ORA-06502: PL/SQL: numeric or value error:number precision too large ORA-06512:at "<Owner>.<Trigger>", line 194 ORA-04088: error during execution of trigger "<Owner>.<Trigger>"
Shouldn't the acceptable maximum value be 999999999 since the column attribute is defined as NUMBER(9)?
So, I am in the process of troubleshooting the trigger and I need to know how to run a trigger manually in SB. Alternatively, I could run the trigger by updating the database table by setting the value >= 100000000. I just want to simplify the trouble shooting process so that I can concentrate and find the root cause the problem.
Thanks all in advance!
------------------------------------
Yahoo! Groups Links

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/toad/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/toad/join
(Yahoo! ID required)

<*> To change settings via email:
toad-***@yahoogroups.com
toad-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
toad-***@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Gregory Liss

2013-03-20 14:04:05 UTC

Permalink

This isn't in the SB, you have to do debugging from the editor. From the SB, select the trigger, click Open In Editor. From the editor, follow the directions in the Help file.

Greg

-----Original Message-----
From: ***@yahoogroups.com [mailto:***@yahoogroups.com] On Behalf Of linorchid
Sent: Tuesday, March 19, 2013 7:47 PM
To: ***@yahoogroups.com
Subject: [toad] Re: How to run a trigger in SB in Toad for Oracle?

Thanks for the information about the help file on debugging triggers. But after reading, I am still clueless as to how to set parameters and then run a trigger.

The help file mentions about setting the parameters for that trigger before running a trigger. In the Set parameters section, the first step is:

1. Click (...) on the Execute toolbar.
or
Select Debug | Set Parameters.

Where is the Execute toolbar while the Triggers tab is open in the SB?

All options are gray out in the Debug menu including "(...)Set Parameters."

I am using Toad for Oracle 11.5 & 11.0.

Any advice, thoughts or suggestions? Thanks.

------------------------------------

Yahoo! Groups Links

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/toad/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/toad/join
(Yahoo! ID required)

<*> To change settings via email:
toad-***@yahoogroups.com
toad-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
toad-***@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

linorchid

2013-03-20 17:25:04 UTC

Permalink

Thanks Greg. Okay, I have opened the trigger in Editor. But, how do I turn the 'Debug' option on? Both the set parameter (...) and the Debug sign in the menu area are gray. Does it mean I do not have access to the 'debug' tool?

Also the trigger calls several procedures from a package. Can I troubleshoot the procedures thru the trigger or should I run each procedure separately in a debug mode?

Thanks a lot.

Post by Gregory Liss
This isn't in the SB, you have to do debugging from the editor. From the SB, select the trigger, click Open In Editor. From the editor, follow the directions in the Help file.
Greg
-----Original Message-----
Sent: Tuesday, March 19, 2013 7:47 PM
Subject: [toad] Re: How to run a trigger in SB in Toad for Oracle?
Thanks for the information about the help file on debugging triggers. But after reading, I am still clueless as to how to set parameters and then run a trigger.
1. Click (...) on the Execute toolbar.
or
Select Debug | Set Parameters.
Where is the Execute toolbar while the Triggers tab is open in the SB?
All options are gray out in the Debug menu including "(...)Set Parameters."
I am using Toad for Oracle 11.5 & 11.0.
Any advice, thoughts or suggestions? Thanks.

------------------------------------
Yahoo! Groups Links

Gregory Liss

2013-03-20 17:30:07 UTC

Permalink

You either don't have debug privileges or the trigger hasn't been compiled in debug mode.

You can use Toad Advisor to find out if you have debug privileges (Help -> Toad Advisor). There is also a section in the Help file on Troubleshooting the debugger which has the minimum Oracle requirements.

When you start debugging the trigger, if it calls other procedures, you can step into them (Debug -> Trace Into (Shift-F7)) if those procedures have been compiled with debug settings.

Greg

-----Original Message-----
From: ***@yahoogroups.com [mailto:***@yahoogroups.com] On Behalf Of linorchid
Sent: Wednesday, March 20, 2013 1:25 PM
To: ***@yahoogroups.com
Subject: [toad] Re: How to run a trigger in SB in Toad for Oracle?

Thanks Greg. Okay, I have opened the trigger in Editor. But, how do I turn the 'Debug' option on? Both the set parameter (...) and the Debug sign in the menu area are gray. Does it mean I do not have access to the 'debug' tool?

Also the trigger calls several procedures from a package. Can I troubleshoot the procedures thru the trigger or should I run each procedure separately in a debug mode?

Thanks a lot.

------------------------------------
Yahoo! Groups Links

linorchid

2013-03-20 18:06:48 UTC

Permalink

Great information and thanks. I am currently using Toad for Oracle Base Ver 11.5. In the Hints section of the Results of Toad Advisor, it states the following:

- Oracle no longer supports this server version.
- Oracle no longer supports this client version.
- Auto-detect Oracle utilities is off

I think whether or not I can use the debug utility in Toad for Oracle has nothing to do w/ Oracle supports because I am able to use the Debug util in a different instance for the same application.

In the Checks area of Toad Advisor, it states the following:

Ensuring debugger privileges are present - done

Does it mean I have debugger privileges? If it does, why the debugger option is gray out?

Thanks.

Post by Gregory Liss
You either don't have debug privileges or the trigger hasn't been compiled in debug mode.
You can use Toad Advisor to find out if you have debug privileges (Help -> Toad Advisor). There is also a section in the Help file on Troubleshooting the debugger which has the minimum Oracle requirements.
When you start debugging the trigger, if it calls other procedures, you can step into them (Debug -> Trace Into (Shift-F7)) if those procedures have been compiled with debug settings.
Greg
-----Original Message-----
Sent: Wednesday, March 20, 2013 1:25 PM
Subject: [toad] Re: How to run a trigger in SB in Toad for Oracle?
Thanks Greg. Okay, I have opened the trigger in Editor. But, how do I turn the 'Debug' option on? Both the set parameter (...) and the Debug sign in the menu area are gray. Does it mean I do not have access to the 'debug' tool?
Also the trigger calls several procedures from a package. Can I troubleshoot the procedures thru the trigger or should I run each procedure separately in a debug mode?
Thanks a lot.

------------------------------------
Yahoo! Groups Links

linorchid

2013-03-22 03:11:38 UTC

Permalink

Hello,

I think I finally got my trigger to run in a debug mode, but I am not sure... because it died on Line #4 w/ the following errors:

ORA-06550: line 4, column 56:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

The first 10 lines of the trigger programming code are as follows:

CREATE OR REPLACE TRIGGER HMMS.PB_DWR_UPDATE
AFTER UPDATE OF tot_lab_reg, tot_lab_ot,tot_matl_reg, tot_matl_ot
ON HMMS.DWR FOR EACH ROW
DECLARE
/*
as_orgunit ORG.ORG_ID%TYPE;
ds_orgunit ORG.ORG_ID%TYPE;
ss_orgunit ORG.ORG_ID%TYPE;
*/
d_mon NUMBER;
...

Any/all the help on this would be greatly appreciated.

Thanks.

------------------------------------
Yahoo! Groups Links

Gregory Liss

2013-03-22 10:07:47 UTC

Permalink

When it says line 4, it means line 4 if the anonymous block that is being executed, not the trigger itself. Can you sent the anonymous block from the params window?

Post by linorchid
Hello,
PL/SQL: ORA-00936: missing expression
PL/SQL: SQL Statement ignored
CREATE OR REPLACE TRIGGER HMMS.PB_DWR_UPDATE
AFTER UPDATE OF tot_lab_reg, tot_lab_ot,tot_matl_reg, tot_matl_ot
ON HMMS.DWR FOR EACH ROW
DECLARE
/*
as_orgunit ORG.ORG_ID%TYPE;
ds_orgunit ORG.ORG_ID%TYPE;
ss_orgunit ORG.ORG_ID%TYPE;
*/
d_mon NUMBER;
...
Any/all the help on this would be greatly appreciated.
Thanks.

------------------------------------
Yahoo! Groups Links

Bert Scalzo

2013-03-22 12:58:04 UTC

Permalink

Also who are you connected to the database as? If the trigger is owned by user_1 and you are connected as user_2, you may not have rights or the anonymous block may need you to add schema prefix to trigger call.

Think of this like working in the toad editor doing select's against another users tables. You might need to prefix the tables or far easier use the toad drop down to alter session set schema, or even just connect as that user. My point is that when running PL/SQL code you need to think about access rights and such like in the editor - and with PL/SQL code you might need to think far more since it may contain access to objects from many schemas and hence make the issue even far more complex.

From: ***@yahoogroups.com [mailto:***@yahoogroups.com] On Behalf Of Gregory Liss
Sent: Friday, March 22, 2013 5:08 AM
To: <***@yahoogroups.com>
Subject: Re: [toad] Re: How to run a trigger in SB in Toad for Oracle?

When it says line 4, it means line 4 if the anonymous block that is being executed, not the trigger itself. Can you sent the anonymous block from the params window?

This isn't in the SB, you have to do debugging from the editor. From the SB, select the trigger, click Open In Editor. From the editor, follow the directions in the Help file.
Greg
-----Original Message-----
Sent: Tuesday, March 19, 2013 7:47 PM
Subject: [toad] Re: How to run a trigger in SB in Toad for Oracle?
Thanks for the information about the help file on debugging triggers. But after reading, I am still clueless as to how to set parameters and then run a trigger.
1. Click (...) on the Execute toolbar.
or
Select Debug | Set Parameters.
Where is the Execute toolbar while the Triggers tab is open in the SB?
All options are gray out in the Debug menu including "(...)Set Parameters."
I am using Toad for Oracle 11.5 & 11.0.
Any advice, thoughts or suggestions? Thanks.

There is a section in the Help file on debugging triggers. Just search for Triggers in the Index.
Greg
-----Original Message-----
Sent: Tuesday, March 19, 2013 2:51 PM
Subject: [toad] How to run a trigger in SB in Toad for Oracle?
Hello All,
ORA-06502: PL/SQL: numeric or value error:number precision too large ORA-06512:at "<Owner>.<Trigger>", line 194 ORA-04088: error during execution of trigger "<Owner>.<Trigger>"
Shouldn't the acceptable maximum value be 999999999 since the column attribute is defined as NUMBER(9)?
So, I am in the process of troubleshooting the trigger and I need to know how to run a trigger manually in SB. Alternatively, I could run the trigger by updating the database table by setting the value >= 100000000. I just want to simplify the trouble shooting process so that I can concentrate and find the root cause the problem.
Thanks all in advance!
------------------------------------
Yahoo! Groups Links

------------------------------------
Yahoo! Groups Links

linorchid

2013-03-22 18:12:16 UTC

Permalink

Thanks for the helpful hint Greg. After modifying the anonymous block, my trigger died w/ the following error message:

ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at "HMMS.PB_DWR_UPDATE", line 272
ORA-04088: error during execution of trigger 'HMMS.PB_DWR_UPDATE'
ORA-06512: at line 4

When the error occurred, the debugger high-lighted line #275 which calls procedure <increase_sum> from a package. But line #272 is parameter line from another procedure <decrease_sum> of the same package.

So, how to find the exact line of code when the trigger bombed???

Thanks a lot and I think I am getting closer to find the root cause of the problem w/ your help.

p.s. How do I send a screen copy of my execution as a file to the posting?

Post by Gregory Liss
When it says line 4, it means line 4 if the anonymous block that is being executed, not the trigger itself. Can you sent the anonymous block from the params window?

------------------------------------
Yahoo! Groups Links

linorchid

2013-03-20 17:33:00 UTC

Permalink

BTW, how do I verify if I have access to the DBMS_DEBUG Oracle package or not?

------------------------------------
Yahoo! Groups Links