Home » SQL & PL/SQL » SQL & PL/SQL » What's wrong with this PROC? (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0)
What's wrong with this PROC? [message #340671] Wed, 13 August 2008 13:55 Go to next message
digigeektes
Messages: 2
Registered: August 2008
Junior Member
What is wrong with this query?

I'm getting:

Error: ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access



CREATE OR REPLACE PROCEDURE ADD_SET_TO_TAG (pTagId IN VARCHAR2, pSetId IN NUMBER)
AS
	MAX_GROUP_ID NUMBER;
	EXISTING_GROUP_ID NUMBER;
BEGIN
	SELECT FEATURE_GROUP_ID INTO EXISTING_GROUP_ID FROM SAVR_TAG_FEATURES WHERE TAG_ID=pTagId;
	SELECT MAX(FEATURE_GROUP_ID)+1 INTO NEW_GROUP_ID FROM SAVR_FEATURE_GROUP;

  IF (NEW_GROUP_ID IS NULL) THEN
    NEW_GROUP_ID := 1;
  END IF;
  
  IF (EXISTING_GROUP_ID = 0) THEN
		-- We have no FEATURE_GROUP_ID for this tag and set, so insert a new one using the MAX() from the groups table
    INSERT INTO SAVR_FEATURE_GROUP(FEATURE_GROUP_ID, FEATURE_SET_ID) VALUES(NEW_GROUP_ID, pSetId);
    UPDATE SAVR_TAG_FEATURES SET FEATURE_GROUP_ID = NEW_GROUP_ID WHERE TAG_ID=pTagId;
	ELSE
		-- We have a real FEATURE_GROUP_ID, so re-use it and add a rec to feature groups using it
		INSERT INTO	SAVR_FEATURE_GROUP(FEATURE_GROUP_ID, FEATURE_SET_ID) VALUES(EXISTING_GROUP_ID, pSetId);
	END IF;	

	EXCEPTION
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE);
			DBMS_OUTPUT.NEW_LINE;
			DBMS_OUTPUT.PUT_LINE('Message: ' || SUBSTR(SQLERRM, 1, 100));
			DBMS_OUTPUT.NEW_LINE;
END;
Re: What's wrong with this PROC? [message #340674 is a reply to message #340671] Wed, 13 August 2008 13:59 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
could be that you are trying to insert into a "database open for read-only access"
Re: What's wrong with this PROC? [message #340681 is a reply to message #340674] Wed, 13 August 2008 14:24 Go to previous messageGo to next message
digigeektes
Messages: 2
Registered: August 2008
Junior Member
Never mind for now. Something's wrong with the db, it is being taken down.
Re: What's wrong with this PROC? [message #340742 is a reply to message #340671] Thu, 14 August 2008 02:06 Go to previous messageGo to next message
sumanthd
Messages: 10
Registered: June 2008
Location: Hyderabad
Junior Member

Try this code

CREATE OR REPLACE PROCEDURE ADD_SET_TO_TAG(pTagId IN VARCHAR2,
                                           pSetId IN NUMBER) AS
  MAX_GROUP_ID      NUMBER;
  EXISTING_GROUP_ID NUMBER;
  NEW_GROUP_ID      NUMBER; ---modifed
BEGIN
  SELECT FEATURE_GROUP_ID
    INTO EXISTING_GROUP_ID
    FROM SAVR_TAG_FEATURES
   WHERE TAG_ID = pTagId;
  SELECT MAX(FEATURE_GROUP_ID) + 1
    INTO NEW_GROUP_ID
    FROM SAVR_FEATURE_GROUP;

  IF (NEW_GROUP_ID IS NULL)
  THEN
    NEW_GROUP_ID := 1;
  END IF;

  IF (EXISTING_GROUP_ID = 0)
  THEN
    -- We have no FEATURE_GROUP_ID for this tag and set, so insert a new one using the MAX() from the groups table
    INSERT INTO SAVR_FEATURE_GROUP
      (FEATURE_GROUP_ID,
       FEATURE_SET_ID)
    VALUES
      (NEW_GROUP_ID,
       pSetId);
    UPDATE SAVR_TAG_FEATURES
       SET FEATURE_GROUP_ID = NEW_GROUP_ID
     WHERE TAG_ID = pTagId;
  ELSE
    -- We have a real FEATURE_GROUP_ID, so re-use it and add a rec to feature groups using it
    INSERT INTO SAVR_FEATURE_GROUP
      (FEATURE_GROUP_ID,
       FEATURE_SET_ID)
    VALUES
      (EXISTING_GROUP_ID,
       pSetId);
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE);
    DBMS_OUTPUT.NEW_LINE;
    DBMS_OUTPUT.PUT_LINE('Message: ' || SUBSTR(SQLERRM, 1, 100));
    DBMS_OUTPUT.NEW_LINE;
END;


Smile
Re: What's wrong with this PROC? [message #340745 is a reply to message #340742] Thu, 14 August 2008 02:19 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
sumanthd what this code will do.Can it stop that error to happen../fa/1587/0/

Regards,
Rajat

[Updated on: Thu, 14 August 2008 02:22]

Report message to a moderator

Re: What's wrong with this PROC? [message #340748 is a reply to message #340742] Thu, 14 August 2008 02:32 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sumanthd wrote on Thu, 14 August 2008 09:06
Try this code

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE);
    DBMS_OUTPUT.NEW_LINE;
    DBMS_OUTPUT.PUT_LINE('Message: ' || SUBSTR(SQLERRM, 1, 100));
    DBMS_OUTPUT.NEW_LINE;
END;



Bug.
Previous Topic: Need help on sorting per column 1, then min/max from column 2 and corresponding column 3 (merged)
Next Topic: case sensitivity in table names: when from?
Goto Forum:
  


Current Time: Sun Dec 11 07:50:44 CST 2016

Total time taken to generate the page: 0.12062 seconds