Oracle PLSQL escaping a single quote

Question

My apologies for the verbose post but the setup is necessary to show my problem and ask a question.

In the anonymous block below I'm trying to construct a string, which encapsulates the table in a single quote ie 'T1' but I've been struggling for the past hour and can use some help.

Secondly, I purposely left out a row in the table partition_rention for table name T2. I suspect a NULL will be returned into the variable when the statement is executed. Will this work?

if v_days is NULL 
then  
  v_days  := 30
 END IF;

Thanks in advance to all who answer and your expertise

create table partition_rention
(
   TABLE_NAME VARCHAR2(30) NOT NULL,
DAYS NUMBER(6),
CONSTRAINT Check_gt0
    CHECK (DAYS> 0)
   ); 
/

INSERT into partition_rention (TABLE_NAME, DAYS) 
 VALUES
 ('T1', 15);
/
INSERT into partition_rention (TABLE_NAME, DAYS) 
 VALUES
 ('T3', 15);
/

CREATE TABLE t1 (     
 seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
   dt   DATE
)
PARTITION BY RANGE (dt)
INTERVAL (NUMTODSINTERVAL(7,'DAY'))
(
   PARTITION OLD_DATA values LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
);
/

INSERT /*+ APPEND */ into t1 (dt)
with dt (dt, interv) as (
select date '2022-01-01', numtodsinterval(30,'MINUTE') from dual
union all
select dt.dt + interv, interv from dt
where dt.dt + interv < date '2022-01-15')
select dt from dt;
/

create index ix_local on t1 (dt) local;
/

CREATE TABLE t2
 (     
     seq_num NUMBER  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
   dt   DATE
)
PARTITION BY RANGE (dt)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(
   PARTITION OLD_DATA values LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
);
/

INSERT /*+ APPEND */ into t2 (dt)
with dt (dt, interv) as (
select date '2022-01-01', numtodsinterval(30,'MINUTE') from dual
union all
select dt.dt + interv, interv from dt
where dt.dt + interv < date '2022-01-15')
select dt from dt;
/

create index ix_global on t2 (dt);
/

CREATE TABLE t3 (
seq_num NUMBER  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt TIMESTAMP)
  PARTITION BY RANGE (dt) 
  INTERVAL ( NUMTODSINTERVAL (1, 'DAY') ) ( 
    PARTITION OLD_DATA VALUES LESS THAN (TIMESTAMP '2022-01-01 00:00:00.000000')
  );
/

INSERT /*+ APPEND */ into t3 (dt)
SELECT TIMESTAMP '2022-01-01 00:00:00'
         + (LEVEL - 1) * INTERVAL '5' MINUTE
         + MOD(LEVEL - 1, 10) * INTERVAL '0.1' SECOND
FROM   DUAL
CONNECT BY
       TIMESTAMP '2022-01-01 00:00:00'
         + (LEVEL - 1) * INTERVAL '5' MINUTE
         + MOD(LEVEL - 1, 10) * INTERVAL '0.1' SECOND < DATE '2022-01-15';
/

DECLARE
      v_str  VARCHAR2 (500);
       v_days  NUMBER := 0;
BEGIN 
    FOR cur_r IN(
      SELECT TABLE_NAME, PARTITIONING_TYPE, COLUMN_NAME, DATA_TYPE
FROM USER_PART_TABLES 
    JOIN USER_PART_KEY_COLUMNS ON NAME = TABLE_NAME
    JOIN USER_TAB_COLS USING (TABLE_NAME, COLUMN_NAME)
where OBJECT_TYPE = 'TABLE' AND 
PARTITIONING_TYPE='RANGE' AND
regexp_like(DATA_TYPE,'^DATE$|^TIMESTAMP*')
)
   LOOP
  --DBMS_OUTPUT.put_line('Table '|| cur_r.table_name);
 
  v_str := 'select DAYS FROM partition_rention into  v_days where TABLE_NAME = '||cur_r.table_name||'';
 

DBMS_OUTPUT.put_line(v_str);

-- execute immediate v_str;

   END LOOP;
END;

Statement processed.
select DAYS FROM partition_rention into  v_days where TABLE_NAME = T1
select DAYS FROM partition_rention into  v_days where TABLE_NAME = T2
select DAYS FROM partition_rention into  v_days where TABLE_NAME = T3

Answer 1

There is no reason for dynamic SQL. It would be this:

begin
    select DAYS 
    into v_days
    FROM partition_rention  
    where TABLE_NAME = cur_r.table_name;
exception
   when NO_DATA_FOUND THEN
      v_days := 30;
end;

If you really insist for dynamic SQL then it would be this one

begin
   v_str := 'select DAYS FROM partition_rention where TABLE_NAME = :t';
   execute immediate v_str into v_days using cur_r.table_name;
exception
   when NO_DATA_FOUND THEN
      v_days := 30;
end;
      

NB, I guess the next step might be to drop outdated partitions. For this have a look at How to drop multiple interval partitions based on date?