This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Oct 8, 2013 5:05 AM by EdStevens RSS

SQLDeveloper, db links, and ora-12514

EdStevens Guru
Currently Being Moderated

There are several ducks to line up here, so please bear with me.

 

First, the lineup, the batting order …

 

Workstation WKS_DBA; Win 7 Pro 64bit; Oracle 10g 32bit client; Oracle 11g 64bit client (in that order in PATH); SQL Developer version 3.2.20.09, build MAIN-09.87

 

Workstation WKS_OLD; Win 7 Pro 32bit; Oracle 10g 32bit client; SQL Developer version 3.0.04, build MAIN-04.34

 

Workstation WKS-NEW; Win 7 Pro 64bit; 11g 32bit client; 11g 64bit client (in that order in PATH).

 

Database server SVRLCL; Oracle Linux 5.6 x86-64; Oracle database 10.2.0.4 64bit (let’s call it DBLCL); database link LK_DBRMT pointing to ..

 

Database server SVRRMT; Windows server 2003 R2, SP2, 32bit; Oracle database 10.2.0.4 (let’s call it DBRMT).

 

The situation: Connected to DBLCL, we have an UPDATE statement with a WHERE clause that includes a SELECT on a table @LK_DBRMT.

 

If the statement is executed by SQLDeveloper on WKS_DBA, it works.

If the statement is executed by SQLDeveloper on WKS_OLD, it works.

If the statement is executed by SQLDeveloper on WKS_NEW, it returns an ora-12514, pointing to the db link.

 

What’s equally strange is that from WKS_OLD and WKS_NEW, SQLDev is sending some CREATE DATABASE LINK statements prior to sending the UPDATE statement. When coming from WKS_NEW, that supposed db link creation includes SERVICE_NAME=null. Here is a sequence of packets received by database DBLCL from SQLDev, beginning with the last query that SQLDev issued on its own behalf, the select from dba.xml_schemas.  The only statement we asked sqldev to execute was the UPDATE.

 

(obviously, the trace has been edited in several ways for brevity and readability. Actualy packet contents include only key clear text to identify what was going on.)

 

15:02:26:948]     nttrd:      socket 24 had bytes read=112

15:02:26:948]     nsprecv:    |..select.1.from.dba_xml_schemas.|

15:02:26:949]     nttwr:      socket 24 had bytes written=159

15:02:26:949]     nspsend:    |........|

15:02:31:124]     nttrd:      socket 24 had bytes read=277

15:02:31:124]     nsprecv:      |....CREATE.DATABASE.LINK.LK_DBRMT..CONNECT.TO.kilroy.IDENTIFIED.BY.mypswd..USING.'(DESCRIPTION.=(ADDRESS_LIST.=(ADDRESS.=.(PROTOCOL.=.TCP)(HOST.=.172.nn.nnn.88)(PORT.=.1521)))(CONNECT_DATA.=.(SERVICE_NAME.=.null))).'..|

15:02:31:128]     nttwr:      socket 24 had bytes written=60

15:02:31:128]     nspsend:    |.<............................D.............................    |

15:02:31:129]     nttrd:      socket 24 had bytes read=269

15:02:31:129]     nsprecv:      |....CREATE.DATABASE.LINK.LK_DBRMT..CONNECT.TO.kilroy.IDENTIFIED.BY.mypswd..USING.'(DESCRIPTION.=(ADDRESS_LIST.=(ADDRESS.=.(PROTOCOL.=.TCP)(HOST.=.172.nn.nnn.88)(PORT.=.1521)))(CONNECT_DATA.=.(SERVICE_NAME.=.null))).'..|

15:02:31:129]     nttwr:      socket 24 had bytes written=11

15:02:31:129]     nspsend:    |...........     |

15:02:31:130]     nttwr:      socket 24 had bytes written=11

15:02:31:130]     nspsend:    |...........     |

15:02:31:130]     nttrd:      socket 24 had bytes read=11

15:02:31:130]     nsprecv:    |...........     |

