Rui Correia, January 03, 2011 - 11:12 am UTC
I had been searching and testing different options and couldn't find another solution. But now I am sure this is the only option.
Thanks!
You can also use a spreadsheet.
Chuck Jolley, January 03, 2011 - 12:31 pm UTC
I've done this by pasting a desc of the the tables into adjacent columns of a spreadsheet.
Then you can mix and match column names to your hearts content to build the insert statement.
View???
A reader, January 03, 2011 - 9:25 pm UTC
January 04, 2011 - 7:10 am UTC
would not be significantly different from what I wrote:
"you will have to list the columns explicitly since the column order does not match. "
You would have to create the view - listing the columns one by one in the right order - and then document furiously to ensure the view is maintained as the table is altered over time.
Rui Correia, January 04, 2011 - 7:22 am UTC
I followed Tom's advice and wrote a small procedure to write down my attribution from a record of table ORDERS type into a record of table ORDERS_HIST type:
DECLARE
w_col_name VARCHAR2(30);
w_string VARCHAR2(75);
CURSOR col_name IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = 'ORDERS'
ORDER BY COLUMN_ID;
BEGIN
OPEN col_name;
LOOP
FETCH col_name INTO w_col_name;
EXIT WHEN col_name%NOTFOUND;
w_string := ' o_rec_orders_hist.ems_orders_hist.'||w_col_name;
w_string := RPAD(w_string, 50, ' ');
DBMS_OUTPUT.PUT_LINE (w_string||' := i_rec_orders.ems_orders.'||w_col_name||';');
END LOOP;
CLOSE col_name;
END;
January 04, 2011 - 7:55 am UTC
Just as a matter of style and performance, you should code like this:
ops$tkyte%ORA11GR2> DECLARE
2
3
4 CURSOR col_name IS
5 SELECT rpad( ' o_rec_orders_hist.ems_orders_hist.'|| column_name, 50 ) ||
6 ' := i_rec_orders.ems_orders.'||column_name||';' data
7 FROM user_tab_columns
8 WHERE table_name = 'ORDERS'
9 ORDER BY COLUMN_ID;
10
11 BEGIN
12 for x in col_name
13 LOOP
14 DBMS_OUTPUT.PUT_LINE( x.data );
15 END LOOP;
16 END;
17 /
Avoid procedural code... Pack into SQL whatever you can.
Use implicit cursors if you are just fetching data - we implicitly array fetch 100 rows at a time (less latching generally, better performance). Also the resulting code is more compact and less buggy (more code = MORE BUGS, less code = LESS BUGS)
I know this is a throwaway piece of code - but in my experience the way we code a throwaway piece of code is indicative of our other 'real' code - so this advice is for the rest of your code.
Rui Correia, January 04, 2011 - 8:06 am UTC
Yes, it a throwaway code.
Nevertheless, great input for generally coding.
Thanks a lot!
sorry, but
Sokrates, January 04, 2011 - 8:24 am UTC
DECLARE
2
3
4 CURSOR col_name IS
5 SELECT rpad( ' o_rec_orders_hist.ems_orders_hist.'|| column_name, 50 ) ||
6 ' := i_rec_orders.ems_orders.'||column_name||';' data
7 FROM user_tab_columns
8 WHERE table_name = 'ORDERS'
9 ORDER BY COLUMN_ID;
10
11 BEGIN
12 for x in col_name
13 LOOP
14 DBMS_OUTPUT.PUT_LINE( x.data );
15 END LOOP;
16 END;
17 /
is tons of procedural code nobody needs.
why not just
SELECT ' o_rec_orders_hist.ems_orders_hist.'|| column_name || chr(10) ||
' := i_rec_orders.ems_orders.'||column_name||';' data
FROM user_tab_columns
WHERE table_name = 'ORDERS'
ORDER BY COLUMN_ID;
no PL/SQL at all is needed to execute a simple select ?
January 04, 2011 - 9:04 am UTC
true, but I assumed there would be more dbms_output's in there to do a bit more work. but true - just need the sql for this one.
Rui Correia, January 04, 2011 - 10:26 am UTC
That's fine, but am I missing something or if the table has like 100 columns and I need to do something with each one of them (lets say dbms_output as a simple example) I will have to use PL/SQL any way and will be even less performance than the cursor?
January 04, 2011 - 10:53 am UTC
I'm not following you at all on this one. Please clarify what you mean.
My mantra is:
o if you can do it in SQL - do it.
o if you prove you cannot do it in SQL - do it in as little pl/sql as possible.
o if plsql is not able to do it - consider a java stored procedure (but this is very very very rare)
o if plsql/java stored procedure is not sufficient - consider C or some other 3gl (this is exceedingly rare)
Rui Correia, January 04, 2011 - 11:08 am UTC
I totally understand your mantra (and agree).
But for this particular example, if I used just SQL as Sokrates posted, in order to do a DBMS_OUTPUT of each column, wouldn't I have to use a PL/SQL loop like:
SELECT COUNT(*)
INTO w_cnt
FROM user_tab_columns
WHERE table_name = 'ORDERS';
FOR i IN 1 .. w_cnt LOOP
SELECT column_name
INTO w_string
FROM user_tab_columns
WHERE table_name = 'EMS_BOOK_EVENTS'
AND column_id = i;
DBMS_OUTPUT.put_line(w_string);
END LOOP;
Maybe cum up with something neater but always using PL/SQL, right?
Thanks!
January 04, 2011 - 11:31 am UTC
you would never write code like that. You would just use SQL once again.
That is just:
SELECT column_name
FROM user_tab_columns
WHERE table_name = 'EMS_BOOK_EVENTS'
AND column_id <= (SELECT COUNT(*)
FROM user_tab_columns
WHERE table_name = 'ORDERS');
See mantra part 1 :)
whenever you find yourself looping over one result set and feeding it into another query - question yourself - ask "shouldn't that be a single sql statement"
Rui Correia, January 04, 2011 - 11:45 am UTC
I believe you did not understand what I meant, probably because I copy/paste something wrong:
SELECT COUNT(*)
INTO w_cnt
FROM user_tab_columns
WHERE table_name = 'ORDERS';
FOR i IN 1 .. w_cnt LOOP
SELECT column_name
INTO w_string
FROM user_tab_columns
WHERE table_name = 'ORDERS'
AND column_id = i;
DBMS_OUTPUT.put_line(w_string);
END LOOP;
I am actually just using the loop in order to be able to do "DBMS_OUTPUT.put_line(w_string);" because the SQL query returns 100 rows and I cannot print them.
Hope you understand me now and sorry for the mistake.
Thanks for everything.
January 04, 2011 - 11:52 am UTC
Nope, do not understand.
this would accomplish the same thing as your code:
select column_name
from user_tab_columns
where table_name = 'ORDERS'
order by column_id;
Rui Correia, January 04, 2011 - 12:23 pm UTC
So how would you print each column_name returned by your SQL code?
Cheers
January 04, 2011 - 1:26 pm UTC
just run it in sqlplus, sqlplus prints queries.
if you were using dbms_output, you must be in some interactive query environment - if it can show you dbms_output - it can CERTAINLY show you the output of a trivial query
It's easier in windows, for once.
A reader, January 05, 2011 - 9:25 am UTC
It took me a while to realize why anyone was writing any code for this.
IF you want to do this on a windows machine it becomes trivial ok. more trivial.
sqlplus in windows allows you to mark and copy an arbitrary rectangle.
So it is just a matter of
>desc my_table
Then mark a rectangle including the column names, but not their types, then press <ENTER>
Viola: your list is on the clipboard.
January 05, 2011 - 10:15 am UTC
ctl-select does that on my unix terminals as well. Not universal - but does work on OEL (Oracle Enterprise Linux)
and you'd still want VI after you did that so you could
:1,$s/.*/t1.& := t2.&/
right after to get your required output :)
:%s/.*/t1.& := t2.&/
Sokrates, January 05, 2011 - 11:40 am UTC
might save you a keystroke or two (nice one anyway !)
"ctl-select" means "<CTRL> C" ?
this should nothing have to do with the linux-kernel you are using (and especially nothing with OEL), but with the terminal - I bet gnome-terminal ?
January 05, 2011 - 11:56 am UTC
ctl-select means to hold the control key down while selecting text.
I used gnome-terminal on red hat and it didn't work. On OEL - ctl-select works just fine. It would be terminal specific - yes, not OS really. But since each OS ships with their "terminal implementation" - it will appear to be OS specific...
ctl-select
Sokrates, January 05, 2011 - 12:01 pm UTC
ok, thanks, ctl-select works for me also on gnome-terminal on red hat (didn't know that before !)
January 05, 2011 - 12:20 pm UTC
must be a setting some where :)
It can come in quite handy.
for those that didn't know - ALT-SELECT works in MS word as well - very nifty for picking out bits of text sometimes...
Well learn something new every day!
Chuck Jolley, January 05, 2011 - 3:35 pm UTC
<Ctrl>mark works in gnome-terminal through putty and xming from windows too! (logged into OEL5 anyway)
The putty terminal it's self does not seem to support it.
I had no idea.
Very handy trick. Thanks!
Now fire up that spreadsheet and you are in column name heaven ;)
putty
nedoboi, January 16, 2011 - 3:28 am UTC
In putty it's Alt+select
Anirban, January 20, 2011 - 3:58 am UTC
We use something simple
INSERT /*+ APPEND */
INTO ORDER_HIST
SELECT ORD.A,DL.X,ORD.B,ORD.C
FROM ORDER ORD, DUAL DL