Home » SQL & PL/SQL » SQL & PL/SQL » Order by Issue in oracle ? (Oracle 10 g)
Order by Issue in oracle ? [message #417531] Mon, 10 August 2009 01:38 Go to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
Hi ,

I am having problem with order by

I am having table called test_report say holding 840 records
SQL> desc test_reports;
Name         Type     Nullable Default Comments 
------------ -------- -------- ------- -------- 
GROUP_ID     NUMBER                             
HIERARCHY_ID NUMBER                             
UPD_USER     CHAR(50) Y                                               
TYPE         CHAR(1)  Y   

SQL> desc test3;
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
COL1 VARCHAR2(50) Y                         
COL2 VARCHAR2(50) Y                         
COL3 VARCHAR2(50) Y                         
COL4 VARCHAR2(50) Y  

i am trying to insert the values from test_report to test3 table declared all the columns has varchar2 and i am spooling the test3 table into test3_a.txt
i am truncating the table test3 and i am doing the same thing again like wise i took 6 to 7 spool files
i am getting mismatch while compring the files ?
why its happing like this how to overcome this situation ?

declare
cursor c1 is
select * from test_report
L_col_val c1%rowtype;
begin
open c1;
loop
fetch c1 into L_col_val; 
exit when c1 % notfound;
insert into test3 
values(to_char(L_col_val.group_id),to_char(L_col_val.hierarchy_id),L_col_val.upd_user,
L_col_val.type);
end loop;
close c1;
end;

/

SQL>spool test3_a.txt ;
SQL>select * from test3;
SQL>delete from test3;

declare
cursor c1 is
select * from test_report
L_col_val c1%rowtype;
begin
open c1;
loop
fetch c1 into L_col_val; 
exit when c1 % notfound;
insert into test3 
values(to_char(L_col_val.group_id),to_char(L_col_val.hierarchy_id),L_col_val.upd_user,
L_col_val.type);
end loop;
close c1;
end;

/

SQL>spool test3_b.txt ;
SQL>select * from test3;
SQL>delete from test3;

Thanks in advance
Rangan Sampath

[Updated on: Mon, 10 August 2009 01:43] by Moderator

Report message to a moderator

Re: Order by Issue in oracle ? [message #417533 is a reply to message #417531] Mon, 10 August 2009 01:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
My first reaction: Why do you want to do this? What does inserting the stuff in your test-table add?
Re: Order by Issue in oracle ? [message #417534 is a reply to message #417531] Mon, 10 August 2009 01:48 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
What do you want to achieve by this?
In your code, I didn't found 'ORDER BY' anywhere.
Please specify your problem clearly.

regards,
Delna
Re: Order by Issue in oracle ? [message #417535 is a reply to message #417531] Mon, 10 August 2009 01:50 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Sorry Frank sir,
I didn't get your post, when I click on POST REPLY.

regards,
Delna
Re: Order by Issue in oracle ? [message #417538 is a reply to message #417535] Mon, 10 August 2009 02:32 Go to previous messageGo to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
I want to export the table and i want to compare for some
requirement .

I know to use order by in my case i dont have any unique column .

how to go with this ?????????
Re: Order by Issue in oracle ? [message #417603 is a reply to message #417538] Mon, 10 August 2009 07:52 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
rangan.s wrote on Mon, 10 August 2009 03:32
I want to export the table and i want to compare for some
requirement .



Try again. I have no idea what you are saying or trying to do.

Quote:

I know to use order by in my case i dont have any unique column .
how to go with this ?????????


You do not have to have a unique column to use an ORDER BY Clause.
Re: Order by Issue in oracle ? [message #417748 is a reply to message #417531] Tue, 11 August 2009 06:37 Go to previous messageGo to next message
Neo06
Messages: 11
Registered: January 2008
Junior Member
Are you exporting the same table twice and comparing the spooled out text file..?

If thats the case, then you should include order by in your cursor.

cursor c1 is
select * from test_report order by 1;


Cause, its something to do with data reads from the datafile.Am not qualified to explain that in detail.
Re: Order by Issue in oracle ? [message #417756 is a reply to message #417748] Tue, 11 August 2009 07:14 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Am not qualified to explain that in detail.

I'm talking about something I don't understand that is maybe correct maybe wrong but it might be interesting to read it.

Regards
Michel
Previous Topic: ORA-06530: Reference to uninitialized composite
Next Topic: SQL Query.
Goto Forum:
  


Current Time: Fri Dec 09 04:19:18 CST 2016

Total time taken to generate the page: 0.08689 seconds