CODEWITHSUNDEEP
oracle-databaseplsql
j_deany
j_deany

Reputation: 67

how to store and call a sql query from a table column?

Insert statement below holds details of another Table that has 3 columns (id,ins_dt,text_stuff) 

INSERT INTO swpurge_config
       (schema
       ,table_name
       ,table_alias
       ,driving_column
       ,deletion_predicate
       ,retention_period_type
       ,retention_period_value)
    VALUES
       ('CUSTOMERS_OWNER'
       ,'LAST_NAMES'
       ,'LN'
       ,'ins_dt'
       ,'WHERE ln.ins_dt < SYSDATE - p_retention_period_value
      AND ora_hash(ln.rowid, 8) = 1'
       ,'month'
       ,'6');

Aim: I am essentially trying to add a delete predicate in a varchar2 column. The idea is to call this column in a Procedure which will delete records up to 1000 rows:

    PROCEDURE delete_rows
   (
      p_schema                 IN VARCHAR2
     ,p_table_name             IN VARCHAR2
     ,p_table_alias            in varchar2
     ,p_retention_period_value IN VARCHAR2
     ,p_delete_predicate       IN VARCHAR2
   ) IS
      v_sql varchar2 (32000);
      v_row_limit pls_integer (1000);
   BEGIN

    v_sql := 'delete from ' || p_schema ||'.'|| table_name ||p_table_alias
     'older than '|| p_retention_period_value || p_delete_predicate;

    dbms_output.put_line(v_sql);

   END delete_rows;

Not sure about 2 things: 1. How to store the sql where clause in a table column? 2. How to execute the where clause as a statement in the procedure?

Thanks

Upvotes: 1

Views: 1536

Answers (1)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

you are talking about Dynamic SQL.

You can just store it inside VARCHAR2 in string format. then retrieve it 

 select deletion_predicate into v_sql from  swpurge_config where ...

then 

v_sql := "SELECT ...... FRom .... " || v_sql;

finally execute it 

 EXECUTE IMMEDIATE v_sql;

if your sql statement also includes parameters then

EXECUTE IMMEDIATE query_str
    using param1;

Upvotes: 2

Related Questions