11

I followed these instructions to add the jTDS Java library to Oracle SQL Developer. Basically it's about downloading the jTDS zip, unzipping it in some place you can reach later and then add the jTDS.jar as a JDBC third party driver in SQL Developer ((In the main menu) Tools > Preferences > (On the tree menu in the new window) Database > Third party JDBC ...


10

When you process a script in SQL Developer using the "Run Script" button (not the "Run Statement" button), it uses SQL*Plus internally to execute your script. Therefore using a SQL*Plus control setting which stops processing on errors should work. Try adding to the top of your script line like this: WHENEVER SQLERROR EXIT FAILURE


9

What you are experiencing is called caching. The database doesn't have to go to disk the 2nd time because it can either get the data from its own buffer cache, or the operating system/disk array can also provide the data faster from its own cache. In order to see whether Oracle fetched the data from disk, or used its cache you can enable autotrace in SQL ...


9

You have to use Service Names to connect to Pluggable Databases in 12c. It's just one of those things that can trip you up, I guess! SQL Plus connecting OK probably threw you.


9

In Oracle SQL Developer Application Goto: Tools->SQL Worksheet OR ALT+F10


8

For a named instance, under port use "1433;instance=INSTANCENAME". Adjust port number accordingly.


7

The green bug you're seeing (look closely) means the package have been compiled with DEBUG option. It is also seen on bare procedures and functions. It is a coincidence they don't expand - maybe a bug (duh) in SQLdeveloper :) On mine, these expand without problem.


7

Use quotation marks: CREATE DATABASE LINK my_link CONNECT TO daniel identified by "*password" USING 'hostname:port/servicename';


7

I did a google search for the phrase "sql developer code formatting", and the first hit was this: http://www.thatjeffsmith.com/archive/2014/03/how-to-make-your-code-look-like-steven-feuersteins-in-oracle-sql-developer/ Have a read through that blog post from Jeff Smith, product manager for SQL Developer. That should explain what you need to do. Edited to ...


7

