Hello , I'm getting these errors while I want to **CREATE A PROCEDURE**
Error at line 13: PL/SQL: SQL Statement ignored Error
at line 13: PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got CLOB
I really can't figure out why these errors are happening , please guide me through this , Thanks to all !
Here's my code :
create or replace PROCEDURE save_note_bg
(p_note_id IN NUMBER ,
p_old_checksum IN VARCHAR2,
p_note_array IN wwv_flow_global.vc_arr2) IS
l_new_checksum VARCHAR2(100);
l_current_checksum VARCHAR2(100);
l_note_content CLOB ;
BEGIN
SELECT TO_CHAR(LPAD(TEXT_NOTE,10,0),'fm0xxxxxxxxx') INTO l_current_checksum
FROM NOTES
WHERE ID = p_note_id;
IF l_current_checksum <> p_old_checksum THEN
apex_json.open_object;
apex_json.write('status_code', 'E');
apex_json.write('status_msg', APEX_LANG.MESSAGE('ANOTHER_USER_CHANGED_NOTE'));
apex_json.close_object;
ELSE
l_note_content := empty_clob();
dbms_lob.createtemporary(l_note_content, true);
FOR i IN 1..p_note_array.count LOOP
l_note_content := l_note_content || TO_CLOB(p_note_array(i));
END LOOP;
UPDATE NOTES
SET TEXT_NOTE = l_note_content
WHERE ID = p_note_id;
l_new_checksum := TO_CHAR(LPAD(l_note_content,10,0),'fm0xxxxxxxxx');
apex_json.open_object;
apex_json.write('status_code', 'S');
apex_json.write('status_msg', 'Note ID ['||p_note_id|| '] AutoSaved');
apex_json.write('new_checksum', l_new_checksum);
apex_json.close_object;
END IF;
EXCEPTION WHEN OTHERS THEN
apex_json.open_object;
apex_json.write('status_code', 'E');
apex_json.write('status_msg', SQLERRM);
apex_json.close_object;
END save_note_bg;