Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE...WHERE CURRENT OF with variable table name
UPDATE...WHERE CURRENT OF with variable table name [message #282062] Tue, 20 November 2007 13:53 Go to next message
gthiruva
Messages: 9
Registered: November 2007
Junior Member
I'm trying to write a PL/SQL stored procedure that does SELECTs and UPDATEs on a table, who's name is passed in as an argument to the procedure.

I figured out how to make the table name variable in the SELECT...FOR UPDATE statement using a weak cursor, but not in the UPDATE...WHERE CURRENT OF statement.

My procedure has a parameter called "table_name" which tells you the name of the table. My code looks something like this:

...
squery := 'SELECT load_seq, owner, lock_time' +
'FROM ' + table_name + ' ' +
'WHERE LOAD_SEQ <= seqorder AND (OWNER IS NULL OR LOCK_TIME < old_lock_time) AND ROWNUM <= num_rows';

OPEN scursor FOR squery;

LOOP
FETCH scursor into retseq, retowner, rettime;
EXIT when scursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('About to update');
UPDATE table_name
SET owner=new_owner, lock_time=new_lock_time
WHERE CURRENT OF scursor;
END LOOP;
...

Any thoughts? I get a ORA-00942 - table or view does not exist on the UPDATE statement.
Re: UPDATE...WHERE CURRENT OF with variable table name [message #282064 is a reply to message #282062] Tue, 20 November 2007 13:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How table_name can be a variable for "select for update" and not for "update where current of"?
This is meaningless.
You update the same that you select (isn't this the meaning of "current row"), so either you know the table either you don't but both can't be.

Regards
Michel
Re: UPDATE...WHERE CURRENT OF with variable table name [message #282068 is a reply to message #282064] Tue, 20 November 2007 14:43 Go to previous messageGo to next message
gthiruva
Messages: 9
Registered: November 2007
Junior Member
Michael - "UPDATE...WHERE CURRENT OF" is supposed to refer to the cursor, not the table. 'scursor' is the cursor that I created to run the select statement.

So that part (I'm pretty sure) is fine.

Anyway, after making my post I had a brainstorm, that maybe using "EXECUTE IMMEDIATE" will work. But now, I get a different problem.

Here's my re-write:

squery := 'SELECT load_seq, owner, lock_time ' ||
'FROM ' || table_name || ' ' ||
'WHERE LOAD_SEQ <= ' || seqorder || ' AND (OWNER IS NULL OR LOCK_TIME < ' || old_lock_time || ') AND ROWNUM <= ' || num_rows;

DBMS_OUTPUT.PUT_LINE('Setting UPDATE');

uquery := 'UPDATE ' || table_name || ' SET owner="' || new_owner || '", lock_time=' || new_lock_time || ' WHERE CURRENT OF scursor';

DBMS_OUTPUT.PUT_LINE('UPDATE set to ' || uquery);

DBMS_OUTPUT.PUT_LINE('Opening CURSOR');

OPEN scursor FOR squery;

LOOP
FETCH scursor into retseq, retowner, rettime;
EXIT when scursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('About to update');
EXECUTE IMMEDIATE uquery;
END LOOP;

Now, it seems like I solved the table name problem, but now I get an "invalid identifier" error on the attempt to update the "owner" column with a string that's also passed in as an argument.
Re: UPDATE...WHERE CURRENT OF with variable table name [message #282101 is a reply to message #282062] Tue, 20 November 2007 20:50 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
First, read about binding and USE it.

Quote:

'scursor' is the cursor that I created to run the select statement.
As SCURSOR is declared in the procedure,it is not visible in the dynamic SQL. You shall pass it through bind variable
uquery := 'BEGIN '||
 'UPDATE '|| table_name||
 ' SET owner=:1, lock_time=:2 WHERE CURRENT OF :3;'||
'END;';
...
EXECUTE IMMEDIATE uquery USING new_owner, new_lock_time, scursor;
However I am not sure, if this is possible. If no, you have another two options:
- run dynamically the whole PL/SQL block (within the CURSOR declaration and usage)
- run single UPDATE statement without using CURSOR at all
Re: UPDATE...WHERE CURRENT OF with variable table name [message #282112 is a reply to message #282068] Tue, 20 November 2007 22:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA
Senior Member
Without the "for update" and "where current of" this would be a simple problem. These are pl/sql syntax, not sql syntax. So, in order to execute them dynamically, they both must be placed within a pl/sql block by wrapping "begin" and "end" around them. Also, when you use dynamic sql like execute immediate, it cannot see anything outside of its own dynamic string that isn't bound and I don't think you can bind the cursor. So, you need to put both in the same dynamic block, as shown below.

If you have test data like:
 
SCOTT@orcl_11g> CREATE TABLE your_table
  2    (load_seq  NUMBER,
  3  	owner	  VARCHAR2 (30),
  4  	lock_time NUMBER)
  5  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO your_table VALUES (1, NULL, 9)
  3  INTO your_table VALUES (2, 'whoever', 5)
  4  INTO your_table VALUES (3, 'whoever', 5)
  5  INTO your_table VALUES (4, 'whoever', 5)
  6  INTO your_table VALUES (5, 'whoever', 12)
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> SELECT * FROM your_table
  2  /

  LOAD_SEQ OWNER                           LOCK_TIME
---------- ------------------------------ ----------
         1                                         9
         2 whoever                                 5
         3 whoever                                 5
         4 whoever                                 5
         5 whoever                                12


What are you trying to accomplish, in static sql, with a known table name is:
    
SCOTT@orcl_11g> DECLARE
  2    -- parameter values that you want to pass in:
  3    seqorder 	NUMBER	     := 3;
  4    old_lock_time	NUMBER	     := 8;
  5    num_rows 	NUMBER	     := 4;
  6    new_owner	VARCHAR2(30) := 'somebody';
  7    new_lock_time	NUMBER	     := 10;
  8    -- everything below this line must be done dynamically together:
  9    -- cursor for update:
 10    CURSOR scursor IS
 11    SELECT load_seq,owner,lock_time
 12    FROM your_table
 13    WHERE load_seq<=seqorder
 14    AND (owner IS NULL OR lock_time<old_lock_time)
 15    AND ROWNUM<=num_rows
 16    FOR UPDATE;
 17    -- variables to fetch into:
 18    retseq		 NUMBER;
 19    retowner 	 VARCHAR2 (30);
 20    rettime		 NUMBER;
 21  BEGIN
 22    OPEN scursor;
 23    LOOP
 24  	 FETCH scursor into retseq, retowner, rettime;
 25  	 EXIT when scursor%NOTFOUND;
 26  	 -- update using where current of your cursor:
 27  	 UPDATE your_table
 28  	 SET owner=new_owner, lock_time=new_lock_time
 29  	 WHERE CURRENT OF scursor;
 30    END LOOP;
 31    CLOSE scursor;
 32  END;
 33  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM your_table
  2  /

  LOAD_SEQ OWNER                           LOCK_TIME
---------- ------------------------------ ----------
         1 somebody                               10
         2 somebody                               10
         3 somebody                               10
         4 whoever                                 5
         5 whoever                                12


So, resetting the test data:
SCOTT@orcl_11g> -- reset data:
SCOTT@orcl_11g> ROLLBACK
  2  /

Rollback complete.

SCOTT@orcl_11g> SELECT * FROM your_table
  2  /

  LOAD_SEQ OWNER                           LOCK_TIME
---------- ------------------------------ ----------
         1                                         9
         2 whoever                                 5
         3 whoever                                 5
         4 whoever                                 5
         5 whoever                                12


If you replicate the whole static pl/sql block dynamically, you can accomplish the same thing and use the table_name as a parameter:
SCOTT@orcl_11g> -- dynamic procedure:
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE your_procedure
  2    (table_name    IN VARCHAR2,
  3  	seqorder      IN NUMBER,
  4  	old_lock_time IN NUMBER,
  5  	num_rows      IN NUMBER,
  6  	new_owner     IN VARCHAR2,
  7  	new_lock_time IN NUMBER)
  8  AS
  9    plsql_block	 VARCHAR2 (32767);
 10  BEGIN
 11    -- dynamic reproduction of static pl/sql block using bind variables:
 12    plsql_block :=
 13   'DECLARE
 14  	 CURSOR scursor IS
 15  	 SELECT load_seq,owner,lock_time
 16  	 FROM ' || table_name ||
 17    ' WHERE load_seq<= :b_seqorder
 18  	 AND (owner IS NULL OR lock_time< :b_old_lock_time)
 19  	 AND ROWNUM<= :b_num_rows
 20  	 FOR UPDATE;
 21  	 retseq 	   NUMBER;
 22  	 retowner	   VARCHAR2 (30);
 23  	 rettime	   NUMBER;
 24    BEGIN
 25  	 OPEN scursor;
 26  	 LOOP
 27  	   FETCH scursor into retseq, retowner, rettime;
 28  	   EXIT when scursor%NOTFOUND;
 29  	   UPDATE ' || table_name ||
 30  	 ' SET owner= :b_new_owner, lock_time= :bnew_lock_time
 31  	   WHERE CURRENT OF scursor;
 32  	 END LOOP;
 33  	 CLOSE scursor;
 34    END;';
 35    -- execution of dynamic pl/sql block using input parameters:
 36    EXECUTE IMMEDIATE plsql_block
 37    USING seqorder, old_lock_time, num_rows, new_owner, new_lock_time;
 38  END your_procedure;
 39  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXECUTE your_procedure ('your_table', 3, 8, 4, 'somebody', 10)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM your_table
  2  /

  LOAD_SEQ OWNER                           LOCK_TIME
---------- ------------------------------ ----------
         1 somebody                               10
         2 somebody                               10
         3 somebody                               10
         4 whoever                                 5
         5 whoever                                12

SCOTT@orcl_11g> Spool off

Re: UPDATE...WHERE CURRENT OF with variable table name [message #282200 is a reply to message #282062] Wed, 21 November 2007 02:57 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@ Barabara :

Hats off to the Real Expert Nod Nod Nod

We rarely see such an exclusive explanained reply in detail in Orafaq nowadays . We really miss your active participation.

Expecting such classic replies again.

Thumbs Up
Rajuvan

[Updated on: Wed, 21 November 2007 03:34]

Report message to a moderator

Re: UPDATE...WHERE CURRENT OF with variable table name [message #282338 is a reply to message #282200] Wed, 21 November 2007 10:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA
Senior Member
rajavu1 wrote on Wed, 21 November 2007 00:57

@ Barabara :

Hats off to the Real Expert :nod: :nod: :nod:

We rarely see such an exclusive explanained reply in detail in Orafaq nowadays . We really miss your active participation.

Expecting such classic replies again.

:thumbup:
Rajuvan



Hi Rajuvan,

I was kind of busy for a while with some personal things, plus I had hardware problems, got a new computer, then had internet connection problems, then download problems, but now I am running Oracle 11g on a new computer with a new modem and I'm back.

Regards,
Barbara
Re: UPDATE...WHERE CURRENT OF with variable table name [message #282342 is a reply to message #282062] Wed, 21 November 2007 10:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>new computer with a new modem
I'm surprised they still make modems.
Re: UPDATE...WHERE CURRENT OF with variable table name [message #282357 is a reply to message #282112] Wed, 21 November 2007 12:07 Go to previous messageGo to next message
gthiruva
Messages: 9
Registered: November 2007
Junior Member
Thanks Barbara, and everyone else for your help!

I got the 'EXECUTE IMMEDIATE' to work with bind variables as in:
    BEGIN 
        squery := 'SELECT rowidtochar(ROWID) ' ||
         'FROM ' || table_name || ' ' ||
         'WHERE LOAD_SEQ <= ' || seqorder ||
         ' AND (OWNER IS NULL OR LOCK_TIME < ' || old_lock_time || ') AND ROWNUM <= ' || num_rows
         ' FOR UPDATE NOWAIT SKIP LOCKED';

        uquery := 'UPDATE ' || table_name || ' SET owner=:1, lock_time=' || new_lock_time ||
         ' WHERE rowid = :2';

        OPEN scursor FOR squery; -- find matching rows

        numRows := 0; 
        LOOP 
            FETCH scursor into retrowID; 
            EXIT when scursor%NOTFOUND; 
            EXECUTE IMMEDIATE uquery using new_owner, rowidtochar(retRowID); 
            numRows := numRows + 1; 
        END LOOP; 

        CLOSE scursor; 
        COMMIT; 
        RETURN numRows; 
    END;


Note: It also seems cleaner to switch from using 'WHERE CURRENT OF' to selecting and updating on ROWID's.

This compiled, but triggered another problem when I tried to call the stored function from a select statement like:

select myfunc(...) from dual;


I got a DML error since Oracle caught me trying to execute an INSERT from within the SELECT I used. A little Googling and I found 'pragma autonomous_transaction' to shut down that error:

create or replace function myfunc(...)
RETURN NUMBER AS
    ...
    pragma   autonomous_transaction;
BEGIN 
    ...
    EXECUTE IMMEDIATE ....
    ...


And everything now works! This has been enlightening for me. I hope it's also been enlightening for others.

[Updated on: Wed, 21 November 2007 12:50]

Report message to a moderator

Re: UPDATE...WHERE CURRENT OF with variable table name [message #282360 is a reply to message #282342] Wed, 21 November 2007 12:21 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
anacedent wrote on Wed, 21 November 2007 17:48

>new computer with a new modem
I'm surprised they still make modems.

What about (A)DSL-modems?
Or cable-modems?

hmm, wondering how you are connected.. Wink
Re: UPDATE...WHERE CURRENT OF with variable table name [message #282361 is a reply to message #282342] Wed, 21 November 2007 12:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA
Senior Member
anacedent wrote on Wed, 21 November 2007 08:48

>new computer with a new modem
I'm surprised they still make modems.


I was previously using a PC5740 Express Card to access unlimited wireless broadband through Verizon Wireless. However, it was incompatible with the Windows Vista operating system on the new computer and a software upgrade would not have fixed it. I could have upgraded to a newer Express Card, but the USB720 modem with a Y-connector was recommended instead, because it is supposed to get better signal reception and speed. The USB modem is smaller than the Express Card, has a flip-up antenna and the Y-connector allows me to connect it to two USB ports at once and locate the antenna in a better spot away from the computer. It is also conveniently plugged into the two back USB ports, instead of sticking out of a slot in the side, so it is out of my way on the desk. I am still getting unlimited wireless broadband through Verizon Wireless. It is the only viable option in the area that I live in. I gave up on getting the phone company to fix the land lines. The neighbors are still having problems with theirs. Verizon seems to be the only company that has good wireless coverage in this area. I know I could get internet connection through a cable tv service, but there are other reasons that I don't do business with the local cable tv company. What I have is sufficient for my needs. The only thing that presented a problem was that it took multiple attempts to download Oracle 11g Enterprise Edition, so that I could install it. However, it appeared that everyone else was having similar download problems at the time, due to problems with the associated user authentication on Oracle's end.

O.K. Your turn. Make me jealous. What do you have? A dedicated T-1 line?

[Updated on: Wed, 21 November 2007 12:26]

Report message to a moderator

Re: UPDATE...WHERE CURRENT OF with variable table name [message #282366 is a reply to message #282062] Wed, 21 November 2007 12:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
At home I use COX Cable & they keep increasing the speed.
At work we have an OC-12 & multiple OC-3s & with the Commerce season already underway we are passing just over 800MB/s sustained during peak hours.
I have a gigE connection to my desktop!
So I work & play in a bandwidth rich environment now.

The Old Days were not good.
I can remember working over 300 baud acoustic coupled modem.
It was a Big Deal when I upgraded to a direct connect 1200 baud modem.
Re: UPDATE...WHERE CURRENT OF with variable table name [message #282367 is a reply to message #282366] Wed, 21 November 2007 12:53 Go to previous message
Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA
Senior Member
I have gotten spikes of 1.4 MB/s, but rarely is that sustained.
Previous Topic: Error on buffer size
Next Topic: joins problem?
Goto Forum:
  


Current Time: Thu May 09 06:19:18 CDT 2024