Read from anydata column

What should you do if you have an anydata column in a queue table and you don’t have any tool to read from it(sqldeveloper doesn’t support it natively)?

I have written some code to extract all the information from such a column and print it, whatever the content would be.

You can find out more

DECLARE
  v_typenm    varchar2(61);
  v_ddllcr    sys.lcr$_ddl_record;
  v_proclcr   sys.lcr$_procedure_record;
  v_rowlcr    sys.lcr$_row_record;
  v_res       number;
  v_newlist   sys.lcr$_row_list;
  v_oldlist   sys.lcr$_row_list;
  v_ddl_text  clob;
  v_ext_attr  sys.anydata;
  PROCEDURE print_any(p_data in sys.anydata)
  IS
    v_tyn varchar2(61);
    v_str varchar2(4000);
    v_chr varchar2(1000);
    v_num number;
    v_dat date;
    v_raw raw(4000);
  BEGIN
    IF p_data IS NULL THEN
      dbms_output.put_line(‘null value’);
      RETURN;
    END IF ;
    v_tyn := P_DATA.GETTYPENAME();
    case LOWER(v_tyn)
    WHEN ‘sys.varchar2′ THEN
      IF p_data.getvarchar2(v_str) = dbms_types.success THEN
        DBMS_OUTPUT.PUT_LINE(substr(v_str,0,253));
      END IF ;      
    WHEN  ‘sys.char’ THEN
      IF p_data.getchar(v_chr) = dbms_types.success THEN
        DBMS_OUTPUT.PUT_LINE(SUBSTR(v_chr,0,253));
      END IF ;
    WHEN  ‘sys.varchar’ then
      IF p_data.getchar(v_chr) = dbms_types.success THEN
        DBMS_OUTPUT.PUT_LINE(V_CHR);
      END IF ;    
    WHEN ‘sys.number’ THEN
      IF p_data.getnumber(v_num) = dbms_types.success THEN
        dbms_output.put_line(v_num);
      END IF ;  
    WHEN  ‘sys.date’ THEN
      IF  p_data.getdate(v_dat)= dbms_types.success THEN
        dbms_output.put_line(v_dat);
      END IF ;  
    WHEN  ‘sys.raw’ THEN
      IF  p_data.getraw(v_raw)= dbms_types.success THEN
        dbms_output.put_line(substr(dbms_lob.substr(v_raw),0,253));
      END IF ;
    WHEN ‘sys.blob’ THEN
      dbms_output.put_line(‘blob found’);
    ELSE
      DBMS_OUTPUT.PUT_LINE(‘data typename is ‘ || v_tyn);
    END CASE ;
  END print_any;
 
BEGIN
  for R_QUEUE in (select USER_DATA from QUEUE_TABLE) LOOP
    CASE lower(r_queue.user_data.gettypename)
      WHEN ‘sys.lcr$_ddl_record’ THEN
 
        if R_QUEUE.USER_DATA.GETOBJECT(V_DDLLCR) = DBMS_TYPES.SUCCESS then
          dbms_output.put_line(‘=======================================================’);
          dbms_output.put_line(‘source database name: ‘ || v_ddllcr.get_source_database_name);
          dbms_output.put_line(‘object owner: ‘ || v_ddllcr.get_object_owner);
          dbms_output.put_line(‘object name: ‘ || v_ddllcr.get_object_name);
          dbms_output.put_line(‘object type: ‘ || v_ddllcr.get_object_type);
          dbms_output.put_line(‘current schema: ‘ || v_ddllcr.get_current_schema);
          dbms_output.put_line(‘transaction id: ‘ || v_ddllcr.get_transaction_id);
          DBMS_OUTPUT.PUT_LINE(‘scn: ‘ || V_DDLLCR.GET_SCN);
          dbms_output.put_line(‘=======================================================’);
          dbms_lob.createtemporary(v_ddl_text, true);
          v_ddllcr.get_ddl_text(v_ddl_text);
          DBMS_OUTPUT.PUT_LINE(‘ddl: ‘ || V_DDL_TEXT);
        END IF;
      WHEN  ‘sys.lcr$_row_record’ THEN    
        if R_QUEUE.USER_DATA.GETOBJECT(V_ROWLCR) = DBMS_TYPES.SUCCESS then
          dbms_output.put_line(‘=======================================================’);
          dbms_output.put_line(‘source database name: ‘ || v_rowlcr.get_source_database_name);
          dbms_output.put_line(‘object owner: ‘ || v_rowlcr.get_object_owner);
          DBMS_OUTPUT.PUT_LINE(‘object name: ‘ || V_ROWLCR.GET_OBJECT_NAME);
          DBMS_OUTPUT.PUT_LINE(‘command_type: ‘ || V_ROWLCR.GET_COMMAND_TYPE);         
          dbms_output.put_line(‘=======================================================’);
          v_oldlist := v_rowlcr.get_values(‘old’);
          FOR i in 1..v_oldlist.count LOOP
            IF  v_oldlist(i) IS NOT NULL THEN
              dbms_output.put_line(‘old(‘ || i || ‘): ‘ || v_oldlist(i).column_name);
              print_any(v_oldlist(i).data);
            END IF ;
          END LOOP;
          v_newlist := v_rowlcr.get_values(‘new’, ‘n’);
          FOR i in 1..v_newlist.count LOOP
            IF  v_newlist(i) IS NOT NULL THEN
              dbms_output.put_line(‘new(‘ || i || ‘): ‘ || v_newlist(i).column_name);
              print_any(v_newlist(i).data);
            END IF ;
          END LOOP;
        END IF;
      ELSE
        print_any(r_queue.user_data);
    END CASE;
  end LOOP;
END;

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s