Home » SQL & PL/SQL » SQL & PL/SQL » Small Urgent Help Neede in PL?SQL
Small Urgent Help Neede in PL?SQL [message #375950] Mon, 15 December 2008 07:36 Go to next message
gov_sep27
Messages: 1
Registered: December 2008
Junior Member
Hi Frens
I hav a small requirement but really stuck in it!
Has 2 tables with the following columns

1.abc_y (notes,frmt)
sample data:
notes:
i live in texas and work in AIG.

2.abc_z(tags_name)
sample data:
tags_name:
live
in
work.
We shud look for tags_name in notes field of abc_y. If we encounter any tags_name in notes field they shud be removed and inserted in frmt field
Now the column frmt shud be updated to 'i texas and AIG'.
abc_y has 2 million rows and abc_z has 120 rows.
wrote the code sucessfuly but cannot bulk update it, which is really needed for me.
Plsssss help me.
I am havin some problm in FOR LOOP after FORALL in the folowing.

DECLARE
l_sql_strng VARCHAR2 (20000);

TYPE var_tab IS TABLE OF VARCHAR2 (20000)
INDEX BY BINARY_INTEGER;

l_text_arry var_tab;

CURSOR c1
IS
SELECT notes
FROM abc_y;
BEGIN
OPEN c1;

FETCH c1
BULK COLLECT INTO l_text_arry LIMIT 5000;
CLOSE c1;

FORALL i IN l_text_arry.FIRST .. l_text_arry.LAST

FOR j IN (SELECT tags_name
FROM abc_z)
LOOP
l_text_arry(i) := REPLACE (l_text_arry(i), j.tags_name);
END LOOP;

UPDATE abc_y
SET frmt = l_text_arry (i);

EXCEPTION
when others then
dbms_output.put_line('Failed!!!!');
END;

Thanks a million!!!

Please correct my code. thanks!!
Re: Small Urgent Help Neede in PL?SQL [message #375958 is a reply to message #375950] Mon, 15 December 2008 08:12 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Read OraFAQ Forum Guide, look especially at the topic "How to format your post?"

[Updated on: Mon, 15 December 2008 08:12]

Report message to a moderator

Re: Small Urgent Help Neede in PL?SQL [message #375963 is a reply to message #375950] Mon, 15 December 2008 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And also about words like "Urgent" and "Plsssss"

Regards
Michel
Re: Small Urgent Help Neede in PL?SQL [message #376015 is a reply to message #375950] Mon, 15 December 2008 12:32 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
FORALL is statement.

Please Read the full functionality and use it correctly.

http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10807/12_tune.htm#sthref1291

Thanks
Trivendra
Previous Topic: Insert pdf
Next Topic: what select send user?
Goto Forum:
  


Current Time: Fri Dec 09 05:58:42 CST 2016

Total time taken to generate the page: 0.08749 seconds