15:02:31:130]     nttwr:      socket 24 had bytes written=84

15:02:31:130]     nspsend:    |...(ORA-02011:.duplicate.database.link.name.    |

15:02:31:131]     nttrd:      socket 24 had bytes read=269

15:02:31:131]     nsprecv:      |....CREATE.DATABASE.LINK.LK_DBRMT..CONNECT.TO.kilroy.IDENTIFIED.BY.mypswd..USING.'(DESCRIPTION.=(ADDRESS_LIST.=(ADDRESS.=.(PROTOCOL.=.TCP)(HOST.=.172.nn.nnn.88)(PORT.=.1521)))(CONNECT_DATA.=.(SERVICE_NAME.=.null))).'..|

15:02:31:132]     nttwr:      socket 24 had bytes written=11

15:02:31:132]     nspsend:    |...........     |

15:02:31:132]     nttwr:      socket 24 had bytes written=11

15:02:31:132]     nspsend:    |...........     |

15:02:31:132]     nttrd:      socket 24 had bytes read=11

15:02:31:132]     nsprecv:    |...........     |

15:02:31:132]     nttwr:      socket 24 had bytes written=84

15:02:31:132]     nspsend:    |...(ORA-02011:.duplicate.database.link.name.    |

15:02:31:134]     nttrd:      socket 24 had bytes read=848

15:02:31:134]     nsprecv:    |.....update.TRANS_DETAIL.RT.|

15:02:31:138]     nttwr:      socket 11 had bytes written=227

15:02:31:138]     nspsend:      |..(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.nn.nnn.88)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=null)(CID=(PROGRAM=oracle)(HOST=svrlcl.myorg.org)(USER=KIlroy))))     |

15:02:31:138]     nttrd:      socket 11 had bytes read=103

15:02:31:138]     nsprecv:      "|""..[(DESCRIPTION=(TMP=)(VSNNUM=169870336)(ERR=12514)(ERROR_STACK=(ERROR=(CODE=12514)(EMFI=4)))) |

15:02:31:139]     nttwr:      socket 24 had bytes written=11

15:02:31:139]     nspsend:    |...........     |

15:02:31:139]     nttwr:      socket 24 had bytes written=11

15:02:31:140]     nspsend:    |...........     |

15:02:31:140]     nttrd:      socket 24 had bytes read=11

15:02:31:140]     nsprecv:    |...........     |

15:02:31:140]     nttwr:      socket 24 had bytes written=136

15:02:31:140]     nspsend:    |....[ORA-12514:.TNS:listener.does.not.currently.know.of.service.requested.in.connect.descriptor.|

Here is the same sequence, using WKS_OLD

 

09:25:34:694]     nttrd:      socket 24 had bytes read=112

09:25:34:694]     nsprecv:    |..select.1.from.dba_xml_schemas.|

09:25:34:694]     nttwr:      socket 24 had bytes written=159

09:25:34:694]     nspsend:    |........|

09:25:39:883]     nttrd:      socket 24 had bytes read=279

09:25:39:883]     nsprecv:      |....CREATE.DATABASE.LINK.LK_DBRMT..CONNECT.TO.kilroy.IDENTIFIED.BY.mypswd..USING.'(DESCRIPTION.=(ADDRESS_LIST.=(ADDRESS.=.(PROTOCOL.=.TCP)(HOST.=.172.nn.nnn.88)(PORT.=.1521)))(CONNECT_DATA.=.(SERVICE_NAME.=.DBRMT))).'|

09:25:39:979]     nttwr:      socket 24 had bytes written=60

09:25:39:979]     nspsend:    |.<......|

09:25:39:980]     nttrd:      socket 24 had bytes read=271

09:25:39:980]     nsprecv:      |....CREATE.DATABASE.LINK.LK_DBRMT..CONNECT.TO.kilroy.IDENTIFIED.BY.mypswd..USING.'(DESCRIPTION.=(ADDRESS_LIST.=(ADDRESS.=.(PROTOCOL.=.TCP)(HOST.=.172.nn.nnn.88)(PORT.=.1521)))(CONNECT_DATA.=.(SERVICE_NAME.=.DBRMT))).'|

