Menu

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