Home » SQL & PL/SQL » SQL & PL/SQL » Tracing procedures, triggers, executions
Tracing procedures, triggers, executions [message #274360] Mon, 15 October 2007 16:39 Go to next message
egray
Messages: 9
Registered: October 2007
Location: Kenner, LA
Junior Member
I am having trouble tracing the interaction of an application with an Oracle 10g database. I am trying to create a web interface for an application and can not figure out where/how the unique IDs (ENTRYID) are created.

I have run dbms_monitor.session_trace_enable for the application's session, but have not been able to see the information I am looking for on where/how the unique ID is generated

Everytime a record is inserted or updated, a unique ENTRYID is generated. Below are two of the statements for inserting and updating.

INSERT INTO GN.SHIPPING (PID,SID,ENTRYID,SVALUE) VALUES ('3428','23','15688221','In Transit');

UPDATE GN.SHIPPING SET ENTRYID='15689671',SVALUE='Delivered' WHERE PID='3428' AND SID='23' AND ENTRYID='15688221';

Where do I need to look to find out where/how the ENTRYID is created? What type of trace do I need to run to capture the information I need?

Thank you for your help,
Ed
Re: Tracing procedures, triggers, executions [message #274366 is a reply to message #274360] Mon, 15 October 2007 18:09 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Where do I need to look to find out where/how the ENTRYID is created?
I suspect it is coming from a TRIGGER.
SQL> desc user_triggers
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TRIGGER_NAME                                       VARCHAR2(30)
 TRIGGER_TYPE                                       VARCHAR2(16)
 TRIGGERING_EVENT                                   VARCHAR2(227)
 TABLE_OWNER                                        VARCHAR2(30)
 BASE_OBJECT_TYPE                                   VARCHAR2(16)
 TABLE_NAME                                         VARCHAR2(30)
 COLUMN_NAME                                        VARCHAR2(4000)
 REFERENCING_NAMES                                  VARCHAR2(128)
 WHEN_CLAUSE                                        VARCHAR2(4000)
 STATUS                                             VARCHAR2(8)
 DESCRIPTION                                        VARCHAR2(4000)
 ACTION_TYPE                                        VARCHAR2(11)
 TRIGGER_BODY                                       LONG
Re: Tracing procedures, triggers, executions [message #274372 is a reply to message #274366] Mon, 15 October 2007 20:49 Go to previous messageGo to next message
egray
Messages: 9
Registered: October 2007
Location: Kenner, LA
Junior Member
I checked the user_triggers and unfortunately it is not one of them. I also saw one of the triggers referred to a procedure, so I checked the Procedures and did not see anything there.

Any other suggestions?
Re: Tracing procedures, triggers, executions [message #274408 is a reply to message #274372] Mon, 15 October 2007 23:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
By the look of the statements (no bind variables), my bet would be that it's the web-application. Is it by any chance in a language other than pl/sql?
Re: Tracing procedures, triggers, executions [message #274590 is a reply to message #274408] Tue, 16 October 2007 09:04 Go to previous message
egray
Messages: 9
Registered: October 2007
Location: Kenner, LA
Junior Member
The statements I pulled from the dbms_monitor.session_trace have bind variables. I replaced the bind variables for the post - sorry if that changes things. The actual statements are:

SQL> INSERT INTO GN.SHIPPING (PID,SID,ENTRYID,SVALUE) VALUES (:hvPID,:hvSID,:hvENTRYID,:hvSVALUE)

SQL> UPDATE GN.SHIPPING SET ENTRYID=:hvENTRYID,SVALUE=:hvSVALUE WHERE PID=:hvPIDw AND SID=:hvSIDw AND ENTRYID=:hvENTRYIDw

Some of the other statements for updating other tables refer to ROWID=:hvROWIDw. I believe it is written in pl/sql. The application is a Microsoft .Net application.
Previous Topic: BLOB & BFILE Advantage and disadvantage
Next Topic: Regarding Triggers & procedures (merged)
Goto Forum:
  


Current Time: Sun Dec 04 16:35:55 CST 2016

Total time taken to generate the page: 0.23757 seconds