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

If the LOOP statement comes between a BEGIN statement and its matching EXCEPTION or END; then the END LOOP statement has to come between them as well. If i want an EXCEPTION handler that catches errors that may occur within the loop, and then continues the loop then the exception handler doesn't appear to work.

The code was restructured to remove the expectation handler.

I already have a query that finds the tables and columns I'm interested in. Now, for each table in that result set, I want to get the matching days value from the partition_retention table if there is one, and if there is no matching row in partition_retention, I want the table_name anyway with a default value (30) for days. To do this I implemented an outer JOIN like this:

BEGIN
    FOR td IN
    (
        SELECT      table_name
        ,      NVL (pr.days, 30) AS days
        FROM       user_part_tables      pt
          JOIN       user_part_key_columns pkc ON pkc.name = pt.table_name
          JOIN       user_tab_cols            tc  USING (table_name, column_name)
        LEFT JOIN partition_retention   pr  USING (table_name)
        WHERE       pkc.object_type      = 'TABLE'
        AND       pt.partitioning_type = 'RANGE'
        AND      REGEXP_LIKE (tc.data_type, '^DATE$|^TIMESTAMP*')
        ORDER BY  table_name -- not needed, but could be handy in debugging
    )
    LOOP
            -- For debugging:
          dbms_output.put_line ( td.table_name
                     || ' = table_name, '
                     || TO_CHAR (td.days)
                     || ' = days'
                     );
          -- call procedure to remove old PARTITIONs here.
    END LOOP;
END;
/

Output from my sample data:

T1 = table_name, 15 = days
T2 = table_name, 30 = days
T3 = table_name, 5 = days

Answer 2

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?