Sql query – table@string meaning

oracleplsql

I am trying to understand a query . When I run the below query , no rows are fetched.

select * from Table

But when I run the query , a result is returned having multiple rows.

select * from Table@somestring

I am not sure what @ followed by somestring means and why that is used in PL/SQL.

Best Answer

It's part of a distributed query. The string after the @ is a database link.

You can see where the database link is pointing by querying the all_db_links view, or the user_ or dba_ equivalents depending on the owner and your privileges. It might refer to a TNS alias, in which case you could get further details from the tnsnames.ora file.

When you query table you're (usually) looking at a table in your own schema. When you query table@dblink you're looking at a table in a remote database. They are different objects so you'd expect different results. Your local table is empty, but the remote one has some data.

I say 'usually' because table could be a synonym pointing at another schema, or even to a schema on a remote database itself. And dblink could be pointing back to your own database - it would be unusual but I've seen it done as a way of accessing another schema (not something I'd recommend). Without seeing if there is a synonym or where the dblink is going it's hard to be definitive, but these are less common scenarios. In this case though you're clearly hitting two different tables, probably on different databases. (Well, unless there's a VPD and connecting to the same table as a different user gives a different view... but starting to go down the rabbit hole now...)

Related Solutions

Updating Multiple Rows with Data from Column A into Column B

If you just want the discharge_dt to be overwritten with the admit_dt from the same row, it sounds like you just need

UPDATE patient
   SET discharge_dt = admit_dt
 WHERE facility_id = 'X'
   AND pat_seq IN (<<query that returns the 60,000 keys>>)

Combine multiple queries into single query

You can use UNION [ALL] to combine rows from multiple queries, as long as the number of columns are the same and the columns are compatible, if not of the exact same types.

SELECT 'Driver' as SourceTable, Driver_No, Name, Available 
  FROM Driver
 WHERE Available = 'Y'
 UNION ALL
SELECT 'Freight', Freight_No, Freight_Wagon_Type_Name, Available
  FROM Freight
 WHERE Available = 'Y'
 UNION ALL
SELECT 'Locomotive', Loco_No, Loco_Name, Available
  FROM Locomotive
 WHERE Available = 'Y';

(added SourceTable column per a_horse_with_no_name's suggestion)

Note that the column names are taken from the first query in the UNION, which means you end up with (SourceTable, Driver_No, Name, Available). You can always alias them as required.

Best Answer

Related Solutions

Updating Multiple Rows with Data from Column A into Column B

Combine multiple queries into single query

Related Question