There are some SQL Developer specific comments/"hints". For example if you run the below as a script (F5) and not a statement (Ctrl-Enter): select /*csv*/ * from table; You will get the results in CSV format the first time already. You can even spool the output just as in SQL*Plus. So you could just run the below block of code as a script (select lines and ...


7

In SQL Developer, SET SERVEROUTPUT ON SIZE UNLIMITED sets the buffer to the maximum value 1000000 instead of unlimited (tested with 12.1.0.2.161018 database and SQL Developer 4.1.5.21). You can confirm this easily with SQL tracing. As an alternative, the below really sets the the buffer to unlimited: begin dbms_output.enable(null); end; /


6

Autotrace in SQL Developer gets the plan from v$sql_plan, and also gets the stats from your session, does a delta of session stats before and after running the query. Explain Plan asks the database what it THINKS the plan will be for your query. Your co-worker is right, they can differ wildly, and you're better served to use AutoTrace or our new feature in ...


6

For where you are looking, you can only see the tables that exist in the schema that belongs to the user you supplied in your connection details. If you want to see 'the tables' - you need to expand the 'Other Users' node, and explore the users there, and go to their respective tables list. If you can query these objects in a worksheet w/o adding a SCHEMA. ...


5

Most likely, it will finish a DDL command behind the scenes, but I wouldn't bet my job on it. What if your CTAS action runs out of tablespace? You would never see the error. If you have an unrealiable Internet connection, then the best solution is probably Simple/basic solution Use VNC or RDP to "jump" to a desktop or server that is in your datacenter. ...


5

Several things might be going on here and might be difficult to pin down but here's a few things. After Oracle crashes, it must do some type of recovery (as in media or crash recovery) to bring the database back to a consistent state. Depending on what you were doing (and whatever else was going on in the instance at the time of the crash) transactions may ...


5

You could check V$TRANSACTION. Here is an example: create table t(a number); Table created. insert into t values (1); 1 row created. select t.status from v$transaction t join v$session s on t.ses_addr = s.saddr where s.sid = sys_context('USERENV', 'SID'); STATUS ---------------- ACTIVE After commiting: commit; Commit complete. select t.status from ...


5

There is also SELECT dbms_transaction.local_transaction_id FROM dual; which will show a transaction id if there are pending commits.


5

The preferences dictate when the code assist features kick in. In 4.0.3 we filter the advice for the automatic bits when there are more than 10 suggested identifiers. So, typing: select * from ...on a large schema, you'd likely not see any suggested table or views names, unless you invoked the helper directly via ctrl+spacebar. As you type more, the more ...


5

There are a number of things that can cause the actual plan to differ from the estimated plan (and if you want to get really deep in the weeds, there are lots of things that can cause different methods of producing the actual plan to produce different results but I'll ignore that). The simplest (and most common) revolve around bind variables. If I do an ...


5

I had the same problem. And I solved it by using SERVICE NAME (an alias) instead of SID: 1. Open file tnsnames.ora from your ORACLE_HOME folder. In case you don't know its location, do the following steps Windows (open cmd) > sqlplus // connect to sqlplus, login as sysdba as / sysdba // then type your password that you set in configuration setup ...


5

define my_schema_name = 'SCHEMA_1'; CREATE OR REPLACE PROCEDURE "&my_schema_name".updateDBUSER( p_userid IN DBUSER.USER_ID%TYPE, p_username IN DBUSER.USERNAME%TYPE) IS BEGIN UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid; COMMIT; END;


5

You can specify the number of rows allowed to be rejected before throwing an error, or you can specify UNLIMITED. I guess you specified a number, but your .tbl file contains rows that the database can't parse appropriately based on your definition. If you want to ignore all malformed rows, you can simply change the limit to UNLIMITED: alter table ...


5

By default, it is the user you used to log in, and you can query it as below: select sys_context('USERENV', 'CURRENT_SCHEMA') from dual; You can change your current schema in your session however: alter session set current_schema=USER2; If you create a table after this, without specifying the schema, that table will be created in the USER2 schema ...


5

Adding AddVMOption -Dsun.java2d.opengl=true to ~/.sqldeveloper/<version>/product.conf fixed it for me.


5

Just set the below in your session, as you would do in SQL*Plus: set null "null" Note: this will not affect how NULLs are displayed in the data grid (you can set that from the menu: Tools - Preferences - Database - Advanced - Display Null Value As), but it affects the exported CSV.


5

You need to pass in a REFCURSOR for the procedure to use as its output (OUT) parameter. Quick procedure to test with: CREATE OR REPLACE PROCEDURE passenger_details (p_passenger_details OUT SYS_REFCURSOR) AS BEGIN OPEN p_passenger_details FOR SELECT 'test test' as Full_Name, 10 as Age, 'alien' as Nationality, 'foo' as Category, 'Name' as Airline_Name, '...


5

You can use dbms_sql to return implicit results. This was a feature added to Oracle 12 to ease the migration from SQL Server: CREATE PROCEDURE passenger_details AS c1 SYS_REFCURSOR; BEGIN open c1 for SELECT Full_Name, Age, Nationality, Category, Airline_Name, Class_Type FROM Passenger JOIN Ticket on Passenger.Passenger_No = Ticket....


4

One way to do this is to ensure that all tables of properties, or metadata, have a date_created and date_last_modified date. Then you can filter where the date created is in a time period and export the changes as inserts or updates. What I do is harder. All changes to development properties are saved as a script of inserts/updates and I use a project/bug ...


4

Both the USER SQL function and the SYSCONTEXT SQL funtion return the name of the session user (example). select USER,SYS_CONTEXT ('USERENV', 'SESSION_USER') from dual The SYSCONTEXT, the UID and the USERENV SQL function can give you further information to your session.


4

You could save multiple connections to the same db but with different credentials. Give them useful names like user@thisdb and you'll be able to view that in SQL Developer at a glance in the top right corner of the worksheet. And my favorite part: if you need to run the same SQL as multiple users or in different environments or both, you'll find that the ...


Only top voted, non community-wiki answers of a minimum length are eligible