Home » SQL & PL/SQL » SQL & PL/SQL » Table as variable and looping
Table as variable and looping [message #217816] Mon, 05 February 2007 13:48 Go to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
Writing a procedure I have the following:

DECLARE
v_key VARCHAR2(13);
BEGIN
FOR c1rec IN (SELECT key ,ROWID
FROM mytable
ORDER BY key)
LOOP
IF v_key=c1rec.key THEN
UPDATE mytable
SET flag ='D'
WHERE ROWID=c1rec.ROWID;
ELSE
v_key := c1rec.key;
END IF;

END LOOP;
COMMIT;
END;

This procedure dedupes records. I want to integrate this into an existing procedure I have however I am running into problems. I use the execute immediate syntax to supply a table name as a variable in my existing procedure however the existing procedure the are no loops.

In the snippet above mytable would need to be replaced by a variable however I have tried many different variations of trying to use execute immediate with this and have not come up with any solutions. If anyone has an idea on how to integrate this to work, or a workaround I am all ears. Thanks in advance
Re: Table as variable and looping [message #217819 is a reply to message #217816] Mon, 05 February 2007 14:25 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


Why do you need a PLSQL loop to "dedupe" ?

Srini


Re: Table as variable and looping [message #217820 is a reply to message #217819] Mon, 05 February 2007 14:31 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
This particular process creates a mail file, we dedupe so we do not send the same piece of mail to the same person more than once.

Is there a different way to dedupe? I am just using a process that has been in my dept since I have started here.

[Updated on: Mon, 05 February 2007 14:32]

Report message to a moderator

Re: Table as variable and looping [message #217822 is a reply to message #217816] Mon, 05 February 2007 14:43 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Google is your friend, but only if you use it.
Results 1 - 10 of about 662,000 for Oracle delete duplicate rows.

http://asktom.oracle.com has only bout 500 hits on DUPLICATE ROWS
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1224636375004

The best rule to follow with Oracle is if it can be done in SQL, it should never be done in PL/SQL.

[Updated on: Mon, 05 February 2007 14:47] by Moderator

Report message to a moderator

Re: Table as variable and looping [message #217832 is a reply to message #217822] Mon, 05 February 2007 15:53 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
Doing some testing I created 2 temp tables both with the same data.

220k records

Created index on both tables on the field to dedupe.

Using google which is my friend and found an example within 15 minutes after you posted... the example is still running.

My way deduped in under 30 seconds. 1400+ duplicate rows.

So could I get an answer to the question originally asked?
Re: Table as variable and looping [message #217833 is a reply to message #217832] Mon, 05 February 2007 15:58 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I'd be curious...can you post the pure SQL vesion that you used?
Re: Table as variable and looping [message #217835 is a reply to message #217816] Mon, 05 February 2007 16:11 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
IMO, asked & answered previously

http://www.orafaq.com/forum/m/217318/74940/#msg_217318
Re: Table as variable and looping [message #217837 is a reply to message #217835] Mon, 05 February 2007 17:03 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
ebrian here is what I used based off an example from google (it is still running by the way):

update works.dedupe_test
set elim_flag = 'D'
WHERE rowid not in
(SELECT MIN(rowid)
FROM works.dedupe_test
GROUP BY match_key);

Here is the other way I ran it which ran much faster...

DECLARE
CURSOR c1 IS SELECT match_key ,ROWID
FROM works.dedupe_test_2
ORDER BY match_key;
v_match_key VARCHAR2(13);
BEGIN
FOR c1rec IN c1 LOOP
IF v_match_key=c1rec.match_key THEN
UPDATE works.dedupe_test_2
SET elim_flag ='D'
WHERE ROWID=c1rec.ROWID;
ELSE
v_match_key := c1rec.match_key;
END IF;

END LOOP;
COMMIT;
END;

anacedent:
helping is not saying someones script is an "abomination", maybe I am not as SQL savvy as you nor have the experience (obviously) but I am trying to automate a standard script we have here at work so I can run a simple excute overnight instead of baby sitting it script by script. Tell me how to make it better... offer up some ideas... I am all about learning new tricks of the trade...


Re: Table as variable and looping [message #217838 is a reply to message #217816] Mon, 05 February 2007 17:36 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
If you expect help with design/implementation, you'll need to do a much better job of enumerating your EXACT requirements.
See on the surface, It boggles my brain to think that your application has that many tables (with different names) that require de-duping & why using dynamic SQL is even needed.
Depending upon the size of the datasets involved, you could always just load up MY_DEDUP_TABLE with various data loads & have only a single code base.


Alternatively you could dynamically create a VIEW against various tables; such that subsequent PL/SQL only uses the single VIEW name.

[Updated on: Mon, 05 February 2007 17:42] by Moderator

Report message to a moderator

Re: Table as variable and looping [message #217841 is a reply to message #217838] Mon, 05 February 2007 17:54 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
This is not about one process, it is about multiple. If I can get one process to work I can get them all to work. I have one table that has about 15 million records. This table has live feeds that update it. Based on criteria supplied by the higher ups we pull data based on sic codes etc to set up a mailing list. We then bounce this data against other tables for example an opt out table and others that are also updated on a regular basis. We do this on a regular basis... and I thought instead of doing these one by one, I could put them into a procedure, feed it a table name, and have it done over night so in the morning I could do whatever special adds or deletes need to be done. After all the "standard" scripts are run. Just trying to make a standard script run better and automated, sorry just learning and trying to make things more efficient for our workplace.

DBA's also dont let me create a view.

[Updated on: Mon, 05 February 2007 17:56]

Report message to a moderator

Re: Table as variable and looping [message #217842 is a reply to message #217816] Mon, 05 February 2007 18:12 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
May be it is you and/or your explanation.
May be it is me & my thick skull.
No where in your last post do I get any clue that any new table name gets included in the processing as an "input paramter".
Over a period of time the tables names touched by the procedures is fixed & finite.
If this is true, the NO dynamic SQL is needed (at all).

You may require some IF .... THEN .... ELSE... logic,
but IMO the tables for each branch are known & fixed.

I am tired of the greasy spot in road where the dead horse fell here & will not be returning to this spot again.

[Updated on: Mon, 05 February 2007 18:12] by Moderator

Report message to a moderator

Re: Table as variable and looping [message #217846 is a reply to message #217816] Mon, 05 February 2007 19:51 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
biohazardbill wrote on Mon, 05 February 2007 13:48

This procedure dedupes records. I want to integrate this into an existing procedure I have however I am running into problems. I use the execute immediate syntax to supply a table name as a variable in my existing procedure however the existing procedure the are no loops.

In the snippet above mytable would need to be replaced by a variable however I have tried many different variations of trying to use execute immediate with this and have not come up with any solutions. If anyone has an idea on how to integrate this to work, or a workaround I am all ears. Thanks in advance


Quoted from my first post maybe i worded it badly...

Anyone else?
Re: Table as variable and looping [message #218093 is a reply to message #217846] Tue, 06 February 2007 15:23 Go to previous message
biohazardbill
Messages: 57
Registered: March 2006
Member
Solution found...

this is a standalone and have added all other components to it:


create or replace procedure test2(
table_name varchar2)
is
type tCsr is ref cursor;
vCsr tCsr;
vSQL varchar2(2000);
v_match varchar2(100);

begin

vSQL := 'select dunsno, rowid from '||table_name;

open vCsr for vSQL;
loop
fetch vCsr into v_match;
exit when vCsr%notfound;

execute immediate 'update '||table_name||
' set flag =''D''
where match = '''||v_match||'''';

end loop;
close Vcsr
commit;
end test2;
Previous Topic: NEED HELP W/ CURSOR LOOP
Next Topic: UTL_FILE
Goto Forum:
  


Current Time: Sun Dec 04 22:43:38 CST 2016

Total time taken to generate the page: 0.07258 seconds