12 Replies Latest reply: Feb 3, 2014 8:03 AM by Jeff Smith Sqldev Pm-Oracle RSS

    SQL Delveoper and TOAD

    sjt0704


      Hi,

       

      I have an issue when running a query in SQL Developer in that it returns too few rows. The same query in TOAD returns far more rows. Why woould the same SQL statement return a different number of rows between two developemnt tools.

       

      Many thanks

      sjt0704

        • 1. Re: SQL Delveoper and TOAD
          KarK

          That may be display behaviour between the tools

          • 2. Re: SQL Delveoper and TOAD
            Partha Sarathy S

            Did you check the count of rows returned in both SQL DEVELOPER and TOAD. Because when you scroll down in SQL DEVELOPER you will get more rows(using F9). If you display output using F5(script output) you will get only 5000 rows because of its buffer size

            • 3. Re: SQL Delveoper and TOAD
              AnnPricks E

              Don't see the rows fetching. If you want to check how many rows returned, use count in that query and check it how many rows returned. Like below

              SELECT COUNT(*) FROM emp;

              If you use above query it should return the same count in both tool.

              • 4. Re: SQL Delveoper and TOAD
                sjt0704

                Hi,

                 

                I performed the selct count(*) query in SQL developer and it returns the same amount of rows (about 1200). The TOAD query returns over 5000.

                Thanks to everyone who has responded.

                • 5. Re: SQL Delveoper and TOAD
                  AnnPricks E

                  Then check the schema name which you have connected in that sql developer and TOAD. Because the count should be the same if you are connecting same db and user. It will not change based on the tool. So you just check whether you are connecting same db and same user

                  • 6. Re: SQL Delveoper and TOAD
                    KarK

                    Hi,

                     

                    If the same query gives different count, then you may connected to different schema or different database.

                     

                    Execute the following query and see whether you are connected to same database and schema in both the tools:

                     

                     

                     

                    show user

                    select sys_context('USERENV','DB_NAME') as Instance
                    from dual;

                     

                    • 7. Re: SQL Delveoper and TOAD
                      Dave Rabone

                      Another possibility ...

                       

                      You have inserted some rows in your session in one of the tools, but not committed the transaction.

                       

                      The other session will not see those rows.

                       

                      Or vice versa, you have deleted some rows and not committed.

                      • 8. Re: SQL Delveoper and TOAD
                        Frank Kulash

                        Hi,

                         

                        Whenever you have a problem, post a complete test script that people can run to re-create the problem.  In this case, include a query, and CREATE TABLE and INSERT statements for any tables it uses.

                        • 9. Re: SQL Delveoper and TOAD
                          sjt0704

                          Hi,

                           

                          Thanks once again for everyone's help. I should add that the SQL runs on EBS 12.1.3 as apps user.

                           

                          select
                            org.organization_code org,         
                            msi.segment1 item,
                            msi.PRIMARY_UOM_CODE uom,
                            cst.item_cost unit_cost,
                            moh.subinventory_code sub,
                            loc.segment1 loc,
                            moh.lot_number lot,
                            lot.expiration_date lot_exp_date,
                            null as serial_no,
                            ms.STATUS_CODE,
                            decode(sub.asset_inventory, 1, decode(msi.inventory_asset_flag,'Y', 'Yes', 'No') , 'No') inv_asset,
                            sum(moh.primary_transaction_quantity) qty,
                            sum(moh.primary_transaction_quantity * nvl(cst.item_cost, 0)) ext_value
                          from
                            apps.mtl_system_items_b msi,
                            apps.mtl_onhand_quantities_detail  moh,
                            apps.mtl_secondary_inventories sub,
                            apps.mtl_item_locations loc,
                            apps.cst_item_costs cst,
                            apps.mtl_parameters org,
                            apps.mtl_lot_numbers lot,
                            apps.mtl_material_statuses_vl ms
                          where
                            msi.inventory_item_id = moh.inventory_item_id and
                            msi.organization_id = moh.organization_id and
                            msi.inventory_item_id = cst.inventory_item_id(+) and
                            msi.organization_id = cst.organization_id(+) and
                            moh.subinventory_code = sub.secondary_inventory_name and
                            moh.organization_id = sub.organization_id and 
                            nvl(moh.locator_id, -99) = loc.inventory_location_id(+) and
                            msi.organization_id = org.organization_id and
                            cst.cost_type_id(+) = 1 and
                            moh.inventory_item_id = lot.inventory_item_id(+) and
                            moh.organization_id = lot.organization_id(+) and
                            moh.lot_number = lot.lot_number(+) and
                            moh.status_id = ms.status_id(+) and
                            msi.serial_number_control_code not in (2,5) and
                            org.organization_code = '&ORG' --and
                            group by
                            org.organization_code,
                            msi.segment1,
                            msi.PRIMARY_UOM_CODE,
                            msi.description,
                            cst.item_cost,
                            moh.subinventory_code,
                            loc.segment1,
                            moh.lot_number,
                            lot.expiration_date,
                            ms.STATUS_CODE,
                            decode(sub.asset_inventory, 1, decode(msi.inventory_asset_flag,'Y', 'Yes', 'No') , 'No')
                          --order by 2,1,5,6

                          • 10. Re: SQL Delveoper and TOAD
                            Dave Rabone

                            EBS 11 used contexts to filter data ... Most of the objects you query are views with the filter conditions in the where clause.

                             

                            I think EBS 12 uses FGAC to do the same thing.

                            • 11. Re: SQL Delveoper and TOAD
                              Partha Sarathy S

                              We cannot come to conclusion on just seeing the query. Check whether you are connecting to the same database using same user in both the tools. Query results should be dependent on tool.

                              • 12. Re: SQL Delveoper and TOAD
                                Jeff Smith Sqldev Pm-Oracle

                                I would try with SQL*Plus on the same machine. And check your NLS params for the session in both tools. And do an autotrace - are the plans identical? Something environmental is causing the query to be different.