Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL escaping variable with a single quote (19.2)
PL/SQL escaping variable with a single quote [message #685458] Tue, 18 January 2022 19:21 Go to next message
Unclefool
Messages: 43
Registered: August 2021
Member
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. How can I test if v_days is NULL then set it to 30?

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


[Updated on: Tue, 18 January 2022 22:40]

Report message to a moderator

Re: PL/SQL escaping variable with a single quote [message #685459 is a reply to message #685458] Wed, 19 January 2022 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, in your test case, for SQL statements, either use ";" or "/" but not both as in this case you execute twice the statement:
SQL> select sysdate from dual;
SYSDATE
-------------------
19/01/2022 07:33:07

1 row selected.

SQL> /
SYSDATE
-------------------
19/01/2022 07:33:08

1 row selected.
For a SQL statement, ";" tells SQL*Plus this is the end of the statement and asks it to execute it.
"/" tells SQL*Plus to execute what's inside its input buffer.

SQL> DECLARE
  2        v_str  VARCHAR2 (500);
  3         v_days  NUMBER := 0;
  4  BEGIN
  5      FOR cur_r IN(
  6        SELECT TABLE_NAME, PARTITIONING_TYPE, COLUMN_NAME, DATA_TYPE
  7  FROM USER_PART_TABLES
  8      JOIN USER_PART_KEY_COLUMNS ON NAME = TABLE_NAME
  9      JOIN USER_TAB_COLS USING (TABLE_NAME, COLUMN_NAME)
 10  where OBJECT_TYPE = 'TABLE' AND
 11  PARTITIONING_TYPE='RANGE' AND
 12  regexp_like(DATA_TYPE,'^DATE$|^TIMESTAMP*')
 13  )
 14     LOOP
 15    --DBMS_OUTPUT.put_line('Table '|| cur_r.table_name);
 16
 17    v_str := 'select DAYS FROM partition_rention where TABLE_NAME = '''||
 18            dbms_assert.simple_sql_name(cur_r.table_name)||'''';
 19
 20    DBMS_OUTPUT.put_line(v_str);
 21    begin
 22      execute immediate v_str into v_days;
 23    exception when no_data_found then v_days := to_number(null);
 24    end;
 25    DBMS_OUTPUT.put_line('--> '||v_days);
 26
 27     END LOOP;
 28  END;
 29  /
select DAYS FROM partition_rention where TABLE_NAME = 'INTERVAL_DATE'
-->
select DAYS FROM partition_rention where TABLE_NAME = 'T1'
--> 15
select DAYS FROM partition_rention where TABLE_NAME = 'T2'
-->
select DAYS FROM partition_rention where TABLE_NAME = 'T3'
--> 15

PL/SQL procedure successfully completed.
Re: PL/SQL escaping variable with a single quote [message #685460 is a reply to message #685459] Wed, 19 January 2022 06:28 Go to previous message
Unclefool
Messages: 43
Registered: August 2021
Member
Perfect. Thanks
Previous Topic: Unexpected Grants To Public
Next Topic: Dynamically pass table name in PLSQL
Goto Forum:
  


Current Time: Wed May 18 17:30:06 CDT 2022