09:25:39:981]     nttwr:      socket 24 had bytes written=11

09:25:39:981]     nspsend:    |...........     |

09:25:39:981]     nttwr:      socket 24 had bytes written=11

09:25:39:981]     nspsend:    |...........     |

09:25:39:981]     nttrd:      socket 24 had bytes read=11

09:25:39:981]     nsprecv:    |...........     |

09:25:39:982]     nttwr:      socket 24 had bytes written=84

09:25:39:982]     nspsend:    |...(ORA-02011:.duplicate.database.link.name.    |

09:25:39:983]     nttrd:      socket 24 had bytes read=271

09:25:39:983]     nsprecv:      |....CREATE.DATABASE.LINK.LK_DBRMT..CONNECT.TO.kilroy.IDENTIFIED.BY.mypswd..USING.'(DESCRIPTION.=(ADDRESS_LIST.=(ADDRESS.=.(PROTOCOL.=.TCP)(HOST.=.172.nn.nnn.88)(PORT.=.1521)))(CONNECT_DATA.=.(SERVICE_NAME.=.DBRMT))).'|

09:25:39:984]     nttwr:      socket 24 had bytes written=11

09:25:39:984]     nspsend:    |........... |

09:25:39:985]     nttwr:      socket 24 had bytes written=11

09:25:39:985]     nspsend:    |...........     |

09:25:39:985]     nttrd:      socket 24 had bytes read=11

09:25:39:985]     nsprecv:    |...........     |

09:25:39:985]     nttwr:      socket 24 had bytes written=84

09:25:39:986]     nspsend:    |...(ORA-02011:.duplicate.database.link.name.    |

09:25:39:988]     nttrd:      socket 24 had bytes read=864

09:25:39:988]     nsprecv:    |.....update.ROY_TRANS_DETAIL.RT.|

09:25:40:015]     nttwr:      socket 12 had bytes written=229

09:25:40:015]     nspsend:      |..(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.nn.nnn.88)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBRMT)(CID=(PROGRAM=oracle)(HOST=svrlcl.myorg.org)(USER=KIlroy))))   |

09:25:40:020]     nttrd:      socket 12 had bytes read=8

09:25:40:020]     nsprecv:    |........|

09:25:40:020]     nttwr:      socket 12 had bytes written=229

09:25:40:020]     nspsend:      |..(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.nn.nnn.88)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBRMT)(CID=(PROGRAM=oracle)(HOST=svrlcl.myorg.org)(USER=KIlroy))))   |

09:25:40:021]     nttrd:      socket 12 had bytes read=32

09:25:40:021]     nsprecv:    |........|

09:25:40:021]     nttwr:      socket 12 had bytes written=38

09:25:40:021]     nspsend:    |..x86_64/Linux.2.4.xx.  |

09:25:40:021]     nttrd:      socket 12 had bytes read=179

09:25:40:021]     nsprecv:    |.....IBMPC/WIN_NT-8.1.0.|

 

And the same sequence when executing from WKS_DBA

 

[10-JUL-2013      14:34:24:546]     nttrd:      socket 24 had bytes read=111

[10-JUL-2013      14:34:24:546]     nsprecv:    |.select.1.from.dba_xml_schemas.w|

[10-JUL-2013      14:34:24:547]     nttwr:      socket 24 had bytes written=159

[10-JUL-2013      14:34:24:547]     nspsend:    |........|

[10-JUL-2013      14:34:45:262]     nttrd:      socket 24 had bytes read=856

[10-JUL-2013      14:34:45:262]     nsprecv:    |.....update.ROY_TRANS_DETAIL.RT.|

[10-JUL-2013      14:34:45:267]     nttwr:      socket 11 had bytes written=226

