15

How do you log onto courgette? Would that username identify you? You can check that by running select sys_context('userenv', 'os_user') from dual; The USERENV namespace can retrieve a lot of different information about the user and their environment. Find out more.


12

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. ...


8

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 ...


8

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 ...


8

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


8

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.


7

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; spool off If you don't want a header line, change to heading off If this is ...


7

Unless I am missing something, your query would be something like this: select created, count(*) CreatedCount from yourtable group by created order by created; See SQL Fiddle with Demo Or if you have a time associated with the date, you can use TRUNC: select trunc(created), count(*) CreatedCount from yourtable group by trunc(created) order by trunc(...


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

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


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 ...


6

The Oracle client (aka driver) knows who you are in your operating system (because that code runs on your computer). This information is transmitted as part of the login process. Depending on your application and driver type (OCI/JDBC) it even transmits information like your computer's name. If you can, run a SELECT * FROM v$session WHERE sid = userenv(...


6

The reason you are having problems with dbms_metadata.get_ddl is that it outputs CLOBs which can be up to 4GB in size. By default, SQL*Plus and Oracle SQL Developer truncate long text so they don't trash the client with large gobs of text. It's very easy to override this behavior in SQL*Plus with a few SET commands and get clean DDL. The script you need ...


6

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


6

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 ...


6

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; /


5

As far as I know, Import/Export and Data Pump are command-line-only types of tools. But if you really wanted to use that functionality from within SQL Developer (or SQL Plus), Data Pump uses the built-in DBMS_DATAPUMP and DBMS_METADATA packages. You should be able to call those packages directly, so I would start there. There's documentation available in ...


5

This can't be done without some kind of workaround, so here's one for you. You can recompile the PL/SQL after creation & raise an exception if the recompilation fails. This will cause SQL*Plus to exit on failure. For example: test.sql: create or replace procedure foo as begin this is an error; end; / exec execute immediate 'alter procedure foo ...


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

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

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

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 ...


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

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

DON'T DO THAT That code is horrible. You should place ALL of the values in a Global Temporary Table (GTT) and JOIN to that table. Call commit or rollback to auto-clear the GTT.


4

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: echo %TNS_ADMIN% echo %ORACLE_HOME% Verify the directories returned, make sure that your tnsnames.ora file is in there.


4

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 from emp ) where rn = 4; You can also do this using rownum, but I find the above way to look cleaner. ...


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 ...


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