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;
iAdvise blog 