Home » SQL & PL/SQL » SQL & PL/SQL » Updating records of a collection
Updating records of a collection [message #276357] Wed, 24 October 2007 23:58 Go to next message
rozyvaz
Messages: 3
Registered: October 2007
Junior Member
Confused

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #276520 is a reply to message #276516] Thu, 25 October 2007 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should NEVER use "select *".
Full stop.

Regards
Michel
Re: Updating records of a collection [message #276552 is a reply to message #276520] Thu, 25 October 2007 08:34 Go to previous messageGo to next message
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 #276558 is a reply to message #276552] Thu, 25 October 2007 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is as stupid as saying "I just know the name of the function I have to call without knowing which parameters it takes".

Regards
Michel
Re: Updating records of a collection [message #276668 is a reply to message #276357] Thu, 25 October 2007 22:41 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
well I still don't quite get your ultimate purpose, but I have this to say:

1) if you use table%rowtype, which if you are doing (slow by slow processing (uh I mean row by row processing)) then you better be using select *.

2) if you want to figure out the names of columns in a select statement at runtime you can use dynamic sql method 4. In particular when you do your required reading on the DBMS_SQL database supplied package, pay attention to dbms_sql.describe_columns.

I have a utilitiy somewhere that does this. Too bad I can't find it for you. Would have saved you a lot of typing and debugging you are going to have to do.

Good luck, Kevin

Re: Updating records of a collection [message #276718 is a reply to message #276668] Fri, 26 October 2007 01:47 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Kevin Meade wrote on Fri, 26 October 2007 05:41

slow by slow processing
Tom Kyte should have patented this expression. Very Happy

MHE
Re: Updating records of a collection [message #276876 is a reply to message #276718] Fri, 26 October 2007 10:22 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
hehehe Laughing
Re: Updating records of a collection [message #277440 is a reply to message #276520] Tue, 30 October 2007 02:27 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: How to alter index
Next Topic: Queries (merged 4)
Goto Forum:
  


Current Time: Fri Dec 02 20:50:29 CST 2016

Total time taken to generate the page: 0.30340 seconds