Help with Oracle PL/SQL and Objects...

[Nazbit]

Hi,

I wonder if you can help me, I am having some trouble dealing with Oracle objects in PL/SQL. I can declare them, populate them and read from them without any issues.

But I am having some problems with trying to copy records in to other records of the same type, and also with updating existing records. I've made a mock up piece of code below to explain what I mean, it may have a few mistakes as I've written it in notepad but should be reasonably clear.

First I have created a record type, which contains attributes relating to a person.....

CREATE OR REPLACE
TYPE PERSON_RECORD_TYPE AS object (
				Person_ID		NUMBER(3),
				Person_Name	VARCHAR(20),
				Person_Age		NUMBER(2),
				static function new return PERSON_RECORD_TYPE );
									
/        


CREATE OR REPLACE                                             
TYPE BODY PERSON_RECORD_TYPE as            
    static function new return PERSON_RECORD_TYPE is 
    
    BEGIN
                                                        
        return PERSON_RECORD_TYPE (                 
                                    	NULL,
					NULL,
					NULL,
					NULL,
					NULL
        					);                
    END;
END;
/

Then I have created a table type, which is a table of the person record type......

CREATE OR REPLACE   
type PERSON_TABLE_TYPE as table of PERSON_RECORD_TYPE;  
/

Finally I have created a procedure which recieves an instance of the person table type and reads through it using a cursor.....

PROCEDURE ADMIN_PERSON (incoming_person	IN	PERSON_TABLE_TYPE)
IS

-- This is a local record declared as the same type as the incoming object
local_person  PERSON_TABLE_TYPE;

-- Cursor to select all from the incoming object
CURSOR select_person
IS

SELECT 	*
FROM 	TABLE ( cast (incoming_person AS PERSON_TABLE_TYPE));

BEGIN

-- Loop to process cursor results
FOR select_person_rec IN select_person
	LOOP
	
		/* Up to this point works fine...*/


		-- If I want to store the current cursor record in a local record of the same type, I can do this....
		local_person.person_id	 	:= select_person_rec.person_id;
		local_person.person_name 	:= select_person_rec.person_name;
		local_person.person_age		:= select_person_rec.person_age;

                -- QUESTION 1
		-- The above works fine, but in my real example there are a lot more fields		
		-- Why cant I set the local record to the value of the cursor record like this below..	
		
		local_person := select_person_rec; 

                -- The above line gives a pl/sql error - expression is of wrong type, (as far as I can see the records are of the same type?)
		
                -- QUESTION 2
		--Also how do you update an existing record within the original object, I have tried the following but it does not work

		UPDATE  incoming_person 
		SET		age = (age + 1)
		WHERE	incoming_person.person_id = '123';

                -- The error here is that the table does not exist
		
	END LOOP;
	
END;
/

So I hope that you can see from this, I have two problems. The first is that I can store the current cursor record in a local record if I assign each attribute one at a time, but my real example has a large number of attributes. So why can't I just assign the entire cursor record to the local cursor record?

I get a PL/SQL error "Expression is of wrong type" when I try to do this.

The second question is with regards to the update statement, obviously this doesn't work, it expects a table name here instead. So can anyone show me how I should update existing person records in the incoming table type to the procedure?

I hope this makes sense, but I don't think I have explained it very well!!

Any help will be gratefully recieved!!

Thanks

 

[kkelly]

Question 1

You need to select a column from your returning cursor eg "select_person_rec.Person_ID"

Question 2

incoming_person is not a view or a table, I think thats why it can't find it


Hope this helps