Deadlock in stored procedure

From: sigdock <sigdock_at_hotmail.com>
Date: 21 Feb 2005 06:28:33 -0800
Message-ID: <1108996113.359629.278250@g14g2000cwa.googlegroups.com>

Hi all,

I wrote a stored procedure to upload files to a table in my database. I also added a line to sync the context-index on the table. However if i try to run the procedure i get a deadlock error. Does anybody have any idea why this happens en how to prevent it? The procedure looks lik this:

CREATE OR REPLACE PROCEDURE load_file_to_my_docs (p_file_name IN my_docs.name%TYPE) AS

  v_bfile      BFILE;
  v_blob       BLOB;
  v_statement  varchar2(200) := 'ALTER INDEX my_docs_doc_idx REBUILD

online PARAMETERS (''SYNC'')';
BEGIN
  INSERT INTO my_docs (id, name, doc)
  VALUES (my_docs_seq.NEXTVAL, p_file_name, empty_blob())   RETURN doc INTO v_blob;

  v_bfile := BFILENAME('DOCUMENTS', p_file_name);

  Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
  Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
  Dbms_Lob.Fileclose(v_bfile);

  COMMIT;
  execute immediate v_statement;
END;
/

the table my_docs looks like:

(ID number(10), name varchar2(200), doc blob)

tia,
Bert Jan Meinders Received on Mon Feb 21 2005 - 08:28:33 CST