[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Facing Oracle Error ORA-00600

Posted on 2006-06-03
7
Medium Priority
?
1,083 Views
Last Modified: 2008-01-09
I am facing the following Oracle error when executing the following command

Can you please help me with the probable solution

create table ak_b
  storage(initial 25M next 1M pctincrease 0)
  tablespace info_data1
as
Select cb.Account_No account_no,
            max(bi.Statement_Date)             Last_Bill_Dt,
            max(bi.To_Date)                  Last_Trafic_Dt,
            sum(cb.New_Charges)/100       Total_Inv_Amt,
                Sum(cb.Balance_Due)/100       Total_Bal,
                Sum(Decode(Sign(nvl(bi.Payment_Due_Date,trunc(SysDate)-1)-trunc(SysDate)),-1,cb.Balance_Due,0))/100 Over_Due,
                Sum(cb.Total_Paid)/100            Total_Paym
           From cmf_balance@north_arbor cb, Bill_Invoice@north_arbor bi
          Where cb.Account_No = bi.Account_No(+)
            And cb.Bill_Ref_No = bi. Bill_Ref_No(+)
          And cb.Bill_Ref_Resets = bi. Bill_Ref_Resets(+)
            Group by cb.Account_No;

Error returned -----> ORA-00600: internal error code, arguments: [qerrmOFBu1], [24347], [], [], [], [], [], []

Just for your further information, I am able to successfully execute the select query and I am getting the proper output as expected... I am only facing the problem in Creating the Oracle table ... pl help



0
Comment
Question by:mukesh_ch
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16822564
what versions do your databases have (the one you run this statement and the one behind the dblink)?

0
 
LVL 6

Accepted Solution

by:
DLyall earned 672 total points
ID: 16822707
I think what you are actually getting reported is ORA-24347 (null column in aggregate function).
I'm guessing the two databases are at different versions.

On Metalink I found a note about this as follows, "When inserting into a 9i table selecting from a remote 8i view a false "ORA-24347: Warning of a NULL column in an aggregate function" may be signalled." Bug 2876379  False ORA-24347 from INSERT over DB LINK between releases.
It was confirmed as a bug in version 9.2.0.3 and was fixed in version 9.2.0.4.

HTH
0
 
LVL 4

Assisted Solution

by:abd00n
abd00n earned 664 total points
ID: 16823266
Hi,
I think the internal join like the one in your query:
   cb.Account_No = bi.Account_No(+)
  .. may result with a NULL value for almost all the records, creating a NULL field (which needs a definition for the data type.)
  By removeing the (+) you may not get the error, try this for analysis and rollback.

Regards;;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Assisted Solution

by:MohanKNair
MohanKNair earned 664 total points
ID: 16827994
Since you are able to run the query, the problem might be during the table creation. First create the table and then insert data into the table.
If possible increase initial extent depending on the amount of data the query extracts.

create table ak_b
  storage(initial 25M next 1M pctincrease 0)
  tablespace info_data1
as
select * from(
Select cb.Account_No account_no,
          max(bi.Statement_Date)           Last_Bill_Dt,
          max(bi.To_Date)               Last_Trafic_Dt,
          sum(cb.New_Charges)/100      Total_Inv_Amt,
                Sum(cb.Balance_Due)/100      Total_Bal,
                Sum(Decode(Sign(nvl(bi.Payment_Due_Date,trunc(SysDate)-1)-trunc(SysDate)),-1,cb.Balance_Due,0))/100 Over_Due,
                Sum(cb.Total_Paid)/100          Total_Paym
           From cmf_balance@north_arbor cb, Bill_Invoice@north_arbor bi
          Where 1=2 and cb.Account_No = bi.Account_No(+)
            And cb.Bill_Ref_No = bi. Bill_Ref_No(+)
         And cb.Bill_Ref_Resets = bi. Bill_Ref_Resets(+)
            Group by cb.Account_No
) where 1=2;


insert into ak_b
Select cb.Account_No account_no,
          max(bi.Statement_Date)           Last_Bill_Dt,
          max(bi.To_Date)               Last_Trafic_Dt,
          sum(cb.New_Charges)/100      Total_Inv_Amt,
                Sum(cb.Balance_Due)/100      Total_Bal,
                Sum(Decode(Sign(nvl(bi.Payment_Due_Date,trunc(SysDate)-1)-trunc(SysDate)),-1,cb.Balance_Due,0))/100 Over_Due,
                Sum(cb.Total_Paid)/100          Total_Paym
           From cmf_balance@north_arbor cb, Bill_Invoice@north_arbor bi
          Where cb.Account_No = bi.Account_No(+)
            And cb.Bill_Ref_No = bi. Bill_Ref_No(+)
         And cb.Bill_Ref_Resets = bi. Bill_Ref_Resets(+)
            Group by cb.Account_No;
0
 
LVL 6

Expert Comment

by:DLyall
ID: 16840381
Hi :mukesh

Have you any more queries on this?

If not could you please assign points to the appropriate response to close this question.


Many thanks

DLyall
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20286445
Forced accept.

Computer101
EE Admin
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses
Course of the Month17 days, 18 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question