Kbase P16317: How to read the oracle DataServer log for the insert stateme
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
28/01/2003 |
|
Status: Unverified
GOAL:
How to read the oracle DataServer log for the insert statement?
FIX:
This solution applies if POGRESS_RECID is implemented to support progress behavior. PROGRESS_RECID is populated with the values from the Oracle sequence generator. The process is done by the Progress DataServer.
When The Progress application is doing an insert or assign to the oracle database using the DataServer, the DataServer opens a cursor (OCI call oopen <cursor_id> cc=1) for the insert of the rows with return code (rc) equals to 0 (OCI retr oopen <cursor_id> rc= 0 (time in microsecond). The ·cc· stands for the number of currently opened cursor, and the rc equals to zero means the cursor is fine and the OCI call was successful. The (1433 us) is the time elapsed between OCI call and the oracle to return the reply in microseconds. Then the DataServer identifies that the cursor is available to be used (OCI call omru <cursor_id>). Here is how the DataServer log looks like for the insert cursor:
13:26:51 OCI call oopen <2> cc = 1
13:26:51 OCI retr oopen <2> rc = 0 (1433 us)
13:26:51 OCI call omru <2>
After the above open cursor, the DataServer opens another cursor to fetch the sequence number to insert it in the PROGRESS_RECID along with other values. Here is the log file inserts for getting the value from the oracle sequence generator:
13:26:51 OCI call oopen <3> cc = 2
13:26:51 OCI retr oopen <3> rc = 0 (481 us)
13:26:51 OCI call omru <3>
13:26:51 OCI call oparse <3> sqlcrc = 53307
13:26:51 SELECT sports2000.Customer_seq.nextval FROM sys.dual
13:26:51 OCI call oexfet <3> num = 1
Again, the first line is to create the cursor number 3, and verify that the cursor is OK and available to be used. The forth line parses the SQL to get the sequence number for the customer table in sports2000 database in oracle to insert the same value to the PROGRESS_RECID column along with other values.
The fifth line is the actual SQL statement to retrieve the sequence number from oracle. The sixth line is for the successfully retrieve of the sequence number.
If however the SQL in line five above is bad like ·SELECT .nextval FROM sys.dual· then the subsequent line should look like the following:
13:26:51 SELECT .nextval FROM sys.dual
=>26:51 OCI retr oparse <3> rc = 0 (312 us)
13:26:51 OCI call odefin <3>
13:26:51 OCI parm odefin <3>
13:26:51 type 3 var :1 addr: 0x40102b3c size 4/0 value: '...'
13:26:51 OCI retr odefin <3> rc = 0 (449 us)
13:26:51 OCI call oexfet <3> num = 1
13:26:51 OCI retr oexfet <3> rc = 936 (35879 us)----------
13:26:51 OCI call orol <0>
The first line above is the bad SQL since the database name and the sequence name for the customer table got lost for some other reasons. The second line above is where the DataServer tells ORACLE what to do with the items in the select list. The forth line is the parameter definition that DataServer tells oracle as follows:
The ·var :1· refers to the first item in the list (which should have been customer_seq.nextval).
The ·type 3· tells oracle that the DataServer would like the variable returned as a binary integer (other types are:
5 - null delimited ASCII string
8 - long (text) value
12 - native date (7 byte binary value)
23 - raw
24 - long raw
102 - cursor parameter (stored procedure).
The ·size 4/0· indicates it is 4 bytes long.
If a nonzero value occurs after the slash, it is an array parameter.
The value: '...' is typically meaningless for odefin. It sometimes shows the value being sent to ORACLE for obnd type calls.
Again, the sixth line is the reply from the oracle with "rc" equals zero means that the OCI call from the DataServer to Oracle was successful and the oracle will execute the SQL. The seventh line is the DataServer OCI call to oracle to fetch a value for the SQL executed in oracle, and the eight line is the reply from the .oracle for the bad SQL statement with ·rc· equals to the oracle error number 936 (missing expression). The ninth line is for the oracle rollback information for the bad insert or assign transaction..