Home » SQL & PL/SQL » SQL & PL/SQL » dynamic cursor in 8.1.7
dynamic cursor in 8.1.7 [message #239413] Tue, 22 May 2007 05:10 Go to next message
Lama
Messages: 45
Registered: February 2006
Member

Am using database 8.1.7 and am trying to make a db trigger for a table inorder to do the following -->

lets say I have table x :

x
----
col1
col2
col3
col4
.
.
.


and I have another table containing these columns

y
-----
table_name
column_name
value


and whenever I do update on any column in x I have to insert in y ALL the columns (even if they are not updated )....

So I need a way to do this dynamically because I have many tables and many columns too...


Note : I tried using a cursor as select * from x , and am trying to link that with user_tab_columns but I need a way to read the cursor as positions :
ex.
delcare
cursor c is
select * from x ;
begin
for rec in c loop
------can i write here rec.first_column or first position!!!!!
end loop;
end;


and I tried using ref cursor but it didnt work coz i think it needs db 10g

Re: dynamic cursor in 8.1.7 [message #239416 is a reply to message #239413] Tue, 22 May 2007 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
can i write here rec.first_column or first position!!!!

No and it is meaningless as there is no first column as there is no order in the columns.

Regards
Michel
Re: dynamic cursor in 8.1.7 [message #239424 is a reply to message #239416] Tue, 22 May 2007 06:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm going to have to disagree here - columns do have a defined order, both in a table, and in a query.
Re: dynamic cursor in 8.1.7 [message #239453 is a reply to message #239424] Tue, 22 May 2007 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In a query, I agree this is the order you gave them in the query.
In a table, I disagree. The current implementation is to give you the columns in the order they have been defined in the create table but it may change. It may be the order they are stored or the reverse order, why not?

Regards
Michel
Re: dynamic cursor in 8.1.7 [message #239500 is a reply to message #239453] Tue, 22 May 2007 09:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, in pragmatic terms, because changing the default order would break many, many applications out there.
Granted, they may well be badly written, but if they work now, and a future release of Oracle were to break them all, there would be a ferocious outcry from the (license paying) users.

The column order is defined in the database - COLUMN_ID in Dba_Tab_Cols, which comes from col# in col$. Whether the queries that build the data set order by this is a question I cannot answer.

Anyone fancy lurking on AskTom until he's taking questions?
Re: dynamic cursor in 8.1.7 [message #239507 is a reply to message #239500] Tue, 22 May 2007 09:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I agree that in pragmatic terms there will be little chance that changes but Oracle does not give (and imo will never give) a "first_column" or a "column(i)" attribute to table records (rowtype).

Do you notice that internally Oracle prefers using col$.intcol#?

Regards
Michel
Re: dynamic cursor in 8.1.7 [message #239699 is a reply to message #239507] Wed, 23 May 2007 02:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
IntCol# appears to be a version of col# that included columns in nested tables, and other non-standard data types.

Looking at it, I suppose that the order that columns are stored in the table has never been the order that they are returned by a query (Longs are always stored at the end of each row for example). That means that somewhere Oracle must be generating an order for the logical columns that it returns (as opposed to the physical columns).
We'd need a look at the sql generated when you run a query, to see if there are any Order By clauses in there. If there were, then the order would presumably be guaranteed.
Re: dynamic cursor in 8.1.7 [message #239708 is a reply to message #239699] Wed, 23 May 2007 02:51 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
regarding the column ordering ......

I think it makes sense to have a "defined" order of the columns someplace.

There are for example a lot of report generating etc GUI tools out there.

Just consider you have some application that is installed a few thousand times. You have to do some reports with that tool. At some point you will have to have a list of colums where you can choose the columns you want which should be ordered in a way that is "logical" from a data design perspective.

Or just normal development. It makes sense to have the columns in a "select * from..." returned in the same order every time, and you can usually assume that columns that have some relationship to each other are close together, e.g. the date and the user of the last change to some data.

While "default" order of the columns might not be of importance for the finished application, it's really helpfull during development.
Previous Topic: how to find out column names ?
Next Topic: SQL Oracle Loader for EBS
Goto Forum:
  


Current Time: Fri Dec 02 12:03:14 CST 2016

Total time taken to generate the page: 0.06113 seconds