Re: Deadlock in stored procedure

On 21 Feb 2005 06:28:33 -0800, "sigdock" <sigdock_at_hotmail.com> wrote:

>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
>

Hi Bert,

Please provide the following info if you want us to help you:

1. Oracle version and platform ( *must ALWAYS* be provided)
2. At which statement in your code does the Deadlock actually occur ?
3. The exact error message/error stack.
   • Kenneth Koenraadt