hi all,
my local environment (VM): CentOS 7, DB 21c XE, Apex 21.1, ORDS 21.1, Tomcat 9
tables are Departments and Employees from sample schema HR and created a package pkg_test on local Oracle Apex 21.1 environment and test it with SQL Command window in Apex, running successfully.
same thing copy/pasted in my workspace at apex.oracle.com, when running same SQL but here it is throwing error ORA-00904: "EMP_NAME": invalid identifier .
please check and help to identify Where is the problem?
my package:
create or replace package pkg_test as
--
/*
to demonstrate 2 Oracle features:
1) use of CAST
2) Pipelined Table Function
*/
--
cursor Emp_Info is (Select
CAST (0 AS NUMBER) Emp_Id,
CAST ('X' AS VARCHAR2(40)) Emp_Name,
CAST (0 AS NUMBER) Emp_Sal,
CAST (to_date(sysdate) AS DATE) Hire_Date
FROM dual);
--
type emp_info_table_type is table of Emp_Info%rowtype;
--
pkg_dept_id departments.department_id%TYPE;
--
procedure set_dept_id (p_dept in number);
--
function get_dept_id return number;
--
function get_emps_info (p_dept in number) return emp_info_table_type PIPELINED;
end pkg_test;
--========================================
create or replace package body pkg_test is
--
procedure set_dept_id (p_dept in number) is
begin
pkg_dept_id := p_dept;
end;
--
function get_dept_id return number is
begin
return pkg_dept_id;
end;
--
-- ====================================================
function get_emps_info (p_dept in number)
return emp_info_table_type
PIPELINED
is
xx number:= 0;
begin
for m_rec in (
Select e.employee_id Emp_Id, e.first_name||' '||e.last_name Emp_Name,
e.salary Emp_Sal,
e.hire_date Hire_Date
From employees e
Where e.department_id = p_dept
)
loop
--
pipe row ( m_rec );
end loop;
--
return;
--
exception
when others then raise;
end get_emps_info;
--
end pkg_test;
SQL to test package:
select emp_id, emp_name from table(pkg_test.get_emps_info (30));
ORA-00904: "EMP_NAME": invalid identifier
regards