CODEWITHSUNDEEP
sqloracle-databasereadonly
user2025818
user2025818

Reputation:

select statements in oracle with for read only

I have a Database Links in Oracle, and when I query some data like: select * from kfilwrk@something for read only, I have an error: "missing keyword". I can just write select statement, but the problem is, I need to add for read only because my select statement may change some data.


UPD: when I write simple select statement and retrieve data, after that I close SQL Developer, it asks me to rollback or commit data. It means the select cursor updates some data

Upvotes: 2

Views: 19133

Answers (2)

thatjeffsmith
thatjeffsmith

Reputation: 22412

If you query over a DB_LINK the database assumes there are transactions to COMMIT on close.

Also, SQL Developer has a shared connection for the Worksheet and the Database Navigator/Object Editors. So if you had opened a table and changed a record - that's on the same connection as your SELECT query on the worksheet.

Upvotes: 0

Andreas Fester
Andreas Fester

Reputation: 36630

The syntax is WITH READ ONLY, not FOR READ ONLY and it can only be used with sub queries:

SQL> CREATE DATABASE LINK MyLink CONNECT TO HR IDENTIFIED BY HR USING 'sampleHost:1521/XE';
SQL> SELECT COUNT(*) FROM (SELECT * FROM Dual@MyLink WITH READ ONLY);

  COUNT(*)
----------
         1

See also http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF55295:

subquery_restriction_clause The subquery_restriction_clause lets you restrict the subquery in one of the following ways:

WITH READ ONLY Specify WITH READ ONLY to indicate that the table or view cannot be updated.

Alternatively, you can set your transaction into read only mode:

SET TRANSACTION READ ONLY;      -- Read only
...
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- back to default (read committed)

Upvotes: 2

Related Questions