Home » SQL & PL/SQL » SQL & PL/SQL » Help with object type
Help with object type [message #194871] Mon, 25 September 2006 16:24 Go to next message
ravvaru
Messages: 2
Registered: September 2006
Junior Member
I have created objects like this
CREATE or replace TYPE rajperson_obj AS OBJECT
(
PERSON_ID NUMBER(38),
DATE_UPDATED DATE ,
DATE_CONVERTED DATE ,
NYSID NUMBER(Cool ,
NYSID_ASSIGN_DATE DATE ,
NYSID_RECYCLE_DATE DATE ,

CONSTRUCTOR FUNCTION rajperson_obj(vnysid number) RETURN SELF AS RESULT
);


CREATE OR REPLACE TYPE BODY rajperson_obj AS
CONSTRUCTOR FUNCTION rajperson_obj(vnysid number)
RETURN SELF AS RESULT
AS
BEGIN
select PERSON_ID,
DATE_UPDATED,
DATE_CONVERTED,
NYSID,
NYSID_ASSIGN_DATE,
NYSID_RECYCLE_DATE
into
self.PERSON_ID,
self.DATE_UPDATED,
self.DATE_CONVERTED,
self.NYSID,
self.NYSID_ASSIGN_DATE,
self.NYSID_RECYCLE_DATE
from person
where nysid = vnysid;

RETURN;
END;
END;

CREATE or replace TYPE rajnysid_consol AS OBJECT (
NYSID NUMBER(Cool,
CONSOLNYSID NUMBER(Cool,
CONSOLDATE DATE
);

CREATE or replace TYPE rajconsol_array AS TABLE OF rajnysid_consol;

CREATE TYPE rajperson_info AS OBJECT (
persondata rajperson_obj,
consoldata rajconsol_array );

And i have written anonym block as follows


"declare
person_info rajperson_info;
v_consol_info rajperson_info.rajconsol_array := rajperson_info.consoldata();
i integer;
begin

person_info := person_info.rajperson_obj(6605914);
dbms_output.put_line('PERSON_ID '||person_info.PERSON_ID);
dbms_output.put_line('DATE_UPDATED '||person_info.DATE_UPDATED);
dbms_output.put_line('DATE_CONVERTED '||person_info.DATE_CONVERTED);
dbms_output.put_line('NYSID '||person_info.NYSID);
dbms_output.put_line('NYSID_ASSIGN_DATE '||person_info.NYSID_ASSIGN_DATE);
dbms_output.put_line('NYSID_RECYCLE_DATE '||person_info.NYSID_RECYCLE_DATE);

for a in ( select Nysid,Consol_Nysid,Consol_Date
from consolidated_nysid
where nysid = 6605914 )
loop
v_consol_info.EXTEND;
v_consol_info(v_consol_info.LAST) := rajperson_info.consoldata(a.Nysid, a.Consol_Nysid, a.Consol_Date);
i := v_consol_info.LAST;
dbms_output.put_line('i is '||i);
dbms_output.put_line('v_consol_info(i).NYSID '||v_consol_info(i).NYSID);
dbms_output.put_line('v_consol_info(i).CONSOLNYSID '||v_consol_info(i).CONSOLNYSID);
dbms_output.put_line('v_consol_info(i).CONSOLDATE '||v_consol_info(i).CONSOLDATE);
END LOOP;

Exception
when others then
dbms_output.put_line('Error '||sqlerrm);
end;
"
This is not working, i'm getting initialization error, Could somebody help me asap..thanks in advance
Re: Help with object type [message #195051 is a reply to message #194871] Tue, 26 September 2006 14:35 Go to previous message
ravvaru
Messages: 2
Registered: September 2006
Junior Member
Never mind Figured it out... Thanks
Previous Topic: Procedure runs slower in 10g
Next Topic: plsql Substring function
Goto Forum:
  


Current Time: Sat Dec 10 09:05:24 CST 2016

Total time taken to generate the page: 0.08148 seconds