1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA -1401 Inserted value too large for column

Discussion in 'SQL PL/SQL' started by man_expo, Nov 12, 2009.

  1. man_expo

    man_expo Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    ORA 04021 - ORA-04021 timeout occurred while waiting to lock object

    I was running a procedure but I think the procedure got hung while loading data in a cursor(96 rows). Any solution for this?

    Now I had to terminate the procedure as nothing was happening. Now when I try to run I am getting the error

    ORA-04021 timeout occurred while waiting to lock <my procedure>

    How to proceed now?

    Please help
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: ORA 04021 - ORA-04021 timeout occurred while waiting to lock object

    How did you 'terminate the procedure'?
     
  3. man_expo

    man_expo Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    I didn't actually terminate the procedure. I am calling the procedure from a UNIX shell script and I just terminated the shell script. So next time I ran the script, this lock out came up. Through toad I could see that my procedure is in uncompiled state. Tried to delete it through Toad. Did not work. :)

    May be it will be solved overnight. But there will still be the problem of the procedure hanging.

    The first line in the begin block is a DBMS_OUTPUT statement saying the process has started. But this is also not appearing. So I suppose the cursor which has to load 96 rows must be the problem. But what is the alternative or how can I make this work?

    The procedure was working when the cursor had only few rows to load.

    Thanks in advance.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    dbms_output.put_line doesn't display anything until the PL/SQL block has completed its execution, which is why it doesn't appear as you expect.

    How do you know the cursor is 'hung'? Killing a shell script doesn't always kill the child processes it spawns; you should check for sqlplus processes with a PPID of 1 to find if it is still running and locking the procedure.

    You've posted no code for this procedure so no one can tell you what may be wrong with it.
     
  5. man_expo

    man_expo Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    "dbms_output.put_line doesn't display anything until the PL/SQL block has completed its execution, which is why it doesn't appear as you expect."

    Thanks for this info. Or else my debugging would have gone in another direction.

    If this is the case then the procedure may not have hung in the first place as there are 65000 rows in first table itself. My code updates 4-5 columns in each table. And there are around 26 such tables.

    Is there anyway I can do something to show as a feedback that the procedure is running and not hung. I had thought DBMS_OUTPUT.PUT_LINE would work.

    Thanks again.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You can try using dbms_pipe to feed another procedure that can accept input through a pipe and display it. Visit the documentation at http://tahiti.oracle.com and look up the package and how it can be used.
     
    man_expo likes this.
  7. man_expo

    man_expo Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for all the help. I will definitely explore dbms_pipe.