Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

21c: PLS-00201: identifier 'SYS.ODCITABPIPELINED' must be declared

SmithJohn45Apr 3 2024 — edited Apr 3 2024

today i used ChatGPT just to test it and enter below prompt:

how i can create oracle table function to get employees and departments data

and it give the code below:


-- Create object types for department and employee
CREATE OR REPLACE TYPE DepartmentType AS OBJECT (
   department_id NUMBER,
   department_name VARCHAR2(50)
);
/
CREATE OR REPLACE TYPE EmployeeType AS OBJECT (
   employee_id NUMBER,
   employee_name VARCHAR2(50),
   department_id NUMBER
);
/
-- Create the pipelined function
CREATE OR REPLACE FUNCTION GetDeptEmpData RETURN SYS.ODCITABPIPELINED IS
BEGIN
   FOR dept_emp IN (
       SELECT d.department_id, d.department_name, e.employee_id, e.employee_name
       FROM departments d
       JOIN employees e ON d.department_id = e.department_id
   ) LOOP
       PIPE ROW(DepartmentType(dept_emp.department_id, dept_emp.department_name));
       PIPE ROW(EmployeeType(dept_emp.employee_id, dept_emp.employee_name, dept_emp.department_id));
   END LOOP;
   RETURN;
END;
/

when i execute these on livesql.oracle.com it creates Types but showed error for Function as below:

Errors: FUNCTION GETDEPTEMPDATA
Line/Col: 0/0 PL/SQL: Compilation unit analysis terminated
Line/Col: 1/32 PLS-00201: identifier 'SYS.ODCITABPIPELINED' must be declared

i thought it has 19c so i executed on my 21c Ent. ( user HR ) but same error:

PLS-00201: identifier 'SYS.ODCITABPIPELINED' must be declared

so i need to know about 'SYS.ODCITABPIPELINED' is it part of ver.23 ? or there is permission issue? i tried grant execute but it shows error too.

SQL> grant execute on SYS.ODCITABPIPELINED to HR;
grant execute on SYS.ODCITABPIPELINED to HR
                    *
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

SQL> show user
USER is "SYS"
SQL>

regards

This post has been answered by Mike Kutz on Apr 3 2024
Jump to Answer
Comments
Post Details
Added on Apr 3 2024
9 comments
125 views