What does @ ! mean in a From Statement

Question

I am trying to determine what this code is doing (Oracle SQL) — especially the at-sign exclamation mark in the from clause.

INSERT INTO "LOCATIONS" "A1"
            ("LOCATION_ID",
             "SEQUENCE",
             "POINT_TYPE")
SELECT "A2"."LOCATION_ID",
       "A2"."SEQUENCE",
       "A2"."LOCATION_TYPE",
       "A2"."POINT_TYPE"
FROM   "LOCATIONS"@! "A2"
WHERE  NOT EXISTS (SELECT 1
                   FROM   "LOCATIONS" "A3"
                   WHERE  "A3"."LOCATION_ID" = "A2"."LOCATION_ID")

FYI, the @ symbol is actually called "at" or "hat." Ampersand is this: &. — Matt, Jul 26 '12 at 14:20
You can't insert 4 columns into 3 columns. The "@" usually means a database link. But I've never seen it together with an "!" — winkbrace, Jul 26 '12 at 14:27
`!` ought to be a database link name, but that isn't a legal character to have in one. Perhaps this is supposed to be run from a shell script that does a real-time substitution of `!` for a real link name, though it would be an odd choice; and as @BazzPsychoNut points out it will fail on the column mismatch anyway. The only use of `!` I'm aware of in Oracle is as the [`soundex` operator](http://docs.oracle.com/cd/E11882_01/text.112/e24436/cqoper.htm#sthref1108), which clearly isn't the case here. — Alex Poole, Jul 26 '12 at 14:32
There are actually 81 columns inserted into 81 columns in the total sql statement. I just removed a number of the columns for simplicity's sake — user739866, Jul 26 '12 at 17:31
Interesting; looks like OEM/GC might be hiding the link name, maybe as a security measure? I can't see that documented anywhere, and don't have a GC environment to check on, but DC doesn't seem to. SO that's a guess, really... — Alex Poole, Jul 26 '12 at 18:38
By the way, what this code is doing is copying the new records from locations @ some database to locations @ your database. I assume it's address or connection data, otherwise I don't see why you'd have a script to load new locations. — winkbrace, Jul 26 '12 at 20:52

score 5 · Answer 1 · edited Aug 31 '14 at 16:21

This is a reverse database link to the original database, where the query is executed. The original query must look like:

INSERT INTO LOCATIONS@remote_db
            ("LOCATION_ID",
             "SEQUENCE",
             "POINT_TYPE")
SELECT "A2"."LOCATION_ID",
       "A2"."SEQUENCE",
       "A2"."POINT_TYPE"
FROM   "LOCATIONS" A2
WHERE  NOT EXISTS (SELECT 1
                   FROM   LOCATIONS@remote_db A3
                   WHERE  "A3"."LOCATION_ID" = "A2"."LOCATION_ID");

That way all remote tables become local, and local tables become remote with "@!".

score -1 · Answer 2 · answered Jul 26 '12 at 14:35

-1

I don't think this is valid SQL. Check your code to make sure nothing is changed before the SQL is executed - particularly check to see if the name of a database link is being substitued for the ! .

If you can't determine what is being executed, you could put a trace on the database

answered Jul 26 '12 at 14:35

Colin Pickard

45,724
13
98
148

This code is pulled from Monitored SQL Executions in Grid Control (with a number of the lines removed - that's why it shows 4 select with only 3 insert columns). – user739866 Jul 26 '12 at 17:30

What does @ ! mean in a From Statement

2 Answers2

Linked