I am trying to create some hierarchical data using Oracles object relation features.
I have defined a "Post" as follows:
create type Post as object (title varchar(20), body varchar(2000),
author varchar(200), parent REF Post,
MEMBER FUNCTION numReplies RETURN NUMBER,
PRAGMA RESTRICT_REFERENCES(numReplies, WNDS)
);
/
create table Posts of Post;
Now I want to write the function numReplies to find out how many posts have the "self" post as the parent:
create or replace type body Post AS
MEMBER FUNCTION numReplies RETURN number IS
i INT;
BEGIN
SELECT COUNT(*) INTO i FROM Posts p WHERE p.parent = SELF;
RETURN i;
END;
END;
/
But I get a compilation error:
Warning: Type Body created with compilation errors.
SQL> show errors;
Errors for TYPE BODY POST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: SQL Statement ignored
6/56 PL/SQL: ORA-00932: inconsistent datatypes: expected TSTER123.POST
got REF TSTER123.POST
I have tried doing REF(SELF) in the where clause and I get the same error message. I have also tried REF(p.parent) (which doesn't make sense anyways) and I get the error:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: SQL Statement ignored
6/49 PL/SQL: ORA-00904: "P"."PARENT": invalid identifier
I want to use the OR features (it's a class project) so I don't want to resort to adding an ID column to post and using that. How can I achieve this?
NOTE: the following query works, I just can't get it to work in a function using SELF.
SELECT title, (select count(*) from Posts p2 where p2.parent = REF(p))
FROM Posts p;
EDIT:
OK, I've gotten it to compile, but I'm not getting the data I expect.
Here is some dummy data I've used:
insert into Posts values ('foo', 'bar', 'tyler', null);
insert into Posts values ('hello world', 'bar', 'jatin', (select REF(p) FROM Posts p where p.title = 'foo'));
insert into Posts values ('bark', 'asd', 'tom', (select REF(p) FROM Posts p where p.title = 'hello world'));
insert into Posts values ('friendly', 'hgfags', 'tyler', (select REF(p) FROM Posts p where p.title = 'foo'));
And here is what compiles:
create or replace type body Post AS
MEMBER FUNCTION numReplies RETURN number IS
i INT;
BEGIN
SELECT COUNT(*) INTO i FROM Posts p WHERE DEREF(p.parent) = SELF;
RETURN i;
END;
END;
/
But, I'm not getting the correct results:
SQL> SELECT title, (select count(*) from Posts p2 where p2.parent = REF(p)) From Posts p;
TITLE (SELECTCOUNT(*)FROMPOSTSP2WHEREP2.PARENT=REF(P))
-------------------- ------------------------------------------------
foo 2
hello world 1
bark 0
friendly 0
SQL> select title, p.numReplies() from Posts p;
TITLE P.NUMREPLIES()
-------------------- --------------
foo 0
hello world 1
bark 0
friendly 0
If I use this:
create or replace type body Post AS
MEMBER FUNCTION numReplies RETURN number IS
i INT;
BEGIN
SELECT COUNT(*) INTO i FROM Posts p WHERE DEREF(p.parent).title = SELF.title;
RETURN i;
END;
END;
/
I get the expected results, but I'm requiring each post to have a unique title.