[10-JUL-2013      14:34:45:267]     nspsend:      |..(DESCRIPTION=(address=(protocol=tcp)(host=svrrmt)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBRMT)(CID=(PROGRAM=oracle)(HOST=svrlcl.myorg.org)(USER=estevens))))      |

[10-JUL-2013      14:34:45:272]     nttrd:      socket 11 had bytes read=8

[10-JUL-2013      14:34:45:272]     nsprecv:   

[10-JUL-2013      14:34:45:272]     nsprecv:    |........|

[10-JUL-2013      14:34:45:272]     nttwr:      socket 11 had bytes written=226

[10-JUL-2013      14:34:45:272]     nspsend:      |..(DESCRIPTION=(address=(protocol=tcp)(host=svrrmt)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBRMT)(CID=(PROGRAM=oracle)(HOST=svrlcl.myorg.org)(USER=estevens))))      |

[10-JUL-2013      14:34:45:273]     nttrd:      socket 11 had bytes read=32

[10-JUL-2013      14:34:45:273]     nsprecv:    |........|

[10-JUL-2013      14:34:45:273]     nttwr:      socket 11 had bytes written=38

[10-JUL-2013      14:34:45:273]     nspsend:    |..x86_64/Linux.2.4.xx.  |

[10-JUL-2013      14:34:45:273]     nttrd:      socket 11 had bytes read=179

[10-JUL-2013      14:34:45:273]     nsprecv:    |.....IBMPC/WIN_NT-8.1.0.|

Several questions suggest themselves, not the least of which are

1- Why is SQLDev even issuing those CREATE DATABASE LINK statements in the first place?

2- Where is it getting the information with which to do that?

