PL/SQL reusable dynamic sql program for same type of task but different table and column

Thank you for reply guys. I kind of solved my problem.

I used to try to update data with ref cursor in dynamic SQL using "where current of" but I now know that won't work.

Then I tried to use %rowtype to store both 'id' and 'clob' in one variable for future updating but turns out weak ref cursor can't use that type binding either.

After that I tried to use record as return of an ref cursor and that doesn't work on weak cursor either.

On the end, I created another cursor to retrieve 'id' separately along with cursor to retrieve 'clob' on the same time then update table with that id.

---

I'm now working on a Oracle data cleaning task and have a requirement like below:

There are 38 tables(maybe more in the future) and every table has one or multiple column which type is Clob. I need to find different keyword in those columns and according to a logic return binary label of the column and store it in a new column.

For example, there is a table 'myTable1' which has 2 Clob columns 'clob1' and 'clob2'. I'd like to find keyword 'sky' from those columns and store '0'(if not found) or '1'(if found) in two new columns 'clob1Sky','clob2Sky'.

I know if I could write it on a static way which will provide higher efficiency but I have to modify it for those very similar tasks every time. I want save some time on this so I'm trying to write it in a reusable way and not binding to certain table.

But I met some problem when writing the program. My program is like below:

create or replace PACKAGE body LABELTARGETKEYWORD 
    as
    /**
    @param varcher tableName: the name of table I want to work on
    @param varchar colName: the name of clob column
    @param varchar targetWord: the word I want to find in the column
    @param varchar newColName: the name of new column which store label of clob 
    */ 
    PROCEDURE mainProc(tableName varchar, colName varchar,targetWord varchar,newColName varchar2)
    as
        type c_RecordCur is ref cursor;
        c_sRecordCur c_recordCur;
        /*other variables*/
    begin
        /*(1) check whether column of newColName exist
          (2) if not, alter add table of newColName
          (3) open cursor for retrieving clob
          (4) loop cursor
              (5) update set the value in newColName accroding to func labelword return
          (6) close cursor and commit*/
    end mainProc;

    function labelWord(sRecord VARCHAR2,targetWord varchar2) return boolean...
    function ifColExist(tableName varchar2,newColName varchar2) return boolean...
END LABELTARGETKEYWORD;

Most DML and DDL are written in dynamic sql way.

The problem is when I write the (5) part, I notice 'Where current of' clause can not be used in a ref cursor or dynamic sql statement. So I have to change the plan.

I tried to use a record(rowid,label) to store result and alter the table later.(the table only be used by two people in my group, so there won't be problem of lock and data changes). But I find because I'm trying to use dynamic sql so actually I have to define ref cursor with return of certain %rowtype and basically all other variables, %type in dynamic sql statement. Which makes me feel my method has something wrong.

My question are:

1. If there a way to define %type in dynamic sql? Binding type to variable in dynamic SQL?

2. Could anybody give me a hint how to write that (5) part in dynamic SQL?

3. Should not I design my program like that?

4. Is it not the way how to use dynamic SQL or PLSQL?

I'm very new to PL/SQL. Thank you very much.

According to Tom Kyte's advice, to do it in one statement if it can be done in one statement, I'd try to use a single UPDATE statement first:

CREATE TABLE mytable1 (id NUMBER, clob1 CLOB, 
  clob2 CLOB, clob1sky NUMBER, clob2sky NUMBER ) 
LOB(clob1, clob2) STORE AS SECUREFILE (ENABLE STORAGE IN ROW);

INSERT INTO mytable1(id, clob1, clob2) 
SELECT object_id, object_name, object_type FROM all_objects
 WHERE rownum <= 10000;

CREATE OR REPLACE 
PROCEDURE mainProc(tableName VARCHAR2, colName VARCHAR2, targetWord VARCHAR2, newColName VARCHAR2)
IS
  stmt VARCHAR2(30000);
BEGIN
  stmt := 'UPDATE '||tableName||' SET '||newColName||'=1 '||
          'WHERE DBMS_LOB.INSTR('||colName||','''||targetWord||''')>1';
  dbms_output.put_line(stmt);
  EXECUTE IMMEDIATE stmt;
END mainProc;
/

So, calling it with mainProc('MYTABLE1', 'CLOB1', 'TAB', 'CLOB1SKY'); fires the statement

UPDATE MYTABLE1 SET CLOB1SKY=1 WHERE DBMS_LOB.INSTR(CLOB1,'TAB')>1

which seems to do the trick:

SELECT * FROM mytable1 WHERE clob1sky=1;

id  clob1    clob2  clob1sky clob2skiy
33  I_TAB1   INDEX  1   
88  NTAB$    TABLE  1   
89  I_NTAB1  INDEX  1   
90  I_NTAB2  INDEX  1   
...