Home » SQL & PL/SQL » SQL & PL/SQL » ORA-ORA-00604 and ORA-01400 problem while executing stored procedure
ORA-ORA-00604 and ORA-01400 problem while executing stored procedure [message #38446] Thu, 18 April 2002 02:31 Go to next message
Chaitanya Sravanth
Messages: 10
Registered: April 2002
Junior Member
CREATE OR REPLACE PROCEDURE SP_ADD_LOCATION(
ErrorStatus OUT Number,
VRoom IN MLOCATION.ROOM%TYPE,
VSlot IN Varchar2,
VReturn OUT Varchar2)
AS
No_of_commas Integer;
Intposition Integer;
Position Integer;
LCount Integer;
I Integer;
Tot_Rooms Number(1);
Tot_Slots Number(1);
Room_Name MLOCATION.ROOM%TYPE;
Varposition Integer;
Slotposition Integer;
Slot_No Number(4);
Position1 Integer;
Vint Number(3);
BEGIN
Intposition := 1;
No_of_commas := 0;
Vint := length(VRoom);
-- to get how many rooms are getting added.
FOR I IN 1..Vint LOOP

-- to get the position of commas in th total string
Intposition := INSTR(VRoom, ',', Intposition,1);

IF Intposition != NULL then
No_of_commas := No_of_commas + 1;
ELSE
EXIT LOOP;
END IF;

END LOOP;

Tot_Rooms := No_of_commas + 1;
Position := 0;

FOR I IN 1..Tot_Rooms LOOP
--passing one room at a time into this variable.

Varposition := (Position+1);
Room_Name := SUBSTR(VRoom, Varposition, INSTR(VRoom,',',Varposition,1)-1);
Position := INSTR(VRoom,',',Varposition,1);

--checking if Room already exists.
SELECT COUNT(*) INTO LCount FROM MLOCATION WHERE UPPER(ROOM) = UPPER(Room_Name);

IF LCount = 1 THEN
ErrorStatus := 1001;

IF VReturn = "" THEN
VReturn := Room_Name;
ELSE
VReturn := VReturn || ',' || Room_Name;
END IF;
END IF;

END LOOP;

Position1 := 0;
--If Room Names do not exist.

IF VReturn = "" THEN

FOR I IN 1..Tot_Rooms LOOP

Varposition := (Position+1);
Room_Name := SUBSTR(VRoom, Varposition, INSTR(VRoom,',',Varposition,1)-1);
Position := INSTR(VRoom,',',Varposition,1);

Slotposition := (Position1+1);
Slot_No := SUBSTR(VSlot, Slotposition, INSTR(VSlot,',',Slotposition,1)-1);
Position1 := INSTR(VSlot,',',Slotposition,1);

INSERT INTO MLOCATION(LOCATION_ID, ROOM, NO_OF_SLOTS) VALUES(LOCATION_ID.NEXTVAL, Room_Name, Slot_No);

FOR I IN 1..Slot_No LOOP
INSERT INTO LOCATION_MAP_DETAILS(LOCATION_DETAILS_ID,ROOM,SLOT) VALUES(LOCATION_DETAILS_ID.NEXTVAL,Room_Name,I);
COMMIT;
END LOOP;

END LOOP;
ErrorStatus := 0;
ELSE
EXIT;
END IF;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
ErrorStatus:=3001;
END;

It is displaying the following msg. when executing the above stored procedure.

CREATE OR REPLACE PROCEDURE SP_ADD_LOCATION(
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01400: cannot insert NULL into ("SYS"."OBJ$"."NAME")

Table for reference

CREATE TABLE MLOCATION (
LOCATION_ID NUMBER (3) PRIMARY KEY,
ROOM VARCHAR2 (20) UNIQUE NOT NULL,
NO_OF_SLOTS NUMBER (4) NOT NULL
);

i need the solution very urgently. help needed regd. this problem.
Thanks in advance.
Re: ORA-ORA-00604 and ORA-01400 problem while executing stored procedure [message #39273 is a reply to message #38446] Tue, 02 July 2002 13:14 Go to previous messageGo to next message
ki
Messages: 8
Registered: February 2002
Junior Member
Please use single-quotes
instead of double-quotes.

Inessa.
Re: ORA-ORA-00604 and ORA-01400 problem while executing stored procedure [message #41384 is a reply to message #38446] Thu, 26 December 2002 07:17 Go to previous messageGo to next message
suman
Messages: 8
Registered: January 2002
Junior Member
Hi,

I have run into the same problem. My stored proc also returns error:
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01400: cannot insert NULL into ("SYS"."OBJ$"."NAME").
So I am looking for a solution and am wondering if replacing double quotes with single quotes solved your problem or not. In my case, it doesn't seem to help.
Please let me know your feedback.

Thanks
Suman.
Re: ORA-ORA-00604 and ORA-01400 problem while executing stored procedure [message #41391 is a reply to message #41384] Thu, 26 December 2002 23:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
Here are some simple tests to demonstrate the differences. The first procedure, with double quotes, produces the errors that you are receiving. The second procedure, with single quotes in place of the double quotes, eliminates the error, compiles, and executes. However, note that comparing to an empty string is not the same as checking if it is null, as demonstrated by the third procedure, which uses IS NULL, instead of = ' ', and provides a different result. If the tests below don't clarify things, then please post your complete code and any other relevant items.

SQL> CREATE OR REPLACE PROCEDURE test
  2    (VReturn OUT VARCHAR2)
  3  AS
  4  BEGIN
  5    IF VReturn = "" THEN
  6      DBMS_OUTPUT.PUT_LINE ('YES');
  7    ELSE
  8      DBMS_OUTPUT.PUT_LINE ('NO');
  9    END IF;
 10  END test;
 11  /
CREATE OR REPLACE PROCEDURE test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01400: cannot insert NULL into ("SYS"."OBJ$"."NAME")

SQL> 
SQL> 
SQL> CREATE OR REPLACE PROCEDURE test
  2    (VReturn OUT VARCHAR2)
  3  AS
  4  BEGIN
  5    IF VReturn = '' THEN
  6      DBMS_OUTPUT.PUT_LINE ('YES');
  7    ELSE
  8      DBMS_OUTPUT.PUT_LINE ('NO');
  9    END IF;
 10  END test;
 11  /

Procedure created.

SQL> 
SQL> 
SQL> VARIABLE g_var VARCHAR2
SQL> SET SERVEROUTPUT ON
SQL> 
SQL> 
SQL> EXEC test (:g_var)
NO

PL/SQL procedure successfully completed.

SQL> PRINT g_var

G_VAR
--------------------------------

SQL> 
SQL> 
SQL> CREATE OR REPLACE PROCEDURE test
  2    (VReturn OUT VARCHAR2)
  3  AS
  4  BEGIN
  5    IF VReturn IS NULL THEN
  6      DBMS_OUTPUT.PUT_LINE ('YES');
  7    ELSE
  8      DBMS_OUTPUT.PUT_LINE ('NO');
  9    END IF;
 10  END test;
 11  /

Procedure created.

SQL> EXEC test (:g_var)
YES

PL/SQL procedure successfully completed.

SQL> PRINT g_var

G_VAR
--------------------------------

Re: ORA-ORA-00604 and ORA-01400 problem while executing stored procedure [message #41393 is a reply to message #41384] Fri, 27 December 2002 07:47 Go to previous messageGo to next message
suman
Messages: 8
Registered: January 2002
Junior Member
Thanks Barbara. It clarifies..

Suman.
icon8.gif  Re: ORA-ORA-00604 and ORA-01400 problem while executing stored procedure [message #164286 is a reply to message #38446] Wed, 22 March 2006 15:15 Go to previous messageGo to next message
billc
Messages: 1
Registered: March 2006
Location: usa
Junior Member
Can anyone clue me in as to why database error codes give absolutely no clue to what the problem is? Why is a recursive error thrown about a single qoute double qoute mix up? And not only does it not point out the line that the error is on, but it gives you a line that the error is not on. Debugging java or vb or even c++ is much easier than debugging oracle. Why is that? Isn't database development supposed to be a bit lighter than application development? I swear I'm beggining to hate oracle just because of the crumby error codes. They would do better to just print line that says "error". Is there a free oracle editor out there that has an intellisense type of debugging feature, or even one that has better error codes? I give oracle an 'F' just because of their error codes. BAH! I'm sorry ppl, I've kept silent for 3 semesters of oracle's crappy error codes.

Thank you for the solution previous poster...it would have taken me quite a while to figure out that cryptic garbage that oracle calls an error code.
Re: ORA-ORA-00604 and ORA-01400 problem while executing stored procedure [message #192625 is a reply to message #39273] Wed, 13 September 2006 00:49 Go to previous message
ank_ora
Messages: 1
Registered: September 2006
Location: India
Junior Member
Thanks alot buddy.

avoiding double quotes solved my problem.
Previous Topic: Shell and PLSQL
Next Topic: To get some 10 out of n for each attribute in the same table.
Goto Forum:
  


Current Time: Sat Dec 03 13:51:45 CST 2016

Total time taken to generate the page: 0.10062 seconds