Home » SQL & PL/SQL » SQL & PL/SQL » ORA00604: error occurred at recursive SQL Level 1 ORA01400: can not insert null in to (sys,obj$,name
ORA00604: error occurred at recursive SQL Level 1 ORA01400: can not insert null in to (sys,obj$,name [message #419540] Mon, 24 August 2009 02:29 Go to next message
meg_2411
Messages: 11
Registered: August 2009
Location: India
Junior Member
Getting the below error while compiling following procedure:

ORA00604: error occurred at recursive SQL Level 1
ORA01400: can not insert null in to (sys,obj$,name)


CREATE OR REPLACE Procedure MIDAS.Insert_Manual_WOs IS

temp NUMBER;
wo_location VARCHAR2(2);
wo_nr VARCHAR2(10);
prefix VARCHAR2(2);
s_user NUMBER;
cursor crs_temp_data is select * from MIDAS.TEMP_DATA;

BEGIN

For i in crs_temp_data
Loop

-- Compute the value of next Wo Nr
SELECT next_wo.NEXTVAL
INTO temp
FROM dual;

if i.type_code ='P' then
prefix := '99';
else
prefix := '55';
end if;

wo_nr := prefix || LPAD(TO_CHAR(temp), 8, '0');

--Decide value of wo_location
select count(*) into s_user from midas_user where superuser_flag='Y' and user_id=i.user_id;

if s_user =1 and i.type_code = 'P' then
wo_location := 'C';
else
wo_location := 'L';
end if;


-- Insert data in wo_in
midas.midas_work_order.insert_local_wo_in(wo_nr,
i.part_nr_in,
i.planned_date,
i.planned_date,
i.planned_qty,
i.earliest_start_date,
i.customer_code,
i.type_code,
'55CV',
i.production_level,
i.production_area,
i.remark,
i.user_id,
wo_location,
i.phantom_flag,
0,
"");



-- Insert data in wo_out
midas.midas_work_order.insert_local_wo_out(wo_nr,
i.part_nr_out,
'55CV',
i.due_date,
i.expected_date,
i.expected_qty,
i.production_level,
0,
"");
End Loop;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);


End;

ANY CLUES??
Re: ORA00604: error occurred at recursive SQL Level 1 ORA01400: can not insert null in to (sys,obj$, [message #419542 is a reply to message #419540] Mon, 24 August 2009 02:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you cut and paste the exact error message, ideally as part of a larger cut and paste showing the whole operation that causes the error.
Re: ORA00604: error occurred at recursive SQL Level 1 ORA01400: can not insert null in to (sys,obj$, [message #419543 is a reply to message #419540] Mon, 24 August 2009 02:43 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I am guessing that there seem to exists "after create on database" trigger and that trigger appears to be failing.

[Updated on: Mon, 24 August 2009 02:44]

Report message to a moderator

Re: ORA00604: error occurred at recursive SQL Level 1 ORA01400: can not insert null in to (sys,obj$, [message #419546 is a reply to message #419542] Mon, 24 August 2009 03:31 Go to previous messageGo to next message
meg_2411
Messages: 11
Registered: August 2009
Location: India
Junior Member
ORA00604: error occurred at recursive SQL Level 1
ORA01400: can not insert null in to (sys,obj$,name)

this is the exact message.......
Re: ORA00604: error occurred at recursive SQL Level 1 ORA01400: can not insert null in to (sys,obj$, [message #419552 is a reply to message #419546] Mon, 24 August 2009 04:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It would have been nice if you'd shown willing and shown us the entire error stack - after all, you're assuming we know more than you, or you wouldn't be asking us for help, so why not provide what we ask for.

Try running this - it should show up any event based triggers that you've got:
select owner,trigger_name,trigger_type,triggering_event
from   dba_triggers
where  trigger_type like '%EVENT';
Re: ORA00604: error occurred at recursive SQL Level 1 ORA01400: can not insert null in to (sys,obj$, [message #419554 is a reply to message #419552] Mon, 24 August 2009 04:16 Go to previous messageGo to next message
meg_2411
Messages: 11
Registered: August 2009
Location: India
Junior Member
JRow,
This is the result of the query...i think i m failing to understand wht additional information u need from my side...sorry,cn u be more specific?

1 SYS AURORA$SERVER$SHUTDOWN BEFORE EVENT SHUTDOWN
2 SYS AURORA$SERVER$STARTUP AFTER EVENT STARTUP
3 SYS JIS$ROLE_TRIGGER$ AFTER EVENT DROP
4 MDSYS SDO_DROP_USER AFTER EVENT DROP
Re: ORA00604: error occurred at recursive SQL Level 1 ORA01400: can not insert null in to (sys,obj$, [message #419617 is a reply to message #419554] Mon, 24 August 2009 08:43 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
First of all , you need to read Forum guidelines http://www.orafaq.com/forum/t/88153/0/ and follow the guidelines completely.

Quote:
This is the result of the query...i think i m failing to understand wht additional information u need from my side...sorry,cn u be more specific?



You need to post the entire SQL*PLUS output for us to see all the things you did and only then we can help you.

[Updated on: Mon, 24 August 2009 08:49]

Report message to a moderator

Re: ORA00604: error occurred at recursive SQL Level 1 ORA01400: can not insert null in to (sys,obj$, [message #419623 is a reply to message #419617] Mon, 24 August 2009 09:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think I've found it - replace the pairs of double quotes with pairs of single quotes in the calls to midas.midas_work_order.insert_local_wo_in and midas.midas_work_order.insert_local_wo_out.

As to what we need - we have a lot of experience with people asking questions here, and while most posters try to help, they almost invariable provide less information than is available, and the information that they supply is inaccurate, or just plain wrong more often that you'd believe.

If we ask you to run something in SQL*Plus and provide the results, we want you to fire up SQL*Plus (either command line or window based), perform the operation that we tell you, and then copy either everything you've done, or at least all of the output provided, and paste that into a reply here between a pair of [code] tags.

In this case, if you'd provided the entire error stack, I suspect that it would have either given us the line number that the error occurred at (which would have narrowed things down a lot) or would have included the procedure that the error occurred in, which was probably midas_work_order.insert_local_wo_in.

We have a lot more experience of solving problems here that new posters have of posting them, so if we ask for something, please supply it - we have our reasons.
Re: ORA00604: error occurred at recursive SQL Level 1 ORA01400: can not insert null in to (sys,obj$, [message #419708 is a reply to message #419623] Tue, 25 August 2009 05:04 Go to previous message
meg_2411
Messages: 11
Registered: August 2009
Location: India
Junior Member
Thanks a tonne JRow!!.......u were absolutely right.

Procedure was created successfully after replacing "" with ''.


Thanks for the instructions also..I ll keep them in mind frm next time onwards.
Previous Topic: Query for RLS is not activated:
Next Topic: migration script needed
Goto Forum:
  


Current Time: Sat Dec 03 20:01:22 CST 2016

Total time taken to generate the page: 0.11908 seconds