Home » SQL & PL/SQL » SQL & PL/SQL » How to attain DYNAMIC PL/SQL Table Structure Comparing and Copying?
How to attain DYNAMIC PL/SQL Table Structure Comparing and Copying? [message #252464] Wed, 18 July 2007 22:28 Go to next message
murali317
Messages: 3
Registered: July 2007
Junior Member
Hi,
I am facing a problem.

From the frontend i am passing the data as a rowtype structure/recordset say rec_auth of table auth which is having more than 150 fields . But in the backend i have one more table say unauth which is an unauth table of b. So depending on the Authstatus the record will be inserted into table auth or unauth. but the unauth tabe is having 125 fields and of which some fields are not there in auth. say 100 fields are common.

Now after insertion i need to query the data back to the user by fetching it to rec_auth. But i cant use select * into rec_auth from unauth table.
It would be great if i can go further with some thing like this

select * into rec_unauth from unauth
and i need to copy all the fields in the rec_unauth to rec_auth
by using a loop which loops through the col names of auth table and get it from unauth if it is present

rec_auth.fieldname = rec_auth.fieldname

how to loop through this. i couldn find an answer and Right now i have created a view having the same structure of auth and select from unauth. But for this requirement i have added an extra database object. It would be better if there is any possibility of doing it the other way.

Hoping for the reply

Regards

Murali

[Updated on: Wed, 18 July 2007 22:34]

Report message to a moderator

Re: How to attain DYNAMIC PL/SQL Table Structure Comparing and Copying? [message #252547 is a reply to message #252464] Thu, 19 July 2007 02:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I didn't really understand your requirment, but can you create two views with identical columns and data types over each table and use those for shipping data between the two?

Ross Leishman
Re: How to attain DYNAMIC PL/SQL Table Structure Comparing and Copying? [message #252554 is a reply to message #252547] Thu, 19 July 2007 02:58 Go to previous messageGo to next message
murali317
Messages: 3
Registered: July 2007
Junior Member
Hi,

I have attained it by creating a view of unauth table with same stucture of auth table where i have added the missing field.But for this requirement only i have added a database object. It would have been batter if there is any mechanism where i can copy say some thing like

rec_auth.field1 = rec_unauth.field1;
rec_auth.field2 = rec_unauth.field2;
and so on;

but i want this to be dynamic.
loop
rec_auth.fieldname = rec_unauth.fieldname;

where fieldname is a vairable.

Is that possible.

Cheers

Murali
Re: How to attain DYNAMIC PL/SQL Table Structure Comparing and Copying? [message #252870 is a reply to message #252554] Fri, 20 July 2007 07:56 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
murali317 wrote on Thu, 19 July 2007 17:58
It would have been batter if there is any mechanism where i can copy say some thing like
...

Why would it be better? Do you think a few dozen lines of confusing dynamic SQL is better than a few simple lines that any programmer can understand?

Hang on, don't answer. I'd rather not know.

If it satisfies your curiosity, I'm pretty sure it can't be done. There are some terribly clever people on this site, and I hate to issue a challenge to write code that I would sack someone for, but.....

It seems to me that you have two disparate PL/SQL RECORD types. The only way to dynamically determine the common columns of each type would be to use dynamic SQL. Now if I remember correctly, dynamic SQL cannot reference variables from outside - they must be passed in as bind variables. But you cannot bind record types.

It is maybe possible if you used database objects instead of PL/SQL records, but then you've created two objects on the database to serve an insanely complex piece of code (I'm not even sure its possible) instead of one simple view to serve a simple piece of code.

Choice seems simple to me.

Ross Leishman

[Updated on: Fri, 20 July 2007 07:58]

Report message to a moderator

Previous Topic: Numeric and alphabetic data operations
Next Topic: is windows xp compatible with oracle 9i?
Goto Forum:
  


Current Time: Sat Dec 03 06:06:11 CST 2016

Total time taken to generate the page: 0.14652 seconds