That may be display behaviour between the tools
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
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.
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.
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
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:
select sys_context('USERENV','DB_NAME') as Instance
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.
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.
Thanks once again for everyone's help. I should add that the SQL runs on EBS 12.1.3 as apps user.
null as serial_no,
decode(sub.asset_inventory, 1, decode(msi.inventory_asset_flag,'Y', 'Yes', 'No') , 'No') inv_asset,
sum(moh.primary_transaction_quantity * nvl(cst.item_cost, 0)) ext_value
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
decode(sub.asset_inventory, 1, decode(msi.inventory_asset_flag,'Y', 'Yes', 'No') , 'No')
--order by 2,1,5,6
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.
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.
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.