SQL & PL/SQL

Hi all,

I am trying to create a table function to retrieve some data that I want to be able to query in the FROM clause of a sql statement. The SELECT statement from the function is more complex, I made it simpler in my code extract for simplicity.

I am attaching all the code I used, and the errors I've got. Do you have an idea why am I getting those errors, and how to get around them?

Any help will be appreciated.

Thanks,
Paul

Here is the code:


CREATE TYPE str_tab_privs AS OBJECT
(org_id CHAR (10),
org_type_id CHAR (10));
/

CREATE TYPE str_tab_privs_table AS TABLE OF str_tab_privs;
/


CREATE FUNCTION getOrgName (org CHAR)
RETURN str_tab_privs_table PIPELINED IS

TYPE ref0 IS REF CURSOR;
cur0 ref0;
v_org CHAR (10);
out_rec str_tab_privs := str_tab_privs (NULL, NULL);

BEGIN
v_org := org;

OPEN cur0 FOR
'SELECT org_id, org_type_id '||
'FROM org_link '||
'WHERE parent_id := v_org OR org_id = := v_org '

USING v_org;

LOOP
FETCH cur0 INTO out_rec.org_id, out_rec.org_type_id;
EXIT WHEN cor0%NOTFOUND;
PIPE ROW(out_rec);
END LOOP;

CLOSE cur0;
RETURN;
END getOrgName;


Error messages I got at function creation:

Line # = 0 Column # = 0 Error Text = PL/SQL: Compilation unit analysis terminated
Line # = 2 Column # = 8 Error Text = PLS-00905: object SCHEMANAME.TYP_TAB_PRIVS_TABLE is invalid