Hi, I'm totally new to Oracle and I have been trying for several day to accomplish a task without success.
What I need to do...?
I need to extract data and export that data in xml format to a file. I'm using UTL_FILE and everything is was working fine. The issue here is with a CLOB column that exceed the 40,000 characters and when I'm trying to setup a cursor a got the error "SQLERRM=ORA-06502: PL/SQL: numeric or value error". This column contains a json object. Below is part of my code, please be advise that I'm new to Oracle...
==========================================================================================================================================
Cursor v_cursor is
SELECT * FROM ADMIN_AUDIT
WHERE EVENT_DATE >= TO_DATE(SYSDATE - 1 || ' 00:00:00', 'DD-MM-YY HH24:MI:SS')
AND EVENT_DATE <= TO_DATE(SYSDATE || ' 23:59:59', 'DD-MM-YY HH24:MI:SS');
begin
DBMS_OUTPUT.ENABLE (buffer_size => NULL);
v_FILENAME := 'FILE';
-- get the current date --
SELECT to_char(SYSDATE,'mmddyyyy')
Into v_curr_date
FROM dual;
v_XML_FILE := SYS.UTL_FILE.FOPEN( 'path', v_FILENAME || v_curr_date || '.xml', 'W', v_max_lsize);
v_XML_HEADER := '<?xml version="1.0"?>';
SYS.UTL_FILE.PUT_LINE(v_XML_FILE, v_XML_HEADER);
SYS.UTL_FILE.PUT_LINE(v_XML_FILE, '<ROW_RECORD>');
OPEN v_cursor;
LOOP
FETCH v_cursor
INTO v_ID, v_EVENT_TYPE, v_OWNER_ID, v_OWNER, v_OWNER_PERMISSIONS, v_EVENT_DESCRIPTION, v_OBJECT_TYPE,
v_OBJECT_ID, v_BEFORE, v_AFTER, v_TERMINAL, v_EVENT_DATE;
EXIT WHEN v_cursor%NOTFOUND;
-- Here i build the xml structure --
SYS.UTL_FILE.PUT_LINE(v_XML_FILE, '<ROW>');
SYS.UTL_FILE.PUT_LINE(v_XML_FILE, '<ID>' || v_ID || '</ID>');
SYS.UTL_FILE.PUT_LINE(v_XML_FILE, '<EVENT_TYPE>' || v_EVENT_TYPE || '</EVENT_TYPE>');
and so for.....
END LOOP;
CLOSE v_cursor;
-- Here i close the xml tags --
SYS.UTL_FILE.PUT_LINE(v_XML_FILE, '</ROW_RECORD>');
SYS.UTL_FILE.FCLOSE(v_XML_FILE);
if i do the query without the cursor it runs ok, but i don't know how to export the data after.
Please I need help with this....