Comments embedded.
On Oct 12, 9:51 pm, SANDEEP REDDY <tosandeepyan...@gmail.com> wrote:
> Here Is The Way To Write Such Queries In RealTime Operations
My way was fine for the example shown. I do agree that inserts should
list the columns to be populated.
> Mentioning The Column Names Helps A lot In Realtime Bcoz Oracle
> Arranges The Columns In Different Way While Creating The Tables...
I'm sorry to say but that statement is completely wrong. Tables are
created with the column order specified in the statement or in the
column order of the source table:
SQL> create table mytab(
2 mycol1 number,
3 mycol2 varchar2(7),
4 mycol3 date,
5 mycol4 varchar2(12));
Table created.
SQL>
SQL> desc mytab
Name Null? Type
----------------------------------------- --------
----------------------------
MYCOL1 NUMBER
MYCOL2 VARCHAR2(7)
MYCOL3 DATE
MYCOL4 VARCHAR2(12)
SQL>
SQL> create table mytab2 as select * from mytab;
Table created.
SQL>
SQL> desc mytab2
Name Null? Type
----------------------------------------- --------
----------------------------
MYCOL1 NUMBER
MYCOL2 VARCHAR2(7)
MYCOL3 DATE
MYCOL4 VARCHAR2(12)
SQL>
Notice the columns are NOT in some random order Oracle may have chosen
'on the fly' but match EXACTLY the order in which they were listed in
the original create statement or in the exact order of the source
table.
As for insert statements:
SQL> insert into mytab
2 values (1, 'Norp',sysdate, 'Pleebo');
1 row created.
SQL>
Perfectly acceptable and successful. Following good practice
guidelines in the next insert we also find it is successful:
SQL> insert into mytab
2 (mycol1, mycol2, mycol3, mycol4)
3 values (2, 'Yerma', sysdate, 'Queezipnu');
1 row created.
SQL>
If we insert into one table from another where both tables have
matching descriptions:
SQL> insert into mytab2
2 select * From mytab;
2 rows created.
SQL>
Agaio, Oracle has no problem processing that statement. Looking at
the results:
SQL> select * From mytab;
MYCOL1 MYCOL2 MYCOL3 MYCOL4
---------- ------- --------- ------------
1 Norp 13-OCT-11 Pleebo
2 Yerma 13-OCT-11 Queezipnu
SQL>
SQL> select * from mytab2;
MYCOL1 MYCOL2 MYCOL3 MYCOL4
---------- ------- --------- ------------
1 Norp 13-OCT-11 Pleebo
2 Yerma 13-OCT-11 Queezipnu
SQL>
Let's look at a table that doesn't match the source definition:
SQL> create table mytab3(
2 mycol1 number,
3 mycol2 varchar2(7),
4 mycol3 date,
5 mycol4 varchar2(12),
6 mycol5 number);
Table created.
SQL>
A 'lazy' insert into ... select ... fails, as we should expect:
SQL> insert into mytab3 select * From mytab2;
insert into mytab3 select * From mytab2
*
ERROR at line 1:
ORA-00947: not enough values
SQL>
Here we need to list the populated columns:
SQL> insert into mytab3 (
2 mycol1,
3 mycol2,
4 mycol3,
5 mycol4)
6 select * From mytab2;
2 rows created.
SQL>
Of course if we really want to ensure a successful insert we also
should list the columns selected:
SQL> insert into mytab3 (
2 mycol1,
3 mycol2,
4 mycol3,
5 mycol4)
6 select
7 mycol1,
8 mycol2,
9 mycol3,
10 mycol4
11 From mytab2;
2 rows created.
SQL>
Both inserts succeeded.
>
> INSERT INTO SAMPLE1(column1,column2)
> ( select a.column1,b.column1 from sample a,sample b
> where a.column1<>b.column1 and a.column1<b.column1)
>
The code you object to was posted as an example to illustrate how to
generate values for the insert in the desired order; it was an example
thrown together and not intended as a tutorial on writing production
code. I suppose I should be less hasty when cobbling together quick
examples and show theh proper way to ensure that code doesn't break
should a column be added to a table some time in the future. Know
that in actual production code I do not endorse the 'quick and dirty'
methods sometimes illustrated in posted examples.
David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