3- Why is the same version of SQLDev behaving differently on WKS_DBA and WKS_NEW ?

  • 1. Re: SQLDeveloper, db links, and ora-12514
    Gary Graham Expert
    Currently Being Moderated

    Hi Ed,

    EdStevens wrote:

    Several questions suggest themselves, not the least of which are

    1- Why is SQLDev even issuing those CREATE DATABASE LINK statements in the first place?

    2- Where is it getting the information with which to do that?

    3- Why is the same version of SQLDev behaving differently on WKS_DBA and WKS_NEW ?

    1. Not sure.  Perhaps as a convenience?  But it does seem a bit over the top.  Maybe Vadim can comment?

    2. Probably from existing connection definitions (i.e., the connections.xml).

    3. Is it possible that the connections.xml files on the different workstations differ?

     

    Specifically, do WKS_NEW and WKS_OLD know of a connection name that is the same as the DBLink name, and its connection details use Service Name on WKS_NEW rather than SID (so the SID is null), but use SID on WKS_OLD?  And WKS_DBA has no knowledge of that connection name?  This is the only scenario I can come up with that makes any sense, unless I'm just looking at the wrong code.

     

    Hope this helps,

    Gary

    SQL Developer Team

  • 2. Re: SQLDeveloper, db links, and ora-12514
    EdStevens Guru
    Currently Being Moderated

    GaryGraham wrote:

     

    Hi Ed,

    EdStevens wrote:

    Several questions suggest themselves, not the least of which are

    1- Why is SQLDev even issuing those CREATE DATABASE LINK statements in the first place?

    2- Where is it getting the information with which to do that?

    3- Why is the same version of SQLDev behaving differently on WKS_DBA and WKS_NEW ?

    1. Not sure.  Perhaps as a convenience?  But it does seem a bit over the top.  Maybe Vadim can comment?

    2. Probably from existing connection definitions (i.e., the connections.xml).

    3. Is it possible that the connections.xml files on the different workstations differ?

     

    Specifically, do WKS_NEW and WKS_OLD know of a connection name that is the same as the DBLink name, and its connection details use Service Name on WKS_NEW rather than SID (so the SID is null), but use SID on WKS_OLD?  And WKS_DBA has no knowledge of that connection name?  This is the only scenario I can come up with that makes any sense, unless I'm just looking at the wrong code.

     

    Hope this helps,

    Gary

    SQL Developer Team

     

    I had the developer send me the connections.xml from both workstations.  Unfortunately, she didn't distinguish which from which, and she is out now so it will be tomorrow before I can follow up.  I'm a bit suspicious of what I'm looking at, because she reported find both in "C:\Users\<username> \AppData\Roaming\SQL Developer\system3.2.20.09.87\o.jdeveloper.db.connection.11.1.1.4.37.59.48\.  I've never dug into this before, but that directory 'system3.2.20.09.87' clearly maps to the version of SQLDev, and her old machine (wks_OLD) is running an older version that 3.2.10.  That's why I want to follow up with her tomorrow.

     

    But for what it's worth, after a line-by-line examination comparing both of her files to mine (WKS_DBA) the only difference I find is that she saved her password and I did not. 


    But .... yes, there is a connection name (<Reference name=) is the same as the name of the db_link.  But they do not reference any connection details.  OracleConnectionType is "TNS".



  • 3. Re: SQLDeveloper, db links, and ora-12514
    EdStevens Guru
    Currently Being Moderated

    As a rule I don't approve of simply 'bumping' a thread, but I'm smack out of ideas on even how to pursue this, and given the flood of posts relating to the EA release, thought it might be worthwhile to maybe catch another set of eyes, or a second look by those who have already seen this.

     

    To summarize ..

    Given an UPDATE that includes a reference to a db_link in the WHERE clause ..

     

    Given one workstation (WKS_DBA), Windows 7 Pro 64 bit, Oracle 11.2 admin client (64 bit),, Oracle 10.2 admin client (32 bit); SQL Dev 3.2.20.09

     

    Given one workstation (WKS_NEW), Windows 7 Pro 64 bit, Oracle 11.2 runtime client (64 bit), Oracle 11.2 runtime client (32 bit); SQL Dev 3.2.20.09

     

    On WKS_DBA, everything works without incident

     

    On WKS_NEW, the UPDATE returns an ora-12514 on the reference to the db_link.  Of course the db_link is at the database so at first blush the client should have not bearing.

     

    Capturing a sqlnet trace at the local database server when connecting from WKS_DBA shows nothing unusual.  SQLDev sends a bunch of packets containing the SELECT's from the data dictionary that he uses to populate the navigation tree, and the first packet he sends after his own setup is the UPDATE statement.

     

    Capturing a sqlnet trace at the local database server when connecting from WKS_NEW shows a very different story. The packet traffic during the internal setup - querying the data dictionary - are the same.  But the first packets relating to the UPDATE are not the UPDATE itself, but three attempts to CREATE DATABASE LINK, defining the same link as the one already defined in the db, only with a SERVICE_NAME of NULL.  Eventually the UPDATE is sent (sent by SQL Dev, received by the server capturing the trace), then the connection info sent to the remote db server, using the same SERVICE_NAME=NULL.

     

    Of course, everything about the db_link is defined on the local db server, so should have no bearing on client behavior.

    A comparison of SQLDev connections.xml between the two workstations revealed nothing.

  • 4. Re: SQLDeveloper, db links, and ora-12514
    rp0428 Guru
    Currently Being Moderated

    >

    On WKS_NEW, the UPDATE returns an ora-12514 on the reference to the db_link.  Of course the db_link is at the database so at first blush the client should have not bearing.

    >

    I don't know why sql developer should be sending CREATE DATABASE LINK ddl at all so the dev team should look into this. Gary's attempt to get a response from Vadim didnt' work.

     

    The only 'common' thing I see is that on WKS_NEW you have the 32 bit Oracle client ahead of the 64 bit client; the others don't have that.

     

    Which begs the question: what type of connection are you using: OCI or thin? If OCI try using the THIN driver to see if you get the same issue and log entries.

     

    As to other things to try I would suggest more tests to try to isolate the offending part a little more.

     

    Test various aspects of the DB_LINK itself to see which use cases work and which do not. Many of these can just be done from WKS_NEW

     

    1. Try a plain SELECT * from myTable@dblink - tests if the DB link itself even works in the FROM clause with NO local table reference.

     

    2. Try a similar SELECT * and add a second remote table in the WHERE clause - that is ALL tables and conditions are from the remote server.

     

    3. Try a SELECT from a local table that joins the remote table and db link in the FROM clause rather than the WHERE clause

     

    4. Try removing the 32 bit Oracle client form the WKS_NEW machine to test if it was getting in the way.

  • 5. Re: SQLDeveloper, db links, and ora-12514
    EdStevens Guru
    Currently Being Moderated

    rp0428 wrote:

     

    >

    On WKS_NEW, the UPDATE returns an ora-12514 on the reference to the db_link.  Of course the db_link is at the database so at first blush the client should have not bearing.

    >

    I don't know why sql developer should be sending CREATE DATABASE LINK ddl at all so the dev team should look into this. Gary's attempt to get a response from Vadim didnt' work.

     

    The only 'common' thing I see is that on WKS_NEW you have the 32 bit Oracle client ahead of the 64 bit client; the others don't have that.

     

    Which begs the question: what type of connection are you using: OCI or thin? If OCI try using the THIN driver to see if you get the same issue and log entries.

     

    As to other things to try I would suggest more tests to try to isolate the offending part a little more.

     

    Test various aspects of the DB_LINK itself to see which use cases work and which do not. Many of these can just be done from WKS_NEW

     

    1. Try a plain SELECT * from myTable@dblink - tests if the DB link itself even works in the FROM clause with NO local table reference.

     

    2. Try a similar SELECT * and add a second remote table in the WHERE clause - that is ALL tables and conditions are from the remote server.

     

    3. Try a SELECT from a local table that joins the remote table and db link in the FROM clause rather than the WHERE clause

     

    4. Try removing the 32 bit Oracle client form the WKS_NEW machine to test if it was getting in the way.

     

    Dredging this back up again.

    I tried deleting all oracle clients and re-installing.  (removed by deleting all oracle directories, deleting 32-bit and 64-bit oracle entries from registry, removing oracle references from PATH)

    Did not uninstall SQL Dev

    Installed just the 32-bit client.  Base client worked, but starting SQLDev returned error that it could not load 32-bit libraries.

    removed the 32-bit client and installed the 64-bit client.

    Original problem exhibits itself.

     

    To recap:

    The problem only occurs on this one workstation.

    The problem only occurs when the update statement is issued from within SQLDev.  No issue with same update statement in sqlplus.

    The problem only occurs when issuing the full update statement.  No version of a SELECT outside of the context of the UPDATE creates the problem.

  • 6. Re: SQLDeveloper, db links, and ora-12514
    EdStevens Guru
    Currently Being Moderated

    Since this issue is apparently tied to SQL Developer (or at least SQL Developer in a given environment), I can't really open an SR on it.  Does anyone have any ideas on how I could get this in the hands of someone on the SQL Dev team and work directly with them to resolve it?  I would think it is odd enough that someone there would like to sink their teeth into it.

  • 7. Re: SQLDeveloper, db links, and ora-12514
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated

    Why can't you open an SR?

     

    We're neck deep in fixing bugs right now, I'll wrangle a developer or two together.

  • 8. Re: SQLDeveloper, db links, and ora-12514
    rp0428 Guru
    Currently Being Moderated
    Dredging this back up again.

    Not a problem. But you didn't comment at all on the question I ask about the driver type (OCI vs. thin) or whether you tried any of the other tests I suggested.

     

    Those tests might help narrow down whether it makes a difference WHERE in the query the DB link is referenced or where the tables being referenced are located.

  • 9. Re: SQLDeveloper, db links, and ora-12514
    EdStevens Guru
    Currently Being Moderated

    JeffSmithSQLDevPM wrote:

     

    Why can't you open an SR?

     

    We're neck deep in fixing bugs right now, I'll wrangle a developer or two together.

    I really don't want to do an end-run around the SR system, but in this case ... when I have to supply the product from a pick-list ... hmm, SQL Developer is not listed as an option to choose.  If you think I'm being too literal in my interpretation, and I should just select 'database' (or something else) I'd be happy to. 

  • 10. Re: SQLDeveloper, db links, and ora-12514
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated

    It's listed under 'All Products' - next to the product dropdown, click on the 'Search all software products' link and type 'SQL Developer' for your search term.

  • 11. Re: SQLDeveloper, db links, and ora-12514
    EdStevens Guru
    Currently Being Moderated

    rp0428 wrote:

     

    >

    On WKS_NEW, the UPDATE returns an ora-12514 on the reference to the db_link.  Of course the db_link is at the database so at first blush the client should have not bearing.

    >

    I don't know why sql developer should be sending CREATE DATABASE LINK ddl at all so the dev team should look into this. Gary's attempt to get a response from Vadim didnt' work.

     

    The only 'common' thing I see is that on WKS_NEW you have the 32 bit Oracle client ahead of the 64 bit client; the others don't have that.

    Ok, on Friday, I had un-restricted access to the problem workstation (developer was out of office).  I completely removed all oracle clients, then started re-installing and testing. I removed and reinstalled only the Oracle clients, not SQL Dev.  All removals were 'ripping it out by the roots' - deleting the installation directories (c:\app\oracle  etc), the inventory directories (c:\Program Files\oracle and c:\Program Files (x86)\oracle) and the registry entires (hklm\software\oracle and hklm\software\Wow6432Node\oracle).

     

    First I installed just the 32-bit. SQL Dev choked and said 'cannot load 32-bit libraries'

     

    Removed the 32 and installed the 64 bit.  Ran the tests and got the same results as before .. SELECT works, SELECT in the UPDATE fails

    Removed 64, installed 32 then 64. Ran the tests and got the same results as before .. SELECT works, SELECT in the UPDATE fails

    Reversed the PATH entries, putting 32-bit first. Ran the tests and got the same results as before .. SELECT works, SELECT in the UPDATE fails

     

     

     

    Which begs the question: what type of connection are you using: OCI or thin? If OCI try using the THIN driver to see if you get the same issue and log entries.

     

    If that selection is made by setting the connection properties as "basic" (thin?) or "tns" (OCI?) then yes, tried it both ways, no change.

     

    As to other things to try I would suggest more tests to try to isolate the offending part a little more.

     

    Test various aspects of the DB_LINK itself to see which use cases work and which do not. Many of these can just be done from WKS_NEW

     

    1. Try a plain SELECT * from myTable@dblink - tests if the DB link itself even works in the FROM clause with NO local table reference.

    2. Try a similar SELECT * and add a second remote table in the WHERE clause - that is ALL tables and conditions are from the remote server.

     

    3. Try a SELECT from a local table that joins the remote table and db link in the FROM clause rather than the WHERE clause

     

    I think the original pretty well covers the bases.  Here's the actual full update statement (with certain names made generic for public posting)

    update LOCAL_TRANS_DETAIL RT
       set rt.sent_to_remoteapp_date = sysdate
    where rt.transactionid in 
    (select rt.transactionid
       FROM LOCAL_TRANS_DETAIL RT
       JOIN LOCALSCHEMA.RMT_MASTER_ITEMS RMI ON RT.NEW_ITEM_NUM = RMI.NEW_ITEM_NUM
       JOIN RMTSCHEMA_A.PAC_PRODUCT_NUMBER@RMTDB PPN ON RT.NEW_ITEM_NUM = PPN.PRODUCT_NUMBER
       JOIN RMTSCHEMA_A.PAC_PRODUCT_NUMBER@RMTDB PPN2 ON PPN.PRODUCT_ID = PPN2.PRODUCT_ID AND PPN2.PRIMARY_SW = 'Y'
       JOIN RMTSCHEMA_B.PRD_IMPRESSION@RMTDB PI ON PI.ORDER_NO = PPN2.PRODUCT_NUMBER
    WHERE RT.SENT_TO_remoteapp_DATE is NULL and
          rt.order_id = 'W4011693' and 
          rt.item_ship_date < '01-AUG-2013')

     

    4. Try removing the 32 bit Oracle client form the WKS_NEW machine to test if it was getting in the way.

    Done. No difference, see previous comment.

  • 12. Re: SQLDeveloper, db links, and ora-12514
    EdStevens Guru
    Currently Being Moderated

    JeffSmithSQLDevPM wrote:

     

    It's listed under 'All Products' - next to the product dropdown, click on the 'Search all software products' link and type 'SQL Developer' for your search term.

    Ah!  Got it.  Will open SR and work from there.

    Thanks.

  • 13. Re: SQLDeveloper, db links, and ora-12514
    Gary Graham Expert
    Currently Being Moderated

    Hi Ed,

     

    Congratulations -- I hear there has been progress made on your SR.  Apparently you got tripped up by an undocumented "convenience" feature for automatically creating DB links for any DB link name matching an existing connection name.  I was not too far off the mark in my earlier comments.

     

    Vadim has cleaned up the feature for the next EA release so the user explicitly controls whether it is enabled.

     

    Regards,
    Gary

  • 14. Re: SQLDeveloper, db links, and ora-12514
    EdStevens Guru
    Currently Being Moderated

    GaryGraham wrote:

     

    Hi Ed,

     

    Congratulations -- I hear there has been progress made on your SR.  Apparently you got tripped up by an undocumented "convenience" feature for automatically creating DB links for any DB link name matching an existing connection name.  I was not too far off the mark in my earlier comments.

     

    Vadim has cleaned up the feature for the next EA release so the user explicitly controls whether it is enabled.

     

    Regards,
    Gary

     

    Gary,

    I had quite following this thread while I worked the SR, and just came back to wrap it up.  Thanks for your remarks.

     

    From my end, one of my final tests brought to light that the connect definition in SQL Dev had a space in it .. like 'mydb myuser'.  Simply eliminating that space, naming it 'mydb_myuser', gave us a very clean 'work around', so we had a point of closure on the SR.  Interestingly, though, even with that reproducible difference on the problem machine, I have been unable to reproduce the problem on my own machine.

     

    Hmm, now that I think it over a bit, I see two problems with the current explanation.  I hate to mess up the tech's internal evaluations by re-opening the SR, but would like to drop these observations into the internal discussions ... could you do that, or let me know the best way for me to do it?

     

    First, is the statement "automatically creating DB links for any DB link name matching an existing connection name".  Describing our scenario in a bit more detail .. We connect to database 'mydb1' using a connection named 'mydb myuser'.  Once connected we issue an update driven by a SELECT that includes references to db_link 'mydb2'.  We also have within the SQL Dev configuration, a connection named 'mydb2'.  So far that matches the assertion of the 'bug'.  However, when remove connection 'mydb1 myuser' and replace it with connection 'mydb1_myuser' (only a name change, all properties remain the same), we still have a connection named 'mydb2', that is the same as the db_link referenced in the problem UPDATE statement.  So it seems there is still a variable that is un-accounted for.

     

    Second, an examination of the SQLNET trace shows that the CREATE DBLINK statements being issued by SQL Dev specify a complete connect string (not a reference to be resolved via tnsnames) that includes 'SERVICE_NAME=null'.  So it seems that even if the issuance of the CREATE DATABASE LINK statement is legit (for whatever reason), the construction of the statement is still problematic.

     

    And thinking aloud some more .. I wonder why creating the db_link on the fly would be considered any more a 'convenience' than anticipating any other 'object not found' in the submitted sql statement?  I don't know what other users expect, but my expectation is I gave SQL Dev (or whatever client tool I'm using) a SQL statement to be submitted to the database.  If there is any problem with it -- syntax, authorization, objects not found -- just report it back when the server rejects it.  This convenience sounds a lot like MS Office trying to 'auto-correct' things.  ;-)

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points