Skip to Main Content

SQL & PL/SQL

Announcement

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

Creating table partition names dynamically

MirithuApr 2 2024

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.

This post has been answered by Barbara Boehmer on Apr 2 2024
Jump to Answer
Comments
Post Details
Added on Apr 2 2024
4 comments
92 views