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 |
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 #282068 is a reply to message #282064] |
Tue, 20 November 2007 14:43 |
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 |
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 |
|
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 #282338 is a reply to message #282200] |
Wed, 21 November 2007 10:15 |
|
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 #282357 is a reply to message #282112] |
Wed, 21 November 2007 12:07 |
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 #282361 is a reply to message #282342] |
Wed, 21 November 2007 12:22 |
|
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
|
|
|
|
|
Goto Forum:
Current Time: Thu May 09 06:19:18 CDT 2024
|