Problem with compiling trigger on partitioned table


#1

Hello!
So, situation is described in theme.
When I try to compile a trigger on partitioned table, the exception is thrown:

line 1: ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at “GITORA.BDF_DBOBJECT_QRY”, line 358
ORA-06512: at “GITORA.API_GITORA”, line 221
ORA-06512: at line 7

Disabling/enabling these gitora triggers helps us to continue working:
bdf_sourcecontrol_ac,
go_ac

Best regards,
Roman.


#2

Hi Roman,

I was able to replicate the issue here as well. Here is the reason:

As some point during the execution of the code Gitora fires the following query:

select object_type
into v_type_cd
from all_objects
where owner=in_schema_cd
and object_name=in_name_tx;

in_name_tx is the name of the table the trigger is on. in_schema_cd is the owner of the table. This query returns more than one row if the table is partitioned which causes the error. I didn’t know that this query could more than one row. Apparently a table partition can have the same name as the table itself.

So as of now, Gitora doesn’t work with triggers ion partitioned tables. Unfortunately, this error will keep happening even if you remove the trigger from the Gitora repository because Gitora is trying to figure out if the trigger your are executing is an instead of trigger. And the error occurs while Gitora is trying to figure out the type if the trigger.

Gitora does this because if the trigger is an instead of trigger, Gitora will put it in version control if the view it is on is in version control. The logic here is that, it doesn’t make sense to put a view to version control without its instead of trigger. Gitora is trying to make it more convenient for the developer and tries to avoid a potential mistake he might do.

So for now, disable the Gitora triggers before you edit that trigger.

Kind Regards,
Yalim


#3

Yalim, thank you for your detailed answer.
I had a similar guess, but obfuscated packages didn’t let me check it out:)

Look forward to new Gitora version release!
Best regards,
Roman.


#4

You are welcome Roman. We are thinking about open sourcing Gitora so that people can make necessary changes for their own needs. Unfortunately, we are swamped with work and even open sourcing a project takes 5-10 days of effort which we don’t have at this moment.

Kind Regards,
Yalim