CODEWITHSUNDEEP
sqloracle-database
jeleb
jeleb

Reputation: 643

Exclamation mark in SQL (Oracle)

Looking at V$SQL in my database, I have just found a strange query that looks like :

UPDATE "MYTABLE" "A1" SET "SOMECOLUMN" = (
    SELECT "A2"."ANOTHERCOLUMN" 
    FROM "ANOTHERTABLE"@! "A2" 
    WHERE "A2".ROWID=:B1
)

Does anyone know the meaning of the syntax @!

I have never seen something like it before in Oracle

Thanks

Upvotes: 11

Views: 4843

Answers (2)

James
James

Reputation: 3411

It's a query that has originated on a remote database. The database where you've seen this query in V$SQL has been referenced in the query on the remote database using the @DB_NAME syntax

The remote database has pushed the query to your database for execution, but to answer the query, your database needs to pull some information back from the remote database. This is where the @! comes in, basically it's a reference back to the database where the query originated from

For example, create a test database link, even to the same database, and run this:

alter system flush shared_pool;
select sysdate from dual@myself;
select sql_text from gv$sql where sql_fulltext like '%@!%';

SQL_TEXT
--------
SELECT SYSDATE@! FROM "DUAL" "A1"

Upvotes: 3

glunder
glunder

Reputation: 1

Often @ in databases are related to a database link. E.g. in Oracle I use create public database link mylink connect to remote_username identified by mypassword using 'tns_service_name';

if the after this user (remote_username) has a table ANOTHERTABLE you could use: SELECT "A2"."ANOTHERCOLUMN" FROM "ANOTHERTABLE"@mylink "A2" WHERE "A2".ROWID=1

How the ! sign is used here is unclear for me. Normally the ! sign is something you use to access the shell where your database client is running.

I don't know is this helped you. Good luck.

Upvotes: -1

Related Questions