Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL Update using WHERE CURRENT OF CLAUSE (Oracle 10g)
Dynamic SQL Update using WHERE CURRENT OF CLAUSE [message #376481] Wed, 17 December 2008 09:19 Go to next message
vusha82
Messages: 2
Registered: December 2008
Junior Member
Hello all,

I have a procedure which dynamically fetches data and update them in the table using WHERE CURRENT OF clause.

My Code goes here.

First Procedure:
----------------

CREATE OR REPLACE PROCEDURE DUMMY(O_ERRMSG OUT VARCHAR2) IS
--DECLARE VARIABLES
BEGIN
      DELETE FROM AA;
      INSERT INTO AA (
                              SELECT
                                      TABLE_NAME,
                                      COLUMN_NAME,
                                      DATA_TYPE,
                                      DATA_LENGTH
                              FROM USER_TAB_COLS
      WHERE COLUMN_NAME IN ('AGE')
      AND DATA_LENGTH IN(10,6)
      COMMIT;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
           AOS_ERRMSG := SQLCODE ||' : ' ||SUBSTR(SQLERRM,1,200);
           PLOG.ERROR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
           PLOG.ERROR('AA - RECORDS NOT AVAILABLE' || ' ' ||O_ERRMSG);           
      RETURN;
      WHEN OTHERS THEN
           AOS_ERRMSG := SQLCODE ||' : ' ||SUBSTR(SQLERRM,1,200);
           PLOG.ERROR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
           PLOG.ERROR('AA - UNEXPECTED ERROR' || ' ' ||O_ERRMSG);
      RETURN;    
END;   

II Procedure:
-------------

CREATE OR REPLACE PROCEDURE TEST(O_ERRMSG OUT VARCHAR2) IS
  CURSOR PF_CUR IS
  SELECT TABLE_NAME,COLUMN_NAME FROM AA; 
  TYPE V_PF IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
  V_SQLC V_PFOLIO;
  E_KEY RAW(128) := UTL_RAW.CAST_TO_RAW('33669911');
  TAB_NAME VARCHAR2(4000);
  COL_NAME VARCHAR2(4000);
  ENCRYPTED_VALUE RAW(2048);
  ENCRYPTED_STRING RAW(64);
    
BEGIN

  DUMMY(O_ERRMSG);

  FOR T_CUR IN PF_CUR 
      LOOP
        
      TAB_NAME := T_CUR.TABLE_NAME;
      COL_NAME := T__CUR.COLUMN_NAME;
      
      EXECUTE IMMEDIATE 'SELECT ' || COL_NAME ||' FROM '|| TAB_NAME ||' WHERE ROWNUM<10'
      BULK COLLECT INTO V_SQLC;
                  
        DBMS_OUTPUT.PUT_LINE(V_SQLC.COUNT);
              
        FOR I IN 1..V_SQLC.COUNT
          LOOP
          
          DBMS_OUTPUT.PUT_LINE(V_SQLC.COUNT);
          
            ENCRYPTED_VALUE := DBMS_CRYPTO.ENCRYPT(SRC => UTL_RAW.CAST_TO_RAW (V_SQLC(I)), 
                                                   TYP => DBMS_CRYPTO.DES_CBC_PKCS5, 
                                                   KEY => E_KEY);
                                                   
            DBMS_OUTPUT.PUT_LINE('ENCRYPTED : ' || RAWTOHEX(ENCRYPTED_VALUE));
            
            ENCRYPTED_STRING := SUBSTR(RAWTOHEX(ENCRYPTED_VALUE),1,10);
            
            DBMS_OUTPUT.PUT_LINE('ENCRYPTED : ' || ENCRYPTED_STRING);
  
            EXECUTE IMMEDIATE 'UPDATE '||TAB_NAME ||
                              ' SET '||COL_NAME ||' = '|| ENCRYPTED_STRING
                              ||' WHERE CURRENT OF PF_CUR'; -- PROBLEM GOES HERE…..
                    
            INSERT INTO BB VALUES (TAB_NAME,COL_NAME,V_SQLC(I),'Y',SYSDATE,USER); -- FOR AUDIT PURPOSE
            COMMIT;
        END LOOP;    
      END LOOP;  
      
EXCEPTION
      WHEN NO_DATA_FOUND THEN
           AOS_ERRMSG := SQLCODE ||' : ' ||SUBSTR(SQLERRM,1,200);
           PLOG.ERROR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
           PLOG.ERROR('RECORDS NOT AVAILABLE' || ' ' ||O_ERRMSG);           
      RETURN;
      WHEN OTHERS THEN
           AOS_ERRMSG := SQLCODE ||' : ' ||SUBSTR(SQLERRM,1,200);
           PLOG.ERROR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
           PLOG.ERROR('UNEXPECTED ERROR' || ' ' ||O_ERRMSG);
      RETURN;     


------------------
Requirement is: I am fetching the table names those have the column names say, 'SALARY' from the system tables and popluating in to the table created by me as records. Then i try to open the table in a cursor, make a dynamic select and fetch the records of those columns in the tables.

Then, i try to iterate through the records return by the dynamic select and i encrypt those data. On the flow, once encrypted, i dynamically update that corresponding table with this new string using 'WHERE CURRENT OF CURSOR'.

The Problem here is, I am unable to update the current record in the cursor with the encrypted new string. It fails in the update statement and the error ORA-3001 - Unimplemented feature is been thrown.

Brainies.....pls help me out in solving this issue.

[Edit MC: add code tags]

  • Attachment: dummy.sql
    (Size: 2.31KB, Downloaded 197 times)

[Updated on: Wed, 17 December 2008 10:10] by Moderator

Report message to a moderator

Re: Dynamic SQL Update using WHERE CURRENT OF CLAUSE [message #376522 is a reply to message #376481] Wed, 17 December 2008 12:42 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
1) The "execute immediate" has no idea what is current in the calling procedure.

2) And when you want to use "current of", even without dynamic SQL, you would have to specify an "for update of ..." in the cursor first.

3) But you can't do that with a cursor that is based on dynamic SQL, as far as I know.


For those three reasons you can't use the "where current of" in the where clause of the update.

Is this a one-time thing during a application upgrade or something? When nobody else is doing anything on the database?

In that case you might get away with using the ROWID both in the select and the update. But I wouldn't recommend that if it's going into a procedure that is going to be used in the day-to-day operation of the application/database.
Re: Dynamic SQL Update using WHERE CURRENT OF CLAUSE [message #376527 is a reply to message #376481] Wed, 17 December 2008 12:50 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi

I have changed you code little bit to use ROWID. You can change according to your code.

DECLARE
  CURSOR pf_cur
  IS
    SELECT table_name, column_name
      FROM aa;

  TYPE v_pf IS TABLE OF VARCHAR2 (32767)
    INDEX BY BINARY_INTEGER;

  e_key              RAW (128)        := UTL_RAW.cast_to_raw ('33669911');
  tab_name           VARCHAR2 (4000);
  col_name           VARCHAR2 (4000);
  encrypted_value    VARCHAR2 (100);
  encrypted_string   RAW (64);

  TYPE cursor_type IS REF CURSOR;

  v_cursor           cursor_type;
  l_string           VARCHAR2 (32700);

  TYPE r IS RECORD (
    column_data   VARCHAR2 (32000),
    rid           ROWID
  );

  TYPE t IS TABLE OF r;

  p                  t                := t ();
  l_counter          PLS_INTEGER      := 2;
BEGIN
  --DUMMY(O_ERRMSG);
  FOR t_cur IN pf_cur
  LOOP
    l_string := 'SELECT ' || t_cur.column_name || ',rowid FROM ' || t_cur.table_name || ' WHERE ROWNUM<10';

    OPEN v_cursor FOR l_string;

    LOOP
      p.EXTEND (l_counter);

      FETCH v_cursor
       INTO p (l_counter).column_data, p (l_counter).rid;

      DBMS_OUTPUT.put_line ('TKSKLDK ' || p (l_counter).column_data);
      l_counter := l_counter + 1;
      EXIT WHEN v_cursor%NOTFOUND;
    END LOOP;

    FOR i IN p.FIRST .. p.LAST
    LOOP
      encrypted_value := DBMS_RANDOM.STRING (1, 10);

      EXECUTE IMMEDIATE    'UPDATE '
                        || t_cur.table_name
                        || ' SET '
                        || t_cur.column_name
                        || ' = '
                        || ''''
                        || TO_CHAR (encrypted_value)
                        || ''''
                        || ' WHERE rowid  = : rid'
                  USING p (i).rid;                                                     -- PROBLEM GOES HERE…..

      COMMIT;
    END LOOP;
  END LOOP;
END;


I have excluded you encryption package, I have given you example for UPDATE the data in the given table AA.

Thanks
Trivendra
Re: Dynamic SQL Update using WHERE CURRENT OF CLAUSE [message #376528 is a reply to message #376481] Wed, 17 December 2008 12:51 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
vusha82 wrote on Wed, 17 December 2008 10:19


CREATE OR REPLACE PROCEDURE DUMMY(O_ERRMSG OUT VARCHAR2) IS
--DECLARE VARIABLES
BEGIN
      DELETE FROM AA;
      INSERT INTO AA (
                              SELECT
                                      TABLE_NAME,
                                      COLUMN_NAME,
                                      DATA_TYPE,
                                      DATA_LENGTH
                              FROM USER_TAB_COLS
      WHERE COLUMN_NAME IN ('AGE')
      AND DATA_LENGTH IN(10,6)
      COMMIT;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
           AOS_ERRMSG := SQLCODE ||' : ' ||SUBSTR(SQLERRM,1,200);
           PLOG.ERROR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
           PLOG.ERROR('AA - RECORDS NOT AVAILABLE' || ' ' ||O_ERRMSG);           




This code cannot produce a NO_DATA_FOUND error.
Re: Dynamic SQL Update using WHERE CURRENT OF CLAUSE [message #376775 is a reply to message #376527] Thu, 18 December 2008 09:23 Go to previous messageGo to next message
vusha82
Messages: 2
Registered: December 2008
Junior Member
Guys,

Thanks for all your effort. Encryption works fine.

Now, i have ended up problem with this query.

V_SQLS := 'SELECT 1 INTO ' || PR_DATA ||' FROM MIOS'
||' WHERE TABLE_NAME NOT LIKE' ||' :1'
||' AND COLUMN_NAME NOT LIKE'||' :2'
||' AND ORIGINAL_VAL <>'||' :3'
||' AND ENCRY <> "Y" '
||' AND DLU <> SYSDATE';

EXECUTE IMMEDIATE V_SQLS USING TAB_NAME,COL_NAME,V_SQLC(I);

I am getting this error while executing the above said dynamic update statement "ORA-00936: missing expression".

Help me out pls.
Re: Dynamic SQL Update using WHERE CURRENT OF CLAUSE [message #376777 is a reply to message #376775] Thu, 18 December 2008 09:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
||' WHERE TABLE_NAME NOT LIKE' ||' :1'


What's the deal with concatenating two strings?


If you ever get an error, using dynamic sql on a generated string, the VERY first thing to do is to print the generated query and execute that manually in sqlplus.
Do that, and copy-paste that here.
Re: Dynamic SQL Update using WHERE CURRENT OF CLAUSE [message #376782 is a reply to message #376775] Thu, 18 December 2008 09:38 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

V_SQLS := 'SELECT 1 INTO ' || PR_DATA ||' FROM MIOS'
||' WHERE TABLE_NAME NOT LIKE' ||' :1'
||' AND COLUMN_NAME NOT LIKE'||' :2'
||' AND ORIGINAL_VAL <>'||' :3'
||' AND ENCRY <> "Y" '
||' AND DLU <> SYSDATE';

EXECUTE IMMEDIATE V_SQLS USING TAB_NAME,COL_NAME,V_SQLC(I);

I am getting this error while executing the above said dynamic update statement "ORA-00936: missing expression".


I am not able to find any update statement in the dynamic sql. However there is an issue with the dynamic sql. You cannot have any "into" clause in your dynamic. You will be getting an error.

Regards

Raj
Re: Dynamic SQL Update using WHERE CURRENT OF CLAUSE [message #376783 is a reply to message #376777] Thu, 18 December 2008 09:39 Go to previous message
_jum
Messages: 509
Registered: February 2008
Senior Member
If You use dynamic SQL
V_SQLS := 'SELECT 1 INTO ' || PR_DATA ||' 


Why not:
EXECUTE IMMEDIATE V_SQLS INTO PR_DATA USING TAB_NAME,COL_NAME,V_SQLC(I);
Previous Topic: migration to Oracle 10gR2 issue
Next Topic: Problem with SQL Space and Quotes
Goto Forum:
  


Current Time: Fri Dec 09 00:03:17 CST 2016

Total time taken to generate the page: 0.14713 seconds