Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: Cursor optimization

    I have a simple procedure that loops through a 500 record cursor and then does two single record inserts for each record in the 500. The script runs fine in my development environment (the cursor is 300 records long), but seems to hang in other environments.

    Any ideas to optimize this? All of the tables are index on the ID key at a minimum.

    Thanks,

    Robert

    Code:
    CREATE OR REPLACE PROCEDURE createRole (roleName IN varchar2, roleDesc IN varchar2)
     IS
     	domainID		PLS_INTEGER :=0;
    	roleID			PLS_INTEGER :=0;
    	err_msg			VARCHAR2(300) := NULL;
    
    	CURSOR domain_cursor IS SELECT ID FROM DRDomain;
    
    BEGIN
    	DBMS_OUTPUT.PUT_LINE('role name  ' || roleName);
    	DBMS_OUTPUT.PUT_LINE('role description ' || roledesc);
    	
    	FOR rec IN domain_cursor
       	LOOP
    
    		domainID := rec.id;
    
    		INSERT INTO DRRole (name, description)
    		VALUES (roleName, roleDesc);
    		
    		INSERT INTO DRDomainMembership (domainID, entityID, entityTypeID)
    			SELECT domainID, R.ID, 10 from DRRole R WHERE R.name = roleName
    			AND R.ID NOT IN (select entityID from DRDomainMembership
          		where entityTypeID = 10);		
    		
       	END LOOP;
    	COMMIT;
    
    END

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I don't know why you'd want to insert the same role row 500 times? Try this instead:
    Code:
    CREATE OR REPLACE PROCEDURE createRole (roleName IN varchar2, roleDesc IN varchar2)
     IS
        l_roleID          PLS_INTEGER :=0;
    
    BEGIN
        DBMS_OUTPUT.PUT_LINE('role name  ' || roleName);
        DBMS_OUTPUT.PUT_LINE('role description ' || roledesc);
    
        INSERT INTO DRRole (name, description)
        VALUES (roleName, roleDesc)
        RETURNING id INTO l_roleID;
        
        INSERT INTO DRDomainMembership (domainID, entityID, entityTypeID)
                SELECT domainID, l_roleID, 10
                FROM   DRDomain D
                MINUS
                SELECT domainID, entityID, entityTypeID
                from DRDomainMembership;
            
        COMMIT;
    
    END

  3. #3
    Join Date
    May 2004
    Posts
    184
    Tony,

    Thank you. The nature of the structure requires a unique RoleID for every record in another table, hence the cursor loop and 500 inserts. I did learn something from your example though. "RETURNING " I assume returns the value of the newly inserted row, which is really what I would have preferred to use for this script.

    It ended up being out of date indexes.

    Thanks for your help.

    Robert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •