Hello Community,
I am using SQL Developer Version 23.1.0.097 on Oracle Database 19c Enterprise Edition Version 19.20.0.0.0 database.
I am querying the default sample database - schema: HR, table: EMPLOYEES.
I have created a regular table function as follows, that is executing successfully:
CREATE OR REPLACE TYPE OBJ_TYPE AS OBJECT
(
FIRST_NAME VARCHAR2(100),
LAST_NAME VARCHAR2(100),
EMAIL VARCHAR2(50),
HIRE_DATE DATE
);
CREATE OR REPLACE TYPE TYP_TABLE AS TABLE OF OBJ_TYPE;
CREATE OR REPLACE FUNCTION GET_EMPLOYEES(DDATE DATE)
RETURN TYP_TABLE
IS
V_TABLE TYP_TABLE := TYP_TABLE();
BEGIN
FOR REC IN (
SELECT FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE
FROM HR.EMPLOYEES
WHERE HIRE_DATE = DDATE
) LOOP
V_TABLE.EXTEND;
V_TABLE(V_TABLE.COUNT) := OBJ_TYPE(REC.FIRST_NAME, REC.LAST_NAME, REC.EMAIL, REC.HIRE_DATE);
END LOOP;
RETURN V_TABLE;
END;
Now, in a scenario where the HR.EMPLOYEES table is partitioned daily, and the partition name is in the format PYYYYMMDD, such that a query on a partition would look like:
SELECT * FROM HR.EMPLOYEES PARTITION (P20240402),
how can I re-write the above function such that when I input the DDATE parameter, this date will be used to create the table partition name? e.g.
When I execute the function as SELECT * FROM TABLE(GET_EMPLOYEES('02-APR-24')); ,this date will be used in the partition name as HR.EMPLOYEES PARTITION (P20240402)?
Thank you.