| Updating records of a collection [message #276357] |
Wed, 24 October 2007 23:58  |
rozyvaz
Messages: 3
Registered: October 2007
|
Junior Member |
|
|
Hi All
I need to take all the rows from a table and apply certain logic to the rows if the data in there is incorrect or dirty...
Here is how i am going about it:
/*CREATE CURSOR TO GET ALL THE ROWS FOR THE PARTICULAR TABLE*/
CURSOR table_info_cur IS
SELECT * FROM ( SELECT source_tab FROM table_info WHERE tab_id = p_tab_id);
/*CREATE A TABLE COLLECTION TYPE TO HOLD THIS CURSOR INFORMATION SO THAT ALL UPDATES OPERATIONS CAN BE PERFORMED ON THIS RECORDSET ITSELF*/
TYPE TableSet IS TABLE OF table_info_cur%ROWTYPE;
table_info_set TableSet;
/*THEN I BULK COLLECT ALL THE DATA IN THE TABLE LIKE THIS:*/
SELECT * BULK COLLECT INTO table_info_set FROM (SELECT source_tab FROM table_info WHERE tab_id = p_tab_id);
/* THEN LOOP THRU EACH ROW OF THE COLLECTION */
FOR i IN table_info_set.FIRST .. table_info_set.LAST
LOOP
NOW FOR EACH ROW OF THIS COLLECTION I NEED TO UPDATE THE VALUE , SOME THING LIKE
table_info_set(i).FOR_A_PARTICULAR_COLUMN : = 0;
CAN SOMEBODY PLEASE TELL ME HOW DO I GET THE COLUMN NAME OF THE COLLECTION THAT I WANT TO UPDATE. PLZ LOOK AT THE ATTACHED CODE...
AWAITING REPLY
THANKS
IM ATTACHING THE SAMPLE CODE FOR REFERENCE
|
|
|
|
| Re: Updating records of a collection [message #276372 is a reply to message #276357] |
Thu, 25 October 2007 00:55  |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).
Please keep off your finger from the shift key.
The field names are the same as the table ones.
Regards
Michel
[Updated on: Thu, 25 October 2007 00:56] Report message to a moderator |
|
|
|
| Re: Updating records of a collection [message #276516 is a reply to message #276372] |
Thu, 25 October 2007 06:58 |
rozyvaz
Messages: 3
Registered: October 2007
|
Junior Member |
|
|
Ok I will try another way:
how can i access the column name of a collection when i have done a select * to get all the rows of a table.
some thing like..
First i make a cursor to get all the table rows and then i make a table type using the cursor to access the columns in the table.
here's a sample code
DECLARE
CURSOR c1 IS
SELECT * FROM TABLE_NAME;
TYPE TableSet IS TABLE OF c1%ROWTYPE;
table_info_set TableSet;
BEGIN
OPEN c1;
LOOP
FETCH table_info_cur BULK COLLECT INTO table_info_set;
EXIT WHEN table_info_set.COUNT = 0;
END LOOP;
FOR i IN table_info_set.FIRST .. table_info_set.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(table_info_set.[B]col_name[/B]);
END LOOP;
CLOSE c1;
END;
how can i get the column name to be printed here.
|
|
|
|
|
|
| Re: Updating records of a collection [message #276552 is a reply to message #276520] |
Thu, 25 October 2007 08:34 |
rozyvaz
Messages: 3
Registered: October 2007
|
Junior Member |
|
|
Thanks for the prompt reply.
But i have used SELECT * from because im developing a generic procedure which gets only the table id and nothing else is known at run time.
Hence the importance of using select * and of getting to know the column name.
kindly suggest.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Updating records of a collection [message #277440 is a reply to message #276520] |
Tue, 30 October 2007 02:27 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You should NEVER use "select *".
Full stop.
I disagree. In this case, where he is using a %ROWTYPE, useing SELECT * will cause less problems.
This piece of code will adapt automatically to changes in the table structure, whereas rewriting the code to use a list of columns would make the code less resiliant to change.
|
|
|
|
| Re: Updating records of a collection [message #277446 is a reply to message #277440] |
Tue, 30 October 2007 02:55 |
|
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Just for information:
We already talked on this several times.
If some are interested on the arguments for and against can search on this board.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
