SQL Developer is using the so called "thin driver".
For this the JDBC driver (a file named ojdbc6.jar) implements the complete Oracle network protocol which is usually handled by the Oracle client. It could use the another implementation: the "OCI" (or "thick") driver which would require an Oracle client to be installed. That would be necessary if e.g. ...
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 ...
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
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 ...
Asuming this is to transport data to an other system. It that case this will work:
set colsep ";"
set linesize 9999
set trimspool on
set heading off
set pagesize 0
set wrap off
set feedback off
set newpage 0
set arraysize 5000
spool you csv_file.csv
select rows from your tables;
If you don't want a header line, change to heading off
If this is ...
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.
I did a google search for the phrase "sql developer code formatting", and the first hit was this:
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 ...
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 ...
In SQL Developer, SET SERVEROUTPUT ON SIZE UNLIMITED sets the buffer to the maximum value 1000000 instead of unlimited (tested with 188.8.131.52.161018 database and SQL Developer 184.108.40.206). You can confirm this easily with SQL tracing.
As an alternative, the below really sets the the buffer to unlimited:
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 ...
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 ...
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
Use VNC or RDP to "jump" to a desktop or server that is in your datacenter. ...
You could check V$TRANSACTION. Here is an example:
create table t(a number);
insert into t values (1);
1 row created.
from v$transaction t
join v$session s
on t.ses_addr = s.saddr
where s.sid = sys_context('USERENV', 'SID');
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 ...
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 ...
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)
UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid;
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 ...
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. ...
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.
tnsnames.ora should be located in TNS_ADMIN, which is defined as ORACLE_HOME/network/admin/.
Go to a command prompt (assuming Windows from your mention of "c:\") and execute the following:
Verify the directories returned, make sure that your tnsnames.ora file is in there.
Oracle 11g doesn't support the LIMIT clause, though the impending 12c release is rumored to support it.
Anyway, you can do this using an analytic windowing function:
select * from
select salary, row_number() over (order by salary desc) as rn
where rn = 4;
You can also do this using rownum, but I find the above way to look cleaner.
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 ...
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 ...
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.
Yes, but it's expensive. If you fetch all the rows down to the client, then the export will use that recordset for the export.
If it's a long running query of a few rows, no big deal. If it's a long running query of millions+ of records, you might exhaust the JVM or upset the network guy/gal.
I talk about this more here.
Scroll to the end of the ...