Oracle Streams error logs not showing timestamp values

Question

When outputting a log of streaming errors on Oracle 10g, the log does not show values for timestamp fields; instead it simply shows "timestamp is SYS.TIMESTAMP" as the old and new value. Is there any way to configure Oracle streams to show the actual timestamp value? We need this information to properly debug the root cause of these streaming errors.

For example, executing the following:

exec strmadmin.print_errors;

Gives us this (truncated) error log:

*************************************************                                                                       
----- ERROR #1                                                                                                          
----- Local Transaction ID: 10.31.14788721                                                                              
----- Source Database: <REMOVED>                                                                          
----Error Number: 1403                                                                                                  
----Message Text: ORA-01403: no data found


--message: 1                                                                                                            
type name: SYS.LCR$_ROW_RECORD                                                                                          
source database: <REMOVED>                                                                                
owner: <REMOVED>                                                                                                         
object: TPRODUCT                                                                                                        
is tag null: Y                                                                                                          
command_type: UPDATE                                                                                                    
old(1): UIDPK                                                                                                           
1001110                                                                                                                 
old(2): LAST_MODIFIED_DATE                                                                                              
typename is SYS.TIMESTAMP

score 0 · Answer 1 · edited Apr 15 '14 at 09:47

0

in the session where print_any procedure is launched use:

alter session set nls_date_format='HH24:Mi:SS MM/DD/YY';

edited Apr 15 '14 at 09:47

manlio

18,345
14
76
126

answered Apr 15 '14 at 09:29

Congo

1

Templar · Accepted Answer · 2011-08-27T21:35:55.023

In case anyone is interested, I found the solution to this problem. Simply replace the "print_any" stored procedure with the following version which outputs the value for timestamp data types:

CREATE OR REPLACE PROCEDURE print_any(data IN ANYDATA) IS
  tn  VARCHAR2(61);
  str VARCHAR2(4000);
  chr VARCHAR2(1000);
  num NUMBER;
  dat DATE;
  rw  RAW(4000);
  res NUMBER;
BEGIN
  IF data IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('NULL value');
    RETURN;
  END IF;
  tn := data.GETTYPENAME();
  IF tn = 'SYS.VARCHAR2' THEN
    res := data.GETVARCHAR2(str);
    DBMS_OUTPUT.PUT_LINE(SUBSTR(str,0,253));
  ELSIF tn = 'SYS.CHAR' then
    res := data.GETCHAR(chr);
    DBMS_OUTPUT.PUT_LINE(SUBSTR(chr,0,253));
  ELSIF tn = 'SYS.VARCHAR' THEN
    res := data.GETVARCHAR(chr);
    DBMS_OUTPUT.PUT_LINE(chr);
  ELSIF tn = 'SYS.NUMBER' THEN
    res := data.GETNUMBER(num);
    DBMS_OUTPUT.PUT_LINE(num);
  ELSIF tn = 'SYS.DATE' THEN
    res := data.GETDATE(dat);
    DBMS_OUTPUT.PUT_LINE(dat);
  ELSIF tn = 'SYS.TIMESTAMP' THEN
    res := data.GETTIMESTAMP(dat);
    DBMS_OUTPUT.PUT_LINE(tn || ':' || to_char(dat,'DD-MON-YYYY HH24:MI:SS.FF'));
  ELSIF tn = 'SYS.RAW' THEN
    -- res := data.GETRAW(rw);
    -- DBMS_OUTPUT.PUT_LINE(SUBSTR(DBMS_LOB.SUBSTR(rw),0,253));
    DBMS_OUTPUT.PUT_LINE(tn || ":RAW");
  ELSIF tn = 'SYS.BLOB' THEN
    DBMS_OUTPUT.PUT_LINE(tn || ":BLOB");
  ELSE
    DBMS_OUTPUT.PUT_LINE('typename is ' || tn);
  END IF;
END print_any;

Oracle Streams error logs not showing timestamp values

2 Answers2