RE: Re: How to run a trigger in SB in Toad for Oracle?

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: [email protected] [mailto:[email protected]] On Behalf Of Gregory Liss
Sent: Friday, March 22, 2013 5:08 AM
To: <[email protected]>
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?

On Mar 21, 2013, at 11:11 PM, "linorchid" <[email protected]<mailto:linorchid%40yahoo.com>> wrote:

> 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.
>
> --- In [email protected]<mailto:toad%40yahoogroups.com>, Gregory Liss <gregory.liss@...<mailto:gregory.liss@...>> wrote:
>>
>> 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: [email protected]<mailto:toad%40yahoogroups.com> [mailto:[email protected]<mailto:toad%40yahoogroups.com>] On Behalf Of linorchid
>> Sent: Wednesday, March 20, 2013 1:25 PM
>> To: [email protected]<mailto:toad%40yahoogroups.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.
>>
>> --- In [email protected]<mailto:toad%40yahoogroups.com>, Gregory Liss <gregory.liss@> wrote:
>>>
>>> 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: [email protected]<mailto:toad%40yahoogroups.com> [mailto:[email protected]<mailto:toad%40yahoogroups.com>] On Behalf Of linorchid
>>> Sent: Tuesday, March 19, 2013 7:47 PM
>>> To: [email protected]<mailto:toad%40yahoogroups.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.
>>>
>>>
>>> --- In [email protected]<mailto:toad%40yahoogroups.com>, Gregory Liss <gregory.liss@> wrote:
>>>>
>>>> There is a section in the Help file on debugging triggers. Just search for Triggers in the Index.
>>>>
>>>> Greg
>>>>
>>>> -----Original Message-----
>>>> From: [email protected]<mailto:toad%40yahoogroups.com> [mailto:[email protected]<mailto:toad%40yahoogroups.com>] On Behalf Of linorchid
>>>> Sent: Tuesday, March 19, 2013 2:51 PM
>>>> To: [email protected]<mailto:toad%40yahoogroups.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
>>>
>>
>>
>>
>>
>> ------------------------------------
>>
>> Yahoo! Groups Links
